4 Replies Latest reply on Sep 11, 2018 7:11 AM by marc.kusters

Trouble converting SAP JSON datetime to timestamp

marc.kusters Aug 27, 2018 11:13 AM

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?

1. Re: Trouble converting SAP JSON datetime to timestamp

shawkins Aug 27, 2018 11:30 AM (in response to marc.kusters)

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.
Actions
2. Re: Trouble converting SAP JSON datetime to timestamp

marc.kusters Aug 27, 2018 12:02 PM (in response to shawkins)

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 +2 147 483 647
Actions
3. Re: Trouble converting SAP JSON datetime to timestamp

shawkins Aug 27, 2018 12:45 PM (in response to marc.kusters)

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.
Actions
4. Re: Trouble converting SAP JSON datetime to timestamp

marc.kusters Sep 11, 2018 7:11 AM (in response to shawkins)

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

Go to original post