I’m curious what everyone thoughts are on storing dates/times as UTC in the database… as a general rule of thumb it seems to be recommended.
My understanding is that you would store times in their UTC format in the database and would always need to be converting it to the users local time in the front end, and then converting any of their inputs back into UTC time to store in the DB?
What if all of your users were from the one country, or Timezone. Would you still do it?
Any thoughts or input appreciated… just a general chat about time storage in the DB’s
Well to store or not the dates in UTC depends on your needs.
Do you want to be global and have all dates and times presented to the user exactly in their local time? Have users from different time zones that need to know the exact time of an event in their local time?
If this is the case then you should do that and convert indeed to local time on the front end.
In many cases however like for example make an appointment with a doctor or other local activities you really want only the exact local time to be chosen and not the global time. So then there is definitely no need for UTC time.
Yes, I sense it really depends on what information you are storing…
… if the user needs to see something in relative time - like how many minutes ago an item was locked for editing - then you need UTC if there is any risk at all that users will be in a different time zone.
… if you need something in absolute time as per George’s example then it will depend on what you want to present different users.
Thanks for shining a light on an important topic, Philip!
I just have really bad memories of a horrible ‘feature’ of IBM Cognos reporting (BI reporting tool) that i used.
The data we used was very time/interval centric and it was stored in the database in GMT time (which i now understand why) but it was never converted to a different timezone when you built reports with the data
So you would ask for data for a time period and it would give you data that was 10 hours our of sync (as my timezone was GMT+10).
So to get the data you needed, you had to ask for a time frame 10 hours ago… horrible UI.
Anyway, i guess they just never converted the time in the front end between server and UI… and made the user do it in Excel.
I will almost always store dates as UTC, even if it’s a local application and may never require internationalisation.
Some points to consider:
When dealing with daylight savings, working out minutes or hours between two dates will not work out well if the date happens to be on the day that the clocks are moved forwards or backwards by an hour.
I know that DST is being re-considered across Europe. If daylight savings is dropped or changed then you’ll need a table to keep track of that.
Fractional time zones. Good luck.
Year changes - if I submit some data at 23:30 on 31 Dec (because I might not be fun at parties) did I submit it in 2020 or 2021?
There’s probably a few other points I haven’t considered but in most cases it’s best to store as UTC and let the front end display that date in a format that’s best for your user. Wappler does a good job at manipulating dates. Moment.js also tracks timezones and I believe historical changes too.
In this case UTC is the best choice since it doesn't have daylight savings.
You don't have to keep track of this yourself, your os/server keeps track of this (if you keep it up-to-date).
Same as above.
It depends on the timezone where you are in and where the other user is in. In your timezone it can be 2020 and in the other's timezone it maybe is 2021. If you store the datetime in UTC and then on the client show it in his local time, then he will see a different date then you. If you want him to see the same date as you, so not translated then you shouldn't convert it to UTC.
In the first Wappler release we returned all datetime fields from the database as UTC strings and when formatted with App Connect it was shown to the users in their timezone. But this changed when users where complaining the the database didn't return the datetime exactly as it was stored in the database. It was expected when you insert 2020-12-31 23:30:00 into the database that it returns the exact same result again. So everyone gets the 2020 back as year and not 2021 if you where in a different timezone.
We are now looking on how to improve this behavior. In ASP/PHP server connects returns a timestring like 2020-12-31 23:30:00 while nodeJS now returns a UTC timestring like 2020-12-31T21:30:00Z. We want to support both, because both have there own use-cases. The question is on how to implement it.
If it wasn’t already mentioned, keep in mind that a timestamp field behaves differently from a datetime field (at least in mysql). A timestamp is stored as utc and presented according to the session time zone. A datetime however, is stored as-is.
It is true that timestamp is stored as UTC and datetime as-is, but you normally don’t see any difference in behavior unless you change the timezone in mysql. You will get it out as you put it in.
