Today in Oracle Unix date

I have a field which stores the date in a Unix timestamp.

Instead of having to translate the test ie

TO_DATE( TO_CHAR( TO_DATE('19700101', 'yyyymmdd') + + ( FLOOR( ph.change_date / 24 / 60 / 60)))) = '07-NOV-2020'

​x
 
TO_DATE( TO_CHAR( TO_DATE('19700101', 'yyyymmdd') + + ( FLOOR( ph.change_date / 24 / 60 / 60)))) = '07-NOV-2020'​

I want to just say

ph.change_date = Todays Date

 
ph.change_date = Todays Date  ​

But have that as a Unix Timestamp.

Any help appreciated.

Answer

Unix time is in the UTC time zone; do not naively use SYSDATE as it may be in the wrong time zone. Use SYSTIMESTAMP and convert it to UTC:

WHERE ph.change_date >= ( TRUNC( SYSTIMESTAMP AT TIME ZONE 'UTC' ) - DATE '1970-01-01' ) * 24 *60 * 60
AND   ph.change_date <  ( TRUNC( ( SYSTIMESTAMP + INTERVAL '1' DAY ) AT TIME ZONE 'UTC' ) - DATE '1970-01-01' ) * 24 *60 * 60

 
WHERE ph.change_date >= ( TRUNC( SYSTIMESTAMP AT TIME ZONE 'UTC' ) - DATE '1970-01-01' ) * 24 *60 * 60AND   ph.change_date <  ( TRUNC( ( SYSTIMESTAMP + INTERVAL '1' DAY ) AT TIME ZONE 'UTC' ) - DATE '1970-01-01' ) * 24 *60 * 60​

Advertisement

Answer