Consider a table shops, for each shop i need to persist the time zone based on the country in which shop is located. Is it good to store the time zone as a VARCHAR (ex: value = Asia/Kolkata) ? or is there any other options available to store the time zone info ?
Asked
Active
Viewed 284 times
0
-
Read [this](http://stackoverflow.com/a/3269325/664577) answer (all others are also useful). Following its advice, I would store name, timestamp and the offset. For names `VARCHAR` is fine. You can also get fancy and use a really large [ENUM](https://dev.mysql.com/doc/refman/5.7/en/enum.html). – Anthony Accioly Feb 22 '14 at 20:39
1 Answers
0
As you state, you should store the timezone as a VARCHAR in your database in the form "Asia/Kolkata", "America/Los_Angeles", etc. This is significantly better than storing any type of offset from UTC since that offset can be variable thanks to daylight savings time.
Another good practice is to store all times inside your database as UTC. Doing so will allow you to easily compare times between shops in different timezones -- this is particularly useful when rolling up reports or aggregating data. The user interface (or View layer) should be responsible for converting all UTC times to the shop's local timezone.
alfredaday
- 2,048
- 18
- 14