cancel
Showing results for 
Search instead for 
Did you mean: 

How XSJS manage timestamps

rurban
Associate
Associate
0 Kudos

Dear folks,

in these days I'm struggling with something that looks like an issue and I'd like to have your opinion.

I develop a big portion of custom XSJS code building up a batch process which has to work on some ad hoc tables. We are running HANA 1.0 on prem SPS12 with CET timezone set at OS level.

very often this code inserts and reads records from those tables that contains a lot of timestamp. From the very beginning everything looked fine, we were using standar $.hdb connection and the related metods to execute selects and inserts. All data field were provided using JS data object (we are in CEST timezone).

The issue raised as soon as we produced and sent an email with such timestamps included into the text. Our assumption was this one: as soon as I provide a timestamp to an insert statement via $.hdb this takes care of its UTC conversion an store it into db table with such a timezone.

unfortunately we've seen that this not the case.

I try to explain what happen using this example.

I create a test table with this structure:

entity TESTS_INSERT_TS{

key ID: String(40);

TIMESTAMP: UTCTimestamp;

TIMEZONE: Integer;

};

now I create an XSJS rutine which simply inserts 2 records, one using the new Date() object and, for the other one, we convert such a date value in UTC before inserting.

this is my code:

function convertDateToUTC(oDate) {

return new Date( oDate.getUTCFullYear(), oDate.getUTCMonth(), oDate.getUTCDate(), oDate.getUTCHours(), oDate.getUTCMinutes(), oDate.getUTCSeconds() );

}

var oConnection = $.hdb.getConnection();

var now = new Date();

oConnection.executeUpdate('INSERT INTO "DMMS"."TESTRURBAN.model::TESTS_INSERT_TS" VALUES (?,?,?)',$.util.createUuid(), now, now.getTimezoneOffset());

var utcDate = convertDateToUTC(now);

oConnection.executeUpdate('INSERT INTO "DMMS"."TESTRURBAN.model::TESTS_INSERT_TS" VALUES (?,?,?)',$.util.createUuid(), utcDate, utcDate.getTimezoneOffset());

oConnection.commit();

var result = oConnection.executeQuery('SELECT * FROM "DMMS"."TESTRURBAN.model::TESTS_INSERT_TS"');

$.response.contentType = "text/json"; $.response.setBody(JSON.stringify(oResult));

I ran it at 2017-07-25 19:09:22.002 CEST.

this is the json response:

this is the odata response out of the same tabe

as you can see in those 2 pictures the highlighted TS (the one with UTC conversion) in the JSON format is retrieved correctly providing the UTC string value, the odata one is providing the same UTC format but giving back not the UTC converted date but the CEST one.

of course the other record is 2 hours earlier.

After more tests, we understood that the HANA DB doesn't take care of UTC conversion by default, so in order to render the right TS in our UIs we have to convert date objects using our convertDateToUTC function. Unfortunately, this leads to have a totally wrong timestamps retrived by the executeQuery JSON, sinces it look that, see the first picture, record id 1, that the TS is converted again to UTC.

At the end we insert a record with corresponsing UTC date 2017-07-25T17:09:22.0000000 but XSJS JSON converted gives back 2017-07-25T15:09:22.000Z insted of retreiving the same value.

Do you have any idea about that? to me it looks like a wrong conversion performed by XSJS as soon as retrives timestamp

thanks a lot for any feedback

BR
Roberto

Accepted Solutions (0)

Answers (1)

Answers (1)

BergenMatthias
Discoverer
0 Kudos

Hi Roberto,

did you find a solution to your problem?