Oracle database DATE data type does not store timezone information

Oracle database DATE data type does not store timezone information

The Oracle database DATE data type does not store timezone information, so it will be good to consistently store GMT time in a DATE column and use the time in GMT in code. The idea is that we also save the current timezone of a user, so that the code can always calculate the local time of the user by shifting the GMT time.

However, to be convenient, we often set time using local time. An example query which stores a GMT date converted from local time could be:

  1. UPDATE users
  2. SET dateline = FROM_TZ(CAST(TO_DATE(
  3.     '2011-05-03 20:00:00', 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP),
  4.     'America/Los_Angeles'
  5.   ) AT TIME ZONE 'GMT'
  6. WHERE user_id = 99;

where '2011-05-03 20:00:00' is local time, and the local timezone is 'America/Los_Angeles'.

You can use the following query to list all the timezone names:

SELECT tzname FROM v$timezone_names;

Now that we store time in GMT, keep in mind that when we compare the values of DATE columns with the SYSDATE function which returns the local time of database, we should first make sure that the database timezone is GMT. This can be verified by

SELECT DBTIMEZONE FROM DUAL; /* should be +00:00 */

Note that the timezone of database could be different from the timezone of the session, i.e., the timezone of the current connection to database:

SELECT SESSIONTIMEZONE FROM DUAL; /* e.g., -07:00 */

While the DATE data type stores up to seconds, the TIMESTAMP data type stores up to fractional seconds. There is also the TIMESTAMP WITH TIME ZONE data type.

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.

More information about formatting options

To prevent automated spam submissions leave this field empty.