将值存储在 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
字面量作为边界,无需进行任何转换函数。
'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