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

date, oracle, select, sql

Solution

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

Problem

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?

Original source