Search code examples
sqloracleplsqltimestampsysdate

Oracle SQL, Date and Time in GMT (UTC)

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

Solution

  • 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;

    db<>fiddle