Timezones in Oracle 10

oracletimezone

I have an application that stores its timestamps in Oracle using UTC (Coordinated Universal Time). Another application reads these timestamps from the database, but there is no convenient way to convert the timestamps to another timezone automatically (from within that application). Is it possible to adjust session settings in Oracle to return timezone adjusted timestamps for SELECT queries?

Best Answer

Since the data is stored in a TIMESTAMP column, you cannot automatically convert it. You'll have to write some code that tells Oracle what time zone to treat the data as being stored in the GMT time zone and tell it that you want it converted to the session's time zone. You would get the automatic behavior you are looking for if you used the TIMESTAMP WITH LOCAL TIME ZONE data type instead. If you used the TIMESTAMP WITH TIME ZONE data type, you would avoid having to tell the database what time zone the timestamp comes from when you write the conversion.

Assuming that your session sets its time zone properly

ALTER SESSION SET time_zone = '-8:00'

for example, would tell Oracle that the current session is 8 hours before GMT (currently the Pacific time zone), if the data is stored in a TIMESTAMP column, you'd need something like

FROM_TZ( ts, 'GMT' ) AT TIME ZONE sessiontimezone

If the data is stored in a TIMESTAMP WITH TIME ZONE column, you'd just need

ts_tz AT TIME ZONE sessiontimezone

If the data is stored in a TIMESTAMP WITH LOCAL TIME ZONE column, you would just select that column.

An example

SQL> ed
Wrote file afiedt.buf

  1  create table foo(
  2    ts timestamp,
  3    ts_tz timestamp with time zone,
  4*   ts_local_tz timestamp with local time zone )
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

  1  insert into foo
  2    values( sys_extract_utc( systimestamp ),
  3            systimestamp,
  4*           systimestamp )
SQL> /

1 row created.

SQL> select from_tz( ts, 'GMT' ) at time zone sessiontimezone ,
  2         ts_tz at time zone sessiontimezone,
  3         ts_local_tz
  4    from foo;

FROM_TZ(TS,'GMT')ATTIMEZONESESSIONTIMEZONE
---------------------------------------------------------------------------
TS_TZATTIMEZONESESSIONTIMEZONE
---------------------------------------------------------------------------
TS_LOCAL_TZ
---------------------------------------------------------------------------
02-MAY-13 01.59.03.171000 PM -08:00
02-MAY-13 01.59.03.171000 PM -08:00
02-MAY-13 01.59.03.171000 PM

In response to the comment below from DylanKlomparens -- you cannot just apply the AT TIME ZONE to a TIMESTAMP and expect to get the proper result so I'm not sure that I understand what you are seeing. If you just to an AT TIME ZONE on a plain TIMESTAMP, the timestamp is simply treated as having the time zone that you specified. It doesn't change the time. It also doesn't depend on the session time zone. In all these cases, if ts represents a UTC time, the PST version represents the wrong time-- there should be an 8 hour difference between the starting and ending values.

SQL> select ts from foo;

TS
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM

SQL> select ts at time zone 'PST' from foo;

TSATTIMEZONE'PST'
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM PST

SQL> alter session set time_zone = '-0:00';

Session altered.

SQL> select ts at time zone 'PST' from foo;

TSATTIMEZONE'PST'
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM PST

SQL> alter session set time_zone = '-4:00';

Session altered.

SQL> select ts at time zone 'PST' from foo;

TSATTIMEZONE'PST'
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM PST

In response to Vincent's comment-- A TIMESTAMP WITH LOCAL TIME ZONE does adjust automatically to the session's time zone regardless of the server's time zone. As I adjust my session's time zone, the results change as well.

SQL> select ts_local_tz from foo;

TS_LOCAL_TZ
---------------------------------------------------------------------------
02-MAY-13 05.59.03.171000 PM

Elapsed: 00:00:00.00
SQL> alter session set time_zone = '-0:00';

Session altered.

Elapsed: 00:00:00.00
SQL> select ts_local_tz from foo;

TS_LOCAL_TZ
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM

Elapsed: 00:00:00.00
SQL> alter session set time_zone = '+4:00';

Session altered.

Elapsed: 00:00:00.00
SQL> select ts_local_tz from foo;

TS_LOCAL_TZ
---------------------------------------------------------------------------
03-MAY-13 01.59.03.171000 AM

Elapsed: 00:00:00.00
Related Question