Add timezone to CurrDateTime()

Builtin & User functions
On our radar
The cloud environments have their system time in UTC, but not everyone is in this timezone.
It seems that when using SQL Server as database, this time cannot be changed. So we have to take care of this in the code. 
Easy to do, just make an action that takes the CurrDateTime and converts it to the wanted timezone. However, this function cannot be used in the default value of the attribute of an entity, since that needs to be a date literal.
A solution for this would be to have the timezone as input parameter for the CurrDateTime() function, which is considered a date literal by the platform.
Created on 13 Mar 2016
Comments (3)
There are already a bunch of components in the Forege for timezone conversion, but this is still a decent idea.

That said... it is much better I've found to do the conversion on READ of the DB in many apps, simply because the time zone can change, or you have users in different time zones. I'd rather know what TZ the date/time is in the DB and convert it, then to not be sure.

I agree with Justin that it's a decent idea, but it doesn't go far enough.  Most of the time you need time zone for times stored in the database not just for CurrDateTime().  For example, a user in the US Eastern time zone adds a record and the CurrDateTime() is saved in the server time zone as the time the record was created.  Now a user in the US Pacific time zone looks at that record and will see the server time when the record was created.  Ideally, times stored in the database would always be UTC, regardless of the server time.  Then it would be very easy to change all the time related functions to include an optional timezone element.  

Until all times are stored as UTC you will always have issues with this.  Let's says you start in the cloud and your server time is something other than UTC and then after years of history, you bring that application in house where the server time is something different.  You would have to perform updates to all the date time fields in all the historical data and while doing this will obviously change the times it can also change dates, which could affect all sorts of reports, searches, etc.

I appreciate the attempts of the Forge components to deal with this but it's just a stop gap.  To be a global platform this should be handled completely and automatically by the platform and all the built-in datetime related functions.


Agreed that my proposed solution is not the best, and I would probably do like you guys suggest and keep the data on the database in UTC. And do all "conversion" on the application itself.
However, we have a client that requested to have all times stored in the database in CET. In that case, an optional timezone parameter on the CurrDateTime would be very useful.