Cast timestamp to timestamp with time zone

Question

I am trying convert timestamp to timestamp with time zone with adding GMT time zone:

to_timestamp(to_char(t.time_started, 'DD-MON-YYYY hh24:mi:ss')
  || nvl2(t.time_started, ' GMT', null), ('DD-MON-YYYY hh24:mi:ss tzd'))

but this gets ORA-01812 date format not recognized. How can I specify add the time zone?

Answer 1

You need to use the to_timestamp_tz() function, not just to_timestamp; the 'tzd` format element isn't recognised for that, hence your error:

select to_timestamp_tz(to_char(t.time_started, 'DD-MON-YYYY hh24:mi:ss')
  || nvl2(t.time_started, ' GMT', null), ('DD-MON-YYYY hh24:mi:ss tzd'))
from t;

TO_TIMESTAMP_TZ(TO_CHAR(T.TIME_STARTED,'DD-MON-YYYYHH24:MI:SS')||NVL2(T.TIME_STARTED,'GMT',NULL),('DD-MON-YYYYHH24:MI:SSTZD'))
------------------------------------------------------------------------------------------------------------------------------
05-JAN-15 18.09.28.000000000 EUROPE/LONDON                                                                                     

As you'll notice, you're losing the fractional seconds with the format models you've chosen.

You can also specify the time zone a value represents with the at time zone expression:

select t.time_started at time zone 'GMT' from t;

T.TIME_STARTEDATTIMEZONE'GMT'     
-----------------------------------
05-JAN-15 18.12.53.419495000 GMT    

Or you can use cast, as your question title suggested, which I believe assumes the system time zone:

select cast(t.time_started as timestamp with time zone) from t;

CAST(T.TIME_STARTEDASTIMESTAMPWITHTIMEZONE)
-------------------------------------------
05-JAN-15 18.14.19.236338000 EUROPE/LONDON  

I'm in the UK so that works for me, but if your server is in a different time zone you might then need to convert it.

Answer 2

If the column t.time_started is of type TIMESTAMP, and the request is to interpret this timestamp in a given time zone, then the function FROM_TZ is to be used here.

In contrast, the AT TIME ZONE Clause is used to move a TIMESTAMP WITH TIME ZONE value from one time zone to another time zone.

This means that with the following SELECT statement first an implicit conversion (using the current session time zone) of the TIMESTAMP value into a TIMESTAMP WITH TIME ZONE value takes place and then this value is displayed in another time zone, which may not be the expected result and the solution to the question.

select t.time_started at time zone 'GMT' from t

See the following example which shows the difference. (My session time zone is currently +2:00).

CREATE TABLE t
(
   time_started    TIMESTAMP
);

INSERT INTO t
     VALUES (CURRENT_TIMESTAMP);

SELECT * FROM t;


SELECT FROM_TZ(t.time_started, 'GMT') as T1, t.time_started AT TIME ZONE 'GMT' as T2 FROM t

The result is:

+-----------------------------------+-----------------------------------+
|                T1                 |                T2                 |
+-----------------------------------+-----------------------------------+
| 2019-07-16 12:17:29,068660 +00:00 | 2019-07-16 10:17:29,068660 +00:00 |
+-----------------------------------+-----------------------------------+

Both values are displayed in GMT, but are clearly different.