Oracle Time Zones Conversion (using from_tz)
Solution 1
to_timestamp() gets a string (VARCHAR2, CHAR ...) if you try to give it a date, then oracle will convert it to a string according to NLS_DATE_FORMAT which might vary in different environments and return unexpected results (as in this case).
What you should do is use to_char first, so your query can 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 right then you want 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
Solution 2
Please try this as well .There is a function for this purpose in oracle
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;
Solution 3
SELECT TO_CHAR(NEW_TIME(systimestamp,'EST','PST'), 'DD-MON-YY HH24:MI:SS') AS converted_timestamp_column FROM DUAL;
Mark Marina
Updated on May 20, 2020Comments
-
Mark Marina almost 4 years
I'm trying to convert a time (date + time) from one time zone to another. In the query below, I'm trying to convert a time from EST ("America/New_York") to PST ("America/Los_Angeles"). The query is partially working; the 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 wrong. It should be 2012-02-13 instead of 2012-02-12.
Am I doing something wrong? Here's my query:
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
-
Mark Marina about 12 yearsThank you very much for the explanation and the help with the query. Really appreciated.
-
Mark Marina about 12 yearsA.B. Cade: I have another question if you don't mind. In my query, I convert the time from 'America/New_York' to 'America/Los_Angeles'. Is it also possible to get the time zone abbreviation ('PST') on top of converting the time. So if the time in New York is 2012-02-14 01:00 PM I could get 2012-02-14 10:00 AM PST for Los Angeles.
-
A.B.Cade about 12 yearsIf I understand you right, then you don't want to get a Date but a VARCHAR2 which shows the date formatted and includes at the end the abbreviation. So, you need to remove the upper "to_date" (since you don't want a date) and add to the to_char's format "TZD"
-
Mark Marina about 12 yearsThanks again ! This is exactly what I needed.
-
Frank Schmitt over 5 yearsThat function
NEW_TIME
works only for a very limited number of timezones, see docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/….