CODEWITHSUNDEEP
jsonoracle-databasedateplsql
doberkofler
doberkofler

Reputation: 10361

How to convert a JSON date to an Oracle date in local time

Let's say today is 30-NOV-2016 at 00:00:00 in Europe (GMT+1) and a frontend JavaScript application JSON.stringify(new Date(2016, 11-1, 30)) send a JSON (stringified) date to an Oracle backend.

This date would arrive as the string "2016-11-29T23:00:00.000Z" and now I would like to convert this string to a proper Oracle DATE in the (again) local timezone (GMT+1).

I expected

SELECT CAST(TO_TIMESTAMP_TZ('2016-11-29T23:00:00.000Z', 'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"') AT LOCAL AS DATE) FROM DUAL;

to do the trick, but this actually returns me the UTC date 29.11.2016 23:00:00 and not the correct local date 30.11.2016 00:00:00.

This should be quite straightforward but I cannot seem to figure out, what I'm doing wrong?

Upvotes: 3

Views: 10043

Answers (1)

Alex Poole
Alex Poole

Reputation: 191295

When you use to_timestamp_tz() but don't actually specify the time zone in the conversion it defaults to your system time zone, which presumably isn't UTC. The timestamp with time zone you are generating is therefore already in your local system time zone, so at local isn't doing anything.

You can convert to a plain timestamp instead, and declare the time zone as UTC with the from_tz() function; then still use the AT LOCAL expression to change it to your local time zone:

alter session set time_zone = 'Europe/Vienna';

SELECT CAST(
  FROM_TZ(TO_TIMESTAMP('2016-11-29T23:00:00.000Z', 'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"'),
    'UTC') AT LOCAL AS DATE) FROM DUAL;

CAST(FROM_TZ(TO_TIM
-------------------
2016-11-30 00:00:00

Breaking that down a bit:

  1. TO_TIMESTAMP('2016-11-29T23:00:00.000Z', 'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"') converts your string value to a plain TIMESTAMP value, with no time zone information.
  2. FROM_TZ(..., 'UTC') converts that plain timestamp to a time stamp with time zone with the time zone part as UTC - no adjustment is done to any of the date/time elements, it just states those represent a UTC value.
  3. ... AT LOCAL converts to your session time zone (which might not be the same as your system time zone).
  4. CAST(... AS DATE) converts the value in your local time zone to a date; again no adjustment is done to the element values, but you lose the fractional seconds and time zone information.

You could also stick with to_timestamp_tz() but include the UTC code:

SELECT CAST(
  TO_TIMESTAMP_TZ('2016-11-29T23:00:00.000Z' || 'UTC',
    'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"TZR')
  AT LOCAL AS DATE) FROM DUAL;

or replace the Z with UTC:

SELECT CAST(
  TO_TIMESTAMP_TZ(REPLACE('2016-11-29T23:00:00.000Z', 'Z', 'UTC'),
    'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3TZR')
  AT LOCAL AS DATE) FROM DUAL;

All of these assume - correctly, I believe - that the JSON string will always be UTC and the Z can be assumed to be there and to mean that (as it should do, of course).

Upvotes: 4

Related Questions