materialdreams materialdreams - 1 month ago 7
JSON Question

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?

Answer

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:

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

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).