Oracle SQL, Date and Time in GMT (UTC)

Question

This coding is giving the correct answer for SYSDATE

select round((SYSDATE - date '1970-01-01')*24*60*60) from dual;

1662482430 (seconds)

I need to return the date / time in GMT/UTC.

I need help with the syntax.

Thanks, Pete

you want to return SYSDATE as UTC? Question is not clear. Please show what type of result you are expecting as output — OldProgrammer, Sep 06 '22 at 16:55

score 1 · Accepted Answer · answered Sep 06 '22 at 17:09

If you use SYSTIMESTAMP instead of SYSDATE then that value will be in your DB time zone, and you can then convert that to UTC with at time zone:

SYSTIMESTAMP at time zone 'UTC'

and cast that back to a date:

cast(SYSTIMESTAMP at time zone 'UTC' as date)

and then use that in your calculation:

select round((cast(SYSTIMESTAMP at time zone 'UTC' as date) - date '1970-01-01')*24*60*60)
from dual;

1 Answers1