Convert Oracle Time Zones (using from_tz)

Question

I am trying to convert time (date + time) from one time zone to another. In the query below, I am trying to convert time from EST ("America / New York") to PST ("America / Los Angeles"). The request partially works; results:

DATABASE_DATE = 2012-02-13 1:00:00 PM LOCALTIME (what I get): 2012-02-12 10:00:00 AM. 

So, the time is good, but the date is incorrect. It should be 2012-02-13 instead of 2012-02-12.

Am I doing something wrong? Here is my request:

 select to_date( to_char( ( from_tz( to_timestamp( DATABASE_DATE , 'YYYY-MM-DD HH:MI:SS') ,'America/New_York') at time zone 'America/Los_Angeles') ,'YYYY-MM-DD HH:MI:SS') ,'YYYY-MM-DD HH:MI:SS') as localtime from table 

thanks

Answer 1

to_timestamp () gets the string (VARCHAR2, CHAR ...), if you try to assign a date to it, then oracle will convert it to a string according to NLS_DATE_FORMAT, which may vary in different environments and return unexpected results (as in this case). What you need to do is use to_char first, so your request might look like this:

 select to_date(to_char((from_tz(to_timestamp(to_char(DATABASE_DATE, 'YYYY-MM-DD HH:MI:SS PM'), 'YYYY-MM-DD HH:MI:SS PM') ,'America/New_York') at time zone 'America/Los_Angeles'),'YYYY-MM-DD HH:MI:SS PM'),'YYYY-MM-DD HH:MI:SS PM') as localtime from table 

UPDATE: if I understand you correctly, you need something like this:

 select to_char((from_tz(to_timestamp(to_char(DATABASE_DATE, 'YYYY-MM-DD HH:MI:SS PM'), 'YYYY-MM-DD HH:MI:SS PM') ,'America/New_York') at time zone 'America/Los_Angeles'),'YYYY-MM-DD HH:MI:SS PM TZD') as localtime from table 

Answer 2

 SELECT TO_CHAR(NEW_TIME(systimestamp,'EST','PST'), 'DD-MON-YY HH24:MI:SS') AS converted_timestamp_column FROM DUAL; 

Answer 3

Please try this as well. For this purpose, oracle has a function

https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2036.htm

 select NEW_TIME (TO_DATE ('2011/11/11 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') from dual;