Trouble converting SAP JSON datetime to timestamp

[marc.kusters]

I got the following problem, I tried consuming a SAP SuccessFactor REST service as JSON. This works without problems. However, I'm having trouble converting the date to a normal timestamp.

The format of the date in milliseconds is as follows: \/Date(253402214400000)\/ or a less extreme example \/Date(1441269314000)\/

Getting the value of that is easy with regex replace. However the next step is using FROM_UNIXTIME(). The only problem is that FROM_UNIXTIME() only accepts integer values while a Unix timestamp is at least a long.

Trying to solve it with TIMESTAMPADD does not work since it also only accepts integer values.

Is there something I missed?

[shawkins]

Newer releases have FROM_MILLIS - [TEIID-5026] Add function to transform timestamp into Unix time - JBoss Issue Tracker

If you don't care about the millisecond resolution, you can just divide by 1000 and cast to an integer.

[marc.kusters]

Already tried that:

TEIID30328 Unable to evaluate convert(253402214400, integer): TEIID30384 Error while evaluating function convert

Which is in seconds instedad of miliseconds.

Which is logical why it fails because the value is bigger than allowed on a java integer:

signed   32 bit  -2 147 483 648   [phone]

[shawkins]

Is that your upper bound value? That would be December 30th, 9999.

In general the limit of signed integer seconds is January 2038 - the end of unix time.  If you only need special handling for the max, you can use a case statement, otherwise you'll need to use a UDF if you are on an older version.

[marc.kusters]

That is correct, some fields have dates that are in the year 9999.

I fixed it by creating a virtual procedure which can convert it for me. Perhaps not the most elegant way but it does work and is robust. It also takes into account timezones and seconds/milliseconds.