Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What data type are you using to store the data? TIMESTAMP? TIMESTAMP WITH TIME ZONE? Or TIMESTAMP WITH LOCAL TIME ZONE? –  Justin Cave May 2 '13 at 21:07
    
A regular TIMESTAMP. –  Dylan Klomparens May 2 '13 at 21:09
2  
You can use FROM_TZ to convert a timestamp to a timestamp in a different timezone: docs.oracle.com/cd/E11882_01/server.112/e26088/… –  a_horse_with_no_name May 2 '13 at 21:14
1  
Is there a way to do this implicitly with every SELECT and UPDATE? So for example, the database's timezone is set to UTC but the session timezone is set to EST. So any SELECT that happens would automatically return time in EST... –  Dylan Klomparens May 2 '13 at 21:15
    
@DylanKlomparens - The automatic conversion of time zones is something that the TIMESTAMP WITH LOCAL TIME ZONE data type does. You're not even storing a time zone with your data so Oracle has no idea that it should assume that they are UTC. –  Justin Cave May 2 '13 at 21:19
show 2 more comments

1 Answer

up vote 5 down vote accepted

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
share|improve this answer
    
Thanks for the thoughtful answer, Justin! I tried this out in a test database and found that I can SELECT my_time_column AT TIME ZONE 'PST' FROM test_table;, where my_time_column is a TIMESTAMP. It appears that setting the session timezone globally influences the TIMEZONE type. –  Dylan Klomparens May 2 '13 at 22:50
    
@DylanKlomparens - I updated my answer. You can't just do an AT TIME ZONE on a plain TIMESTAMP assuming you want the time component to change. Either you actually have a TIMESTAMP WITH TIME ZONE or something else is going on. –  Justin Cave May 2 '13 at 23:11
    
TIMESTAMP WITH LOCAL TIMEZONE are set at the server time zone, not the client time zone. Changing session time zone won't change the output of a query. –  Vincent Malgrat May 3 '13 at 9:18
    
@VincentMalgrat - I updated my answer to show that the results are returned based on the session's time zone. –  Justin Cave May 3 '13 at 9:22
    
Oops you're right, I messed up my test case ! –  Vincent Malgrat May 3 '13 at 9:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.