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

Question

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?

score 4 · Accepted Answer · edited May 23 '17 at 10:34

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:

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.
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.
... AT LOCAL converts to your session time zone (which might not be the same as your system time zone).
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).

Z (aka "Zulu time") represents UTC - as per [the answers to this question](http://stackoverflow.com/questions/9706688/what-does-the-z-mean-in-unix-timestamp-120314170138z). I came across that the last time I tangled in a timezone related question; that was an "oooooohhhh" moment for me! *{:-) — Boneist, Nov 01 '16 at 12:04
Yes, but I've seen that tacked on automatically to a value that wasn't actually UTC. That doesn't seem to be an issue here but just covering myself... — Alex Poole, Nov 01 '16 at 12:13
that would be ... evil! Guess it's not a very well known thing, then — Boneist, Nov 01 '16 at 12:34
@AlexPoole Thank you very much. The syntax is a little hard to understand but now I get it. So to convert a DATE in local time to a JSON string in UTC you would go like SELECT TO_CHAR(CAST(TO_DATE('2016-11-30', 'YYYY-MM-DD') AS TIMESTAMP) AT TIME ZONE 'UTC', 'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"') FROM DUAL; ? — doberkofler, Nov 01 '16 at 18:13
@materialdreams - yes, that would work. If you were starting from a timestamp with time zone, you can convert slightly more easily, e.g. from the current session time: `TO_CHAR(SYS_EXTRACT_UTC(current_timestamp), 'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"')` - or using systimestamp for the system time zone, if that's different - but you can't pass a DATE directly to that. — Alex Poole, Nov 01 '16 at 18:21

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

1 Answers1