I have an application that uploads to an Oracle Data datatype column via:
TO_TIMESTAMP_TZ('2012-10-09 1:10:21 CST','YYYY-MM-DD HH24:MI:SS TZR')
I now need to pull the following format and timezone from this Data column: 'YYYY-MM-DD HH24:MI:SS CDT'
Note: that the date is uploaded in CST but needs to be returned in CDT.
I have Google'd but have only found following for Date datatypes:
SELECT dateColumn From dateTable;
09-NOV-12
SELECT TO_CHAR(dateColumn,'YYYY-MM-DD HH24:MI:SS') From dateTable;
2012-10-09 1:10:21
I have tried the following also:
TO_TIMESTAMP_TZ(dateColumn,'YYYY-MM-DD HH24:MI:SS CDT')
data format not recognized
TO_TIMESTAMP_TZ(CRTE_DT,'YYYY-MM-DD HH24:MI:SS TZH:TZM')
09-NOV-12 1:10:21 AM +01:00
don't understand why this does not come back as YYYY-MM-DD?
How can I solve this problem?
There's a bit of confusion in your question:
Date datatype doesn't save the time zone component. This piece of information is truncated and lost forever when you insert a TIMESTAMP WITH TIME ZONE into a Date.TO_CHAR function. In Oracle, a Date has no format: it is a point in time.TO_TIMESTAMP_TZ to convert a VARCHAR2 to a TIMESTAMP, but this won't convert a Date to a TIMESTAMP.FROM_TZ to add the time zone information to a TIMESTAMP (or a Date).CST is a time zone but CDT is not. CDT is a daylight saving information.CST/CDT (-05:00) and CST/CST (-06:00) will have different values obviously, but the time zone CST will inherit the daylight saving information depending upon the date by default.So your conversion may not be as simple as it looks.
Assuming that you want to convert a Date d that you know is valid at time zone CST/CST to the equivalent at time zone CST/CDT, you would use:
SQL> SELECT from_tz(d, '-06:00') initial_ts,
2 from_tz(d, '-06:00') at time zone ('-05:00') converted_ts
3 FROM (SELECT cast(to_date('2012-10-09 01:10:21',
4 'yyyy-mm-dd hh24:mi:ss') as timestamp) d
5 FROM dual);
INITIAL_TS CONVERTED_TS
------------------------------- -------------------------------
09/10/12 01:10:21,000000 -06:00 09/10/12 02:10:21,000000 -05:00
My default timestamp format has been used here. I can specify a format explicitely:
SQL> SELECT to_char(from_tz(d, '-06:00'),'yyyy-mm-dd hh24:mi:ss TZR') initial_ts,
2 to_char(from_tz(d, '-06:00') at time zone ('-05:00'),
3 'yyyy-mm-dd hh24:mi:ss TZR') converted_ts
4 FROM (SELECT cast(to_date('2012-10-09 01:10:21',
5 'yyyy-mm-dd hh24:mi:ss') as timestamp) d
6 FROM dual);
INITIAL_TS CONVERTED_TS
------------------------------- -------------------------------
2012-10-09 01:10:21 -06:00 2012-10-09 02:10:21 -05:00
to convert a TimestampTZ in oracle, you do
TO_TIMESTAMP_TZ('2012-10-09 1:10:21 CST','YYYY-MM-DD HH24:MI:SS TZR')
at time zone 'region'
see here: http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG264
and here for regions: http://docs.oracle.com/cd/E11882_01/server.112/e10729/applocaledata.htm#NLSPG0141
eg:
SQL> select a, sys_extract_utc(a), a at time zone '-05:00' from (select TO_TIMESTAMP_TZ('2013-04-09 1:10:21 CST','YYYY-MM-DD HH24:MI:SS TZR') a from dual);
A
---------------------------------------------------------------------------
SYS_EXTRACT_UTC(A)
---------------------------------------------------------------------------
AATTIMEZONE'-05:00'
---------------------------------------------------------------------------
09-APR-13 01.10.21.000000000 CST
09-APR-13 06.10.21.000000000
09-APR-13 01.10.21.000000000 -05:00
SQL> select a, sys_extract_utc(a), a at time zone '-05:00' from (select TO_TIMESTAMP_TZ('2013-03-09 1:10:21 CST','YYYY-MM-DD HH24:MI:SS TZR') a from dual);
A
---------------------------------------------------------------------------
SYS_EXTRACT_UTC(A)
---------------------------------------------------------------------------
AATTIMEZONE'-05:00'
---------------------------------------------------------------------------
09-MAR-13 01.10.21.000000000 CST
09-MAR-13 07.10.21.000000000
09-MAR-13 02.10.21.000000000 -05:00
SQL> select a, sys_extract_utc(a), a at time zone 'America/Los_Angeles' from (select TO_TIMESTAMP_TZ('2013-04-09 1:10:21 CST','YYYY-MM-DD HH24:MI:SS TZR') a from dual);
A
---------------------------------------------------------------------------
SYS_EXTRACT_UTC(A)
---------------------------------------------------------------------------
AATTIMEZONE'AMERICA/LOS_ANGELES'
---------------------------------------------------------------------------
09-APR-13 01.10.21.000000000 CST
09-APR-13 06.10.21.000000000
08-APR-13 23.10.21.000000000 AMERICA/LOS_ANGELES
Source: Stackoverflow.com