CODEWITHSUNDEEP
sqloracle-databasetimestamp-with-timezone
ankush bansal
ankush bansal

Reputation: 60

How To Convert Sysdate to GMT?

So Scenario is simple I need to write one function in which one date will be provided which will be sysdate from the database and the return value of the function should be in GMT format. this function can be used across different timezones in different databases as per business scenarios. we need to take care of the Daylight saving concept and other things, it should return GMT Date.

Best Suggestions, please.

Upvotes: 0

Views: 5780

Answers (1)

Stew Ashton
Stew Ashton

Reputation: 1529

For better understanding, let's start with Oracle time zone support. There are three different sources for time zone information:

  • The operating system's time zone (set when the database was started)
  • The database time zone (as set at database creation)
  • The "local" time zone of the session (can be changed with ALTER SESSION)

The Oracle datetime data types:

  • DATE: includes date, hours, minutes and whole seconds, but no time zone information;
  • TIMESTAMP: can also include fractional seconds, but no time zone information;
  • TIMESTAMP WITH TIME ZONE: includes time zone information, which can be either an offset from UTC ('-4:00') or a named time zone ('Europe/Paris'). The offsets don't adjust for daylight savings time, the named time zones do.
  • TIMESTAMP WITH LOCAL TIME ZONE: the value is converted to the database time zone when stored, and converted to the session time zone when selected.

Now for the built-in functions that return "right now":

  • SYSDATE: a DATE (with time to the second) based on the time zone of the operating system;
  • SYSTIMESTAMP: a TIMESTAMP WITH TIME ZONE based on the time zone of the operating system;
  • CURRENT_DATE: a DATE (with time to the second) based on the session time zone;
  • CURRENT_TIMESTAMP: a TIMESTAMP WITH TIME ZONE based on the session time zone;
  • LOCALTIMESTAMP: a TIMESTAMP based on the session time zone;

Finally, there are two ways to get from some other time zone to GMT:

  • AT TIME ZONE 'GMT' will return a TIMESTAMP WITH TIME ZONE
  • SYS_EXTRACT_UTC will return a TIMESTAMP

Both these methods require some kind of TIMESTAMP as input, and the output then needs to be CAST as a DATE.

So any of the following will give you a DATE value in the GMT time zone:

select 
cast(systimestamp at time zone 'gmt' as date),
cast(current_timestamp at time zone 'gmt' as date),
cast(localtimestamp at time zone 'gmt' as date),
cast(sys_extract_utc(systimestamp) as date),
cast(sys_extract_utc(current_timestamp) as date),
cast(sys_extract_utc(localtimestamp) as date)
from dual;

P.S. Don't pass SYSDATE to the function! Let the function use one of the built-in timestamp functions. If you pass a date to the function, the function has to assume what time zone it belongs to.

Upvotes: 1

Related Questions