带时间戳和ISO日期的Oracle SQL查询语句和条件

小编典典

带时间戳和ISO日期的Oracle SQL查询语句和条件

sql

我需要一个查询，它将根据日期时间条件从Oracle数据库中选择记录。下面的例子：

SELECT * FROM table_name WHERE [modification_date] >= '2014-01-28T12:00:00Z';

作为日期时间，我使用ISO日期，这是“必须”。在Oracle
XE数据库中，[modification_date]列的类型为“带时区的时间戳”。现在是我的问题-如何将查询中的ISO日期转换为对数据库的正确搜索？

我尝试将to_timestamp_tz添加到查询语句。

SELECT * FROM table_name
WHERE MODIFICATION_DATE >= to_timestamp_tz('2014-01-28T00:00:0Z', 'YYYY-MM-DD"T"HH24:MI:SS');

但是得到这个错误：

SQL错误[1830] [22008]：ORA-01830：日期格式图片在转换整个输入字符串之前结束

阅读 195

2021-03-17

共1个答案

小编典典

基于前面的问题，很容易将T和Z都视为字符文字，并基本上使用以下命令忽略它们：

to_timestamp_tz('2014-01-28T12:00:0Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

如果您使用时to_timestamp_tz()未指定时区，则默认为您的会话时区to_timestamp()；因此在Zulu /
UTC中指定的时间会丢失该区域信息：

alter session set time_zone = 'America/New_York';
select to_timestamp_tz('2014-01-28T12:00:0Z',
  'YYYY-MM-DD"T"HH24:MI:SS"Z"') from dual;

TO_TIMESTAMP_TZ('2014-01-28T12:00:0Z','YYYY-MM-DD"T"HH24:MI:SS"Z"')
-------------------------------------------------------------------
28-JAN-14 12.00.00.000000000 AMERICA/NEW_YORK

您在纽约的12:00时间显示为12:00，而不是UTC的12:00。

一个更安全的转换，假设你的值总是应该代表UTC，是与明确指定的时间段的from_tz()功能：

WHERE MODIFICATION_DATE >= from_tz(to_timestamp('2014-01-28T12:00:0Z',
  'YYYY-MM-DD"T"HH24:MI:SS"Z"'), 'UTC')

这样可以正确获取UTC时间：

alter session set time_zone = 'America/New_York';
select from_tz(to_timestamp('2014-01-28T12:00:0Z',
  'YYYY-MM-DD"T"HH24:MI:SS"Z"'), 'UTC') from dual;

FROM_TZ(TO_TIMESTAMP('2014-01-28T12:00:0Z','YYYY-MM-DD"T"HH24:MI:SS"Z"'),'UTC')
-------------------------------------------------------------------------------
28-JAN-14 12.00.00.000000000 UTC

2021-03-17