Creating date/time ranges with Oracle TIMESTAMP WITH TIMEZONE datatype
How does one create date/time ranges for TIMESTAMP WITH TIMEZONE
timestamps with a range precision down to the level of seconds?
Here is my sample query, however I can only create ranges开发者_开发知识库 down to the day level:
SELECT COLUMN_NAME, MY_TIMESTAMP
FROM TABLE_NAME
WHERE (MY_TIMESTAMP BETWEEN SYSDATE - INTERVAL '1' DAY
AND SYSDATE - INTERVAL '0' DAY)
ORDER BY MY_TIMESTAMP
Here is the TIMESTAMP
format used by the database:
30-AUG-10 04.20.00.109000 PM -07:00
Thanks.
Use the 'SECOND' qualifier on your INTERVAL, as in:
SELECT COLUMN_NAME, MY_TIMESTAMP
FROM TABLE_NAME
WHERE MY_TIMESTAMP >= SYSDATE - INTERVAL '3600' SECOND
ORDER BY MY_TIMESTAMP
The above should give you all rows with a timestamp within the last hour.
You may need to use SYSTIMESTAMP instead of SYSDATE to get around the issues raised by TIMESTAMP WITH TIMEZONE.
Share and enjoy.
精彩评论