Add the structure DateTimeOffset to OutSystems

On our radar
Props to the fellow developers who have to develop applications on several different timezones!
I feel your pain! 

It would be so cool and easier if the OutSystems Platform could support the DateTimeOffset structure so that we could store date and time value, together with an offset that indicates how much that value differs from UTC. Thus, the value always unambiguously identifies a single point in time.

Created on 29 Jan 2016
Comments (2)
well, since we are developing in the cloud, chances are you are in different timezones.

Speaking as someone who dealt with a global SaaS application with hundreds of thousands of users and hundreds of millions of rows in some tables, I think I have learned a few lessons around timezones...

The last thing I want to do is store the offset in the DB. That is a really, REALLY dangerous idea:

* Offsets change frequently. Literally twice a year for most countries ("daylight savings"). Part of the year, Eastern US is UTC -4. Part of the year it is UTC -5.

* Offsets change at different times of the year. Western Europe changes for daylights savings a few weeks before or after the US, for example.

* The offset you need to use may change, causing you to smash the DB with a massive update. Let's say you have a multi-tenant application, and each tenant might be in a different timezone. Then the tenant changes their timezone... you have millions of records to go and update... all over the place... with the new timezone. That will just crush the DB server.

No, the way you do this is store a timezone with your tenant, and convert on display. Use some smart caching (like cache the lookups for the timezones...) to prevent DB queries and gain speed.