CODEWITHSUNDEEP
oracle-databasetimezone
Tomáš Zato
Tomáš Zato

Reputation: 53149

Converting TIMESTAMP to DATE with respect to timezone

I was inspecting how are DATEs stored in database. Consider the following statement:

SELECT
CAST (to_timestamp_tz('2018-12-05T10:00:00+01:00', 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') AS DATE) AS PRAGUE_TIME,
CAST (to_timestamp_tz('2018-12-05T10:00:00+00:00', 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') AS DATE) AS GMT_TIME
FROM DUAL

Results in:

PRAGUE_TIME          GMT_TIME
2018-12-05 10:00:00  2018-12-05 10:00:00

The times are the same, despite one being created from a +1 timezone offset. Just to be sure, I added further conversion to string:

SELECT
TO_CHAR(CAST (to_timestamp_tz('2018-12-05T10:00:00+01:00', 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') AS DATE),'YYYY-MM-DD HH24:MI:SS') AS PRAGUE_TIME,
TO_CHAR(CAST (to_timestamp_tz('2018-12-05T10:00:00+00:00', 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') AS DATE),'YYYY-MM-DD HH24:MI:SS') AS GMT_TIME
FROM DUAL


PRAGUE_TIME          GMT_TIME
2018-12-05 10:00:00  2018-12-05 10:00:00

Same results. So how can I convert TIMESTAMP to DATE without loosing the timezone information?

Upvotes: 0

Views: 1219

Answers (1)

MT0
MT0

Reputation: 167981

how can I convert TIMESTAMP to DATE without loosing the timezone information?

You cannot.

  • A DATE only has year, month, day, hour, minute and second components.
  • A TIMESTAMP has all those components plus nanoseconds and optionally time zone or time zone offset components.

If you convert from a TIMESTAMP to a DATE then you will lose the information that the DATE cannot store.

What you can do is convert all the TIMESTAMPs to the same time zone using, for example, AT TIME ZONE 'UTC':

SELECT CAST( TIMESTAMP '2018-12-05 10:00:00+01:00' AT TIME ZONE 'UTC' AS DATE )
         AS PRAGUE_TIME_AS_UTC,
       CAST( TIMESTAMP '2018-12-05 10:00:00+00:00' AT TIME ZONE 'UTC' AS DATE )
         AS GMT_TIME_AS_UTC
FROM   DUAL

Results:

|   PRAGUE_TIME_AS_UTC |      GMT_TIME_AS_UTC |
|----------------------|----------------------|
| 2018-12-05T09:00:00Z | 2018-12-05T10:00:00Z |

Upvotes: 3

Related Questions