sql oracle datetime,sql – 带有时区的Oracle DateTime查询

您需要使用

FROM_TZ函数将TIMESTAMP转换为TIMESTAMP WITH TIME ZONE.例如,如果您知道您的变量是UTC时间(0:00)：

SELECT *

FROM book

WHERE date_created >= from_tz(, '+0:00');

这是一个示例脚本,显示您描述的行为(您的本地时区应设置为1:00)：

CREATE TABLE t (tz TIMESTAMP(6) WITH TIME ZONE);

INSERT INTO t VALUES

(to_timestamp_tz('20000101 00:00:00 +1:00','yyyymmdd hh24:mi:ss tzh:tzm'));

INSERT INTO t VALUES

(to_timestamp_tz('20000101 00:00:00 -1:00','yyyymmdd hh24:mi:ss tzh:tzm'));

-- This will return two values instead of one

SELECT *

FROM t

WHERE tz >= to_timestamp('20000101 00:00:00', 'yyyymmdd hh24:mi:ss');

-- This query will return only one row

SELECT *

FROM t

WHERE tz >= from_tz (to_timestamp('20000101 00:00:00',

'yyyymmdd hh24:mi:ss'), '+0:00');