CODEWITHSUNDEEP
oracle-databasetimetimezonetimestamp-with-timezone
Albert Gevorgyan
Albert Gevorgyan

Reputation: 191

Comparing TIMESTAMP WITH TIME ZONE to DATE

I need to compare database generated dates (column values defaulting to SYSDATE) to hand written time stamps recorded with the time zone. This is the comparison I am trying:

where trunc(updated, 'mi') >= to_timestamp_tz('2017-10-24 04:45 US/Pacific', 'YYYY-MM-DD HH24:mi TZR')

I assume that a character string converted to a TIMESTAMP WITH TIME ZONE should be comparable to a DATE. However, this only works if the database is located in my own time zone. Otherwise, I have to manually convert the external time stamp to the database time zone. For example, if I am in BST and the database is in EST, I have to write:

where trunc(updated, 'mi') >= to_timestamp_tz('2017-10-24 04:45 US/Pacific', 'YYYY-MM-DD HH24:mi TZR')

Where 04:45 = 10:45 - 6, and 6 is the difference between BST and EST. This looks absolutely counter-intuitive as the original time stamp has been recorded in PST and is therefore entered as US/Pacific. Could anybody please explain why this conversion is needed? I would also appreciate if somebody suggests a better solution.

Upvotes: 1

Views: 7942

Answers (2)

pravinsham
pravinsham

Reputation: 31

If you are running this query in any of oracle clients, You can also change the session setting by running

ALTER SESSION SET TIME_ZONE ='BST';

When you change the session time_zone all the date values coming from database column and time stamp values passed without time zone will be converted to BST.

hence it will ensure that the comparison is happening in common time zone

Upvotes: -2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

You can cast your DATE column to a TIMESTAMP WITH TIME ZONE value like this:

WHERE 
  FROM_TZ(CAST(TRUNC(updated, 'mi') AS TIMESTAMP), 
       (SELECT TO_CHAR(SYSTIMESTAMP, 'TZR') FROM dual)) >= to_timestamp_tz('2017-10-24 04:45 US/Pacific', 'YYYY-MM-DD HH24:mi TZR')

but it works also the other way around, i.e. convert TIMESTAMP WITH TIME ZONE to DATE in database time zone:

WHERE 
    TRUNC(updated, 'mi') >=
        CAST(TO_TIMESTAMP_TZ('2017-10-24 04:45 US/Pacific', 'YYYY-MM-DD HH24:mi TZR') AT TIME ZONE (SELECT TO_CHAR(SYSTIMESTAMP, 'TZR') FROM dual) AS DATE)

SYDATE is provided in the time zone of database server's operating system (NOT DBTIMEZONE) , thus you have to use (SELECT TO_CHAR(SYSTIMESTAMP, 'TZR') FROM dual) or provide hard-coded value if appropriate.

This approach fails if any users inserted/updated any updated using his current local time zone. In such cases the time zone information is lost and there is no way to recover it.

Upvotes: 3

Related Questions