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

2021-12-30 00:00:00 datetime sql oracle11g oracle isodate

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

I need a query which will select records from Oracle database on the basis of the datetime condition. Example below:

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

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

As datetime I used ISO date and this is "Must be". In Oracle XE database a [modification_date] column has type "Timestamp with time zone". And now is my question - How to convert ISO date in query to proper searching on database ?

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

I tried adding to_timestamp_tz to query statement.

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:日期格式图片结束前转换整个输入字符串

SQL Error [1830] [22008]: ORA-01830: date format picture ends before converting entire input string

推荐答案

基于之前的问题，将 T 和 Z 视为字符文字，基本上忽略它们，使用:

Based on an earlier question, it's tempting to treat both the T and Z as character literals, and basically ignore them, using:

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

如果您使用 to_timestamp_tz() 而不指定时区，则它默认为您的会话时区，to_timestamp() 也是如此；所以在 Zulu/UTC 中指定的时间会丢失该区域信息:

If you use to_timestamp_tz() without specifying a timezone then it defaults to your session time zone, as would to_timestamp(); so a time specified in Zulu/UTC loses that zone information:

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，而不是 12:00 UTC.

Your 12:00 time is shown as 12:00 in New York, not 12:00 UTC.

假设您的值总是代表 UTC，更安全的转换是使用 from_tz() 函数:

A safer conversion, assuming your values are always supposed to represent UTC, is to specify the time zone explicitly with the from_tz() function:

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

这可以正确获取 UTC 时间:

This gets the UTC time properly:

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

相关文章