Dates unexpectedly offset

Modified on Mon, 23 May, 2022 at 1:43 PM

If you are seeing dates offset by 1 day, or datetimes offset by 1 or more hours, you may be running into the effects of reconfiguring your server's timezone.

This might arise when looking at the NOW() formula function, or when exchanging IOD files between servers or between projects on a server.

This article attempts to explain the phenomenon, and what to do about it.

Omniscope is not particularly tolerant to changes in timezone. When a project is first created, it is associated with the server's timezone at that point in time. Thereafter the project is fixed to that timezone. This ensures the project can be shared or ported between servers, while maintaining a consistent behaviour. Within a given project, its fixed timezone is used universally - regardless of the server's new settings, or the end user's location, system settings or browser.

The issue arises when you have data exchanged between different timezones (i.e. between 2 projects with different timezone configurations). Either between two servers of different timezones, or between 2 projects which were created under a different configured timezone.

Omniscope represents dates internally as absolute points in time. So 1 Jan 2020 is actually stored as 1 Jan 2020 00:00:00.000 in the project's timezone. If this data is somehow transferred into a project in another timezone, the date will appear to have a non-zero hours element in the new timezone. This can also cause, for example, 1 Jan 2020 to appear as 31 Dec 2019, if the shift is negative.

It is not expected that a production server should routinely change its timezone. There is not currently a dedicated tool to transfer data or convert projects between timezones, or a direct way to view a project's timezone. You can add a Field Organiser block against any data source, add a field, and configure the formula DATETOTEXT(NOW(), "z Z") to see the timezone in effect (for example, in our daily.omniscope.me sandbox, this yields UTC +0000).

To properly solve the issue, you should standardise on one timezone in your server(s), and recreate any projects where it matters and is in the wrong timezone.

If you have a real world use case for intentionally mixing server timezones long-term, or need help working with this problem, please contact [email protected].