Oracle SQL查询语句和条件与时间戳和ISO日期 [英] Oracle SQL query statement and conditions with timestamps and ISO dates
问题描述
我需要一个查询,它将根据datetime条件从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';
作为datetime我使用ISO日期,这是必须。在Oracle XE数据库中,[modification_date]列具有类型Timestamp with time zone。
现在是我的问题 - 如何将查询中的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,而不是UTC 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
这篇关于Oracle SQL查询语句和条件与时间戳和ISO日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!