比较Oracle中的时间戳

Question

我有一个用于存储日期和时间数据的 VARCHAR2 列，在我的 Oracle 11g Express 数据库中，格式如下：

9/30/2016 14:00:00

我试着找到在时间范围内获取数据的方法，并找到了以下两种方式：

select * 
from dummy
WHERE starttime > '9/30/2016 14:00:00' 
AND starttime < '9/30/2016 17:00:00' 
order by starttime;

select * 
from dummy 
WHERE to_timestamp(starttime, 'mm/dd/yyyy hh24:mi:ss') > TO_TIMESTAMP('9/30/2016 14:00:00', 'mm/dd/yyyy hh24:mi:ss') 
AND TO_TIMESTAMP(starttime, 'mm/dd/yyyy hh24:mi:ss') < TO_TIMESTAMP('9/30/2016 17:00:00', 'mm/dd/yyyy hh24:mi:ss');

我也在想第一种方法是如何工作的，因为starttime列以VARCHAR格式存储，而不进行Timestamp转换，比较仍然有效。有人能向我解释一下这是如何发生的，或者是否存在某些特殊情况，它将无法工作吗？谢谢。

- Kevin

---

第一种方法怎么样？它不行。它可能不会崩溃，但不会返回有意义的结果。只有在日期格式为“YYYY-MM-DD”时，按字母顺序才有效。 - Álvaro González

是的，我知道它不起作用。但对于上述特定情况，它起作用了。所以我只想了解它在那种特定情况下是如何工作的。 - Kevin

'9/30/2016 14:00:00' - '9/30/2016 17:00:00' 可行，但其他范围（例如 '9/30/2016 20:00:00' - '12/1/2016 17:00:00'）将失败。转换为 TIMESTAMP（或 DATE 的）是正确的方式。 - Wernfried Domscheit

为什么在“one, two”中字母排序可以正常工作，但在“four, five”中却不行？纯属巧合。 - Álvaro González

Answer 1

这个方法可行是因为月份、年份和一位数和两位数日期的问题都不存在。考虑任何一个在“9/30/2016 14:00:00”到“9/30/2016 17:00:00”范围内的字符串，它们都必须以“9/30/2016 1”开头。

如果范围是从“9/30/2016 14:00:00”到“10/30/2016 17:00:00”，你将找不到任何记录，因为该字符串必须以“9”或更大的数字开始，并且必须小于或等于“1”，这是不可能的。

所以正是在特定日期内的狭窄范围救了你 :-)

- Thorsten Kettner

---

我在这里支持JohnHC的观点：正确的方法不是将日期时间作为字符串存储，而是作为日期时间（在Oracle中为DATE或TIMESTAMP类型）。但是，如果由于某种原因必须处理存储为字符串的日期时间，则第二种方法转换为日期时间是可行的。（我可能不会在每个查询中都这样做，而是编写一个视图来处理。） - Thorsten Kettner

Answer 2

将值存储在 VARCHAR 列中意味着您将进行字符串比较：

SELECT * 
FROM   dummy
WHERE  starttime > '9/30/2016 14:00:00' 
AND    starttime < '10/30/2016 17:00:00' 
ORDER BY starttime;

这段代码会检查开始时间，逐个字符进行比较，如果第一个字符大于'9'且小于'1'，则返回一行（因为这永远不可能成立，所以它不会返回行）。此外，它不会考虑9和10表示月份，并且在进行字符串比较时，'09/30/2016' < '09/31/1900' < '10/30/2016'。

即使将值存储在TIMESTAMP列中，使用字符串字面量也是不好的做法:

SELECT * 
FROM   dummy
WHERE  starttime > '9/30/2016 14:00:00' 
AND    starttime < '9/30/2016 17:00:00' 
ORDER BY starttime;

这个方法可以正常工作，因为Oracle会使用会话参数NLS_TIMESTAMP_FORMAT作为格式掩码执行隐式转换（使用TO_TIMESTAMP( time, format_mask )）。

所以你的查询语句（假设数据类型为TIMESTAMP）实际上是这样的（虽然Oracle会以更有效的方式实现它）：

SELECT * 
FROM   dummy
       CROSS JOIN
       ( SELECT value AS format_mask
         FROM NLS_SESSION_PARAMETERS
         WHERE PARAMETER = 'NLS_TIMESTAMP_FORMAT' ) nls
WHERE  starttime > TO_TIMESTAMP( '9/30/2016 14:00:00', nls.format_mask )
AND    starttime < TO_TIMESTAMP( '9/30/2016 17:00:00', nls.format_mask )
ORDER BY starttime;

NLS_TIMESTAMP_FORMAT是一个会话参数，这意味着每个用户都可以在自己的会话中设置此参数的值。如果某个用户将其更改为YYYY-MM-DD"T"HH24:MI:SS.ZZZ"Z"（即ISO8601格式），则您的查询将对该用户出现问题（而未更改其他用户），而且您的查询语句没有做任何更改。

与其使用字符串文字和隐式转换，更好的方法是明确设置您期望的格式掩码或使用ANSI TIMESTAMP文字。

SELECT * 
FROM   dummy
WHERE  TO_TIMESTAMP( starttime, 'MM-DD-YYYY HH24:MI:SS' ) > TIMESTAMP '2016-09-30 14:00:00' 
AND    TO_TIMESTAMP( starttime, 'MM-DD-YYYY HH24:MI:SS' ) < TIMESTAMP '2016-09-30 17:00:00'
ORDER BY TO_TIMESTAMP( starttime, 'MM-DD-YYYY HH24:MI:SS' );

您将从基于函数的索引TO_TIMESTAMP( starttime, 'MM-DD-YYYY HH24:MI:SS' )中受益。

更好的方法是将您的列转换为正确的TIMESTAMP格式，然后您就不需要基于函数的索引，可以直接使用TIMESTAMP字面量作为边界，无需进行任何转换函数。

- MT0

Answer 3

将日期存储为varchar不是明智的选择……

如果您不需要跨越一年的边界，则第一种方法是可以的，因为数字按从左到右的顺序进行比较（因为是文本）。除非您以“YYYY-MM-DD HH24:MI:SS”格式进行存储，否则您将会遇到问题。

有两个选项，将存储更改为DATE，或在WHERE子句上使用to_date或to_timestamp（我建议使用to_date）。

- JohnHC

Answer 4

将日期/时间存储为字符字符串或数字是一个不好的想法。优化器对于领域没有任何了解，因此在尝试估计基数时，您并没有给予优化器最好的机会。例如，请考虑以下两个日期

Dec 31st 2016
Jan 1st 2017

如果您将这些内容存储为数字，您可能会使用：

20170101 and 20161231

那么它们之间的天数是多少呢？使用数字，您可以得到

20170101 - 20161231
= 8870

然而，真正（基于日期的）答案是一。

虽然你可以使用 TO_DATE() 或 CAST 转换你的列，但现在你可能无法使用某些优化，如索引、分区修剪、布隆过滤等。

因此，我强烈建议使用正确的数据类型。

- BobC

Answer 5

在Oracle中，你也可以使用以下方法：

SELECT * FROM table 
WHERE field BETWEEN TRUNC(SYSDATE - 6) AND SYSDATE

- Sham Fiorin