Friday, August 21, 2009

Converting Oracle TIMESTAMP WITH TIME ZONE to Current TZ

I see answers to this question all over the Web, usually involving some arithmetic with abbreviations such as "EDT", the use of hacky Oracle extension functions such as NEW_TIME, etc. Forget about that. Here's how you do it: use CAST(). If this seems pedantic, it's not. I recently had to deal with an Oracle database in which times were stored as epoch times, i.e. milliseconds since January 1, 1970 UTC (e.g. new java.util.Date().getTime()). So you can get the beginning of the epoch in Oracle SQL like so:

SELECT TIMESTAMP '1970-01-01 00:00:00 +00:00' FROM DUAL;

That'll get you a timestamp in terms of Greenwich Mean Time (GMT or UTC):

SELECT TO_CHAR(TIMESTAMP '1970-01-01 00:00:00 +00:00', 'TZR') FROM DUAL;

If I've got "epoch time" in ms, I can easily convert it to a timestamp relative to UTC:

SELECT TIMESTAMP '1970-01-01 00:00:00 +00:00' + NUMTODSINTERVAL(epoch_time / 1000, 'SECOND') FROM my_table;

How do I then display, and especially export these values relative to my current time zone? Casting to TIMESTAMP WITH LOCAL TIME ZONE doesn't quite work; by default, the time zone isn't displayed for this column, and the 'TZR' format doesn't work on it (I get an Oracle error, which confused me. So CAST() them to TIMESTAMP WITH LOCAL TIME ZONE, then CAST() to TIMESTAMP WITH TIME ZONE. Oracle does all the work, and I don't have to hard-code any information in the query about my time zone, or rely on EXTRACT(). So:

SELECT CAST(CAST(TIMESTAMP '1970-01-01 00:00:00 +00:00' AS TIMESTAMP WITH LOCAL TIME ZONE) AS TIMESTAMP WITH TIME ZONE) FROM DUAL;





5 comments:

Vacao said...

I've been combing the web for a simpler way and thought I'd share.

If you already have a TIMESTAMP WITH TIME ZONE, and want to show it in the local timezone, you can use "AT LOCAL" to have the database convert it.

SELECT CAST(TIMESTAMP '1970-01-01 00:00:00 +00:00' AS TIMESTAMP WITH TIME ZONE) AT LOCAL FROM DUAL;

You can also use "AT TIME ZONE [string expression]" to convert to an arbitrary time zone.

Note, not sure if postgres or whatnot works the same -- I've been using Oracle.
See here:
http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1007699

DomFilk said...

you can try this free online unix timestamp creator, you can get the current time stamp.

Anonymous said...

Leap Seconds ?

Unknown said...

site web n7z45a8a69 fake louis vuitton replica bags joy replica bags uk replica gucci bags x9j50k7q11 replica evening bags check h7s65i4r64 best replica bags replica bags louis vuitton

Unknown said...

q5e00a1d43 p8y11l9d37 z4j49u2q72 r2t85u9q70 o8c50c2c74 j8t33e9r54