Convert date oracle's timestamp to ISO-8601 date datatype [duplicate]-CodePudding

I am looking for a query in Oracle 12c to convert a 18-12-2003 13:15:00 to 2003-12-18T13:15:00 01:00 in European time zone as datetime datatype.

Is that possible or am I missing something?

CodePudding user response：

First you need to convert the string (I assume your input data is a string, rather than proper DATE or TIMESTAMP value), then you can attach the time zone.

There are several time zones in Europe, you must be more specific.

FROM_TZ(TO_TIMESTAMP('18-12-2003 13:15:00', 'DD-MM-YYYY HH24:MI:SS'), 'Europe/...')

Once you did that, you can output the result in arbitrary format:

TO_CHAR(
   FROM_TZ(TO_TIMESTAMP('18-12-2003 13:15:00', 'DD-MM-YYYY HH24:MI:SS'), 'Europe/...'),
   'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'
)

CodePudding user response：

If you want to convert all dates in the column to that format for the time zone CET and you are sure that the offset always is 1, then you could do this:

SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', '1:00'),'YYYY"-"MM"-"DD"T"HH24":"MI":"SSTZR') 
   FROM DUAL;

However, the question is what to do with daylight savings time. Is the offset going to change when DST goes into effect ? There are a lot of considerations there - this question (credits to @Wernfried Domscheit) has a nice overview.

For example if you data is in UTC time zone and you want to display it in CET, then you could convert it like this:

Note there is 2 hours offset in summer and 1 in winter.

WITH dates (season,dt) AS
(
  SELECT 'summer', TO_DATE('01-AUG-2021','DD-MON-YYYY') FROM DUAL UNION ALL
  SELECT 'winter', TO_DATE('01-JAN-2021','DD-MON-YYYY') FROM DUAL
)
SELECT dt,
       season,
       TO_CHAR(
         FROM_TZ( CAST( dt AS TIMESTAMP ), 'UTC' )
           AT TIME ZONE 'CET',
         'YYYY-MM-DD HH24:MI:SS TZH:TZM TZR'
       ) AS cet_timezone
FROM   dates;

01-AUG-2021 summer  2021-08-01 02:00:00  02:00 CET
01-JAN-2021 winter  2021-01-01 01:00:00  01:00 CET