Hello all,
What is the best date datatype to use when developing a cloud based app?
In my case, the server is in a different country than the users so time zones come into play. What is the best way to plan for this? What changes in code are necessary?
Any other helpful info available?
Thanks
Tim
If you’re using Xojo 2020 or higher, you should be using DateTime. Session has a property called ClientTime and LanguageCode which gives you access to the client browser’s timezone so you can accurately create and convert dates and times to whatever locale the user is in and convert them back to whatever the server is using.
If you’re storing dates, I suggest storing the SecondsFrom1970 double because you can just create a new DateTime using the locale and timezone to get strings that are appropriate for display.
If you’re talking about the SQL data type then use TimeStamp as it’s available within most SQL database brands (more than any other).
Timestamp with time zone in particular. Postgres has full support for it.
I always store date times in GMT / UTC (which SecondsFrom1970 is). Time zone is more a “display time” application in my mind. Kind of the way you would store currency in the equivalent of pennies and only ever add symbols when its time to display.
I’m not sure if it’s best practice, but I really like how ActiveRecord stores the SQLDateTime string because then I can read the value easily when in the database. It is a few more steps to ensure that is UTC though.
Using a TimeStamp with TimeZone on a cloud server, means that a conversion would have to be performed when the data is input. Isn’t this the same as using secondfrom1970?
For example, if the cloud server is in the UK, a user in Germany - that has a different time zone - would not be the same time. So server side stuff would have the wrong time (UK time).
This is the scenario I want to avoid. I want the data stored on the server to reflect the actual date/time of the user.
Perhaps I am missing some logic here…
Tim