Oracle query which works for Day Light Saving in EST time zone

Question

I am working on an application in which:

Authentication token has an expiration of 2 hours from its creation time.
Token creation and expiration time stored in DB are local date / time (i.e. EST)
Tokens generated within 2 hours before DST starts, expire in 1 hour time as clock moves 1 hour forward.
For ex: If a token is created at 1:30 AM on 14th March 2021, then its creation and expiration time will set in DB as “2021-03-14 01:30:00” and “2021-03-14 03:30:00” respectively. Now, suppose DST starts at 2 PM, server time will be 3:00 at that moment and the token which should be valid till 3:30 will now expire at 2:30

currently I am using the query which start failing when day light saving starts in US. The below query is used to fetch active token from DB:

SELECT * FROM tbl1  WHERE TOKEN_VALUE = 'xyztoken' AND 
  TOKEN_EXPIRATION > CURRENT_TIMESTAMP;

Is there any way to tweak the above query so that it returns the right result once day light saving start in US.

TOKEN_EXPIRATION and TOKEN_CREATION fields are in TIMESTAMP

How infeasible would it be to store the token creation and expiration time in UTC instead? Fundamentally these are "instants in time" rather than date/time values with a natural time zone, so storing them in UTC would make more sense IMO. Aside from anything else, if you store "1:30am" on the day that a fall back transition occurs, you've immediately lost information - is that the first occurrence of 1:30am on that day, or the second? — Jon Skeet
"Token creation and expiration time stored in DB are local date / time" What is the data type of the column you are using to store this? Please edit your question with the DDL statement for your table and an example DML (INSERT) statement for how you are storing the token expiration in the table. — MT0
TOKEN_EXPIRATION and TOKEN_CREATION fields are in TIMESTAMP field and while saving it in DB, I am saving it using java new Date(System.currentTimeMillis()) — Sumit Sood

MT0 MT0 · Accepted Answer · 2020-12-15T11:25:36

Assuming that your TOKEN_EXPIRATION is stored as a DATE data type where the date/time represents the time in the EST time zone then you can convert it back to a TIMESTAMP WITH TIME ZONE using:

SELECT *
FROM   tbl1
WHERE  TOKEN_VALUE = 'xyztoken'
AND    FROM_TZ( CAST( TOKEN_EXPIRATION AS TIMESTAMP ), 'EST5EDT' ) > CURRENT_TIMESTAMP;

If it is a TIMESTAMP column then you can skip the CAST:

SELECT *
FROM   tbl1
WHERE  TOKEN_VALUE = 'xyztoken'
AND    FROM_TZ( TOKEN_EXPIRATION, 'EST5EDT' ) > CURRENT_TIMESTAMP;

Oracle query which works for Day Light Saving in EST time zone

1 Answers