时间戳

首页 > 解决方案 > 数据类型为“日期”的 Oracle sql 查询

sql - 数据类型为“日期”的 Oracle sql 查询

问题描述

我对数据类型为“日期”的转换列有疑问。

我有桌子（我不能改变结构）：

id           number(10,0)
time_stamp   date

当我使用 sql 查询时：

  SQL * FROM t;

我有：

 id   time_stamp
    1    01.07.19
    2    20.08.19

但我需要有时间的日期，所以我使用了：

SELECT  
id
,TO_TIMESTAMP(lr.time_stamp,'DD.MM.YYYY HH24:MI:SS') as dt
FROM t;

我有：

  id         dt
  1   01.07.19 00:00:00,000000000
  2   20.08.19 00:00:00,000000000

但是当我使用 TO_CHAR 时，我看到有时间。

SELECT  
id
,(TO_CHAR(time_stamp, 'DD.MM.YYYY HH24:MI:SS')) as dt
FROM t

我有：

  id         dt
  1   01.07.19 17:09:07
  2   20.08.19 21:45:03

而我只是需要时间。我尝试在 WHERE 子句中使用它来获得给定范围内的结果。但是当我这样使用它时：

SELECT id
FROM t
WHERE
 TO_TIMESTAMP(TO_CHAR(time_stamp,'DD.MM.YYYY HH24:MI:SS')) >= '01.11.2020 06:00:00' 
 AND TO_TIMESTAMP(TO_CHAR(time_stamp,'DD.MM.YYYY HH24:MI:SS')) <= '02.11.2020 06:00:00'

它没有完全优化，查询需要很长时间。你有什么建议吗？我总是需要从昨天 06:00 到今天 06:00 的范围

标签： sqloracledatetimequery-optimizationwhere-clause

解决方案

您正在混合存储和表示问题。

日期以其自己的内部格式存储，您无法控制。您应该使用日期函数和算术将日期作为日期进行比较。

另一方面，您可以使用字符串格式化方法以您喜欢的格式显示to_char()它。请注意，to_timestamp()在date列上应用是没有意义的，并且可能会产生令人惊讶的结果：它旨在将字符串转换为日期，而不是相反。

假设您想要昨天早上 6 点到今天早上 6 点之间的所有记录，并且日期以显示时间部分的格式显示，然后：

select id, to_char(dt, 'dd.mm.yyyy hh24:mi:ss') dt
from t
where dt >= trunc(sysdate - 1) + 6 / 24
  and dt <  trunc(sysdate) + 6 / 24

您还可以where使用间隔来表达条件：

where dt >= trunc(sysdate - 1) + interval '6' hour
  and dt <  trunc(sysdate) + interval '6' hour

这两个where谓词都称为 SARGable：没有函数应用于被过滤的列，这样效率更高，并且可以利用现有索引。

推荐阅读