CODEWITHSUNDEEP
oracle-databasetimezonedst
Sumit Sood
Sumit Sood

Reputation: 485

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

I am working on an application in which:

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

Upvotes: 0

Views: 225

Answers (1)

MT0
MT0

Reputation: 168416

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;

Upvotes: 1

Related Questions