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'
I want to just say
ph.change_date = Todays Date
But have that as a Unix Timestamp.
Any help appreciated.
Advertisement
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