James Gallagher James Gallagher - 3 months ago 12
SQL Question

Oracle Date datatype, transformed to 'YYYY-MM-DD HH24:MI:SS TMZ' through SQL

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?

Answer

There's a bit of confusion in your question:

  • a 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.
  • When you want to display a date, either on screen or to send it to another system via a character API (XML, file...), you use the TO_CHAR function. In Oracle, a Date has no format: it is a point in time.
  • Reciprocally, you would use TO_TIMESTAMP_TZ to convert a VARCHAR2 to a TIMESTAMP, but this won't convert a Date to a TIMESTAMP.
  • You use FROM_TZ to add the time zone information to a TIMESTAMP (or a Date).
  • In Oracle, CST is a time zone but CDT is not. CDT is a daylight saving information.
  • To complicate things further, 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