Convert timestamp with timezone to date - new thread

[vojvodina9i]

thread759-289278

I was trying to convert Unix time to our local time, but using the method described in the thread759-289278 would give us the wrong result. For example, if we have ‘timestamp with the time zone’ variable that contains this value:
15-MAR-09 04.20.20.000000000 AM US/PACIFIC and we use to_char (or CAST or any other way to convert it to DATE), we are going to get the wrong result:
15-MAR-09 03.20.20.000000 AM US/PACIFIC.
By purpose, I chose the date when we obey Daylight Saving Time. For some reason, after the conversion, the system is loosing this one hour. I didn’t figure it way?
BTW, CAST doesn’t eliminate “time”. The time component is there, but depending on the format, might not be displayed. This is an example:
SELECT to_char(CAST(((FROM_TZ(CAST((to_date('19700101000000','YYYYMMDDHH24MISS') + NUMTODSINTERVAL( 1237116020, 'SECOND' )) AS TIMESTAMP), 'GMT')
AT TIME ZONE 'US/Pacific') ) as DATE), 'yyyy-mon-dd hh:mm:ss')
FROM DUAL;
And the result would be:
2009-mar-15 03:03:20. So the time is there.

 

[ChrisHunt]

For some reason, after the conversion, the system is loosing this one hour

Presumably, if you only specify the timezone as "US/Pacific", it assumes Pacific Standard Time. If you're using Pacific Daylight Time you need to say so explicitly.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[vojvodina9i]

I think that our problem here is our Server, and the way how it deals with Daylight Saving Time. I think it is still converting between DST and ST (and back) by using old rules (before 2007).