Creating date/time ranges with Oracle TIMESTAMP WITH TIMEZONE datatype.

Question

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.

Answer 1

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.