Are timestamps stored as UTC timestamps or local timestamps in the database?

[Q&A] General
1

Hi there,

Are timestamps stored a UTC timestamps or local timestamps (server time)? E.g. the server is in a timezone with an offset of +2h. A timestamp in server time is 1 January 15:00h. Is this timestamp stored in the DB as 1 January 15:00h OR 1 January 13:00h?

2

Hi,
Timestamps are stored in local server time.
The date time extensions plug-in has process actions available to convert timestamps between local time and UTC.

3

Hi @Joost,

How am I supposed to do this from the Novulo UI within a data definition? There is a .getutcoffset(), but this does not return the UTC offset but a UNIX timestamp.

4

for the current time you can take DateTime:utcnow()
For other timestamps you can use this:
DateTime:now().getutcoffset().timestamptoutc()

Where you replace the first datetime:now() with the datetime field.

5

Hi @Helena

Given solution is not working properly, if you use DateTime:utcnow() or DateTime:now().getutcoffset().timestamptoutc() still the offset is the offset of the servertime instead of UTC.

To get a proper offset in a formatted datetime you could use a work around using datetime:now().getutcoffset().timestamptoutc().format(“yyyy-MM-ddTHH:mm:ssZ”) but when you reaplace datetime:now() with the datetime field it will give a query error.

I already created a SR (SR67708) for above issue

6

I noticed the SR. The difference is that datetime:now().getutcoffset().timestamptoutc().format(“yyyy-MM-ddTHH:mm:ssZ”) can be completely done by the datahandler, while replacing it with a db field, it will have to be done in SQL, where you found a bug.

In architect you could get around this by first caching your timestamp value and then apply the functions to the cached field: cacheddatetime.getutcoffset().timestamptoutc().format(“yyyy-MM-ddTHH:mm:ssZ”)

In exportfields the only workaround I can think of is using an xslt if exporting to xml or html, or otherwise let excel do your calculations if exporting to an XLSX template.

With a bugfix this workaround wouldn’t be needed.

7

I understand the difference and what is happening in the query. Unfortunately we need the function in a data definition, so both workarounds won’t work in this case.

Is there an estimation when this bug could be fixed?