In what TimeZone to store DateTime in a DB?

Question

Facebook

The web application will be used in different time zones.
In what format (time zone) to store the date and time field in the database?

How to determine the time zone of the client?
I see 2 options:
1. Store in UTC, determine the time based on the client's IP address.
2. Store in UTC, the client sets the time zone manually in the profile.

I wonder how they store time on Facebook and Vkontakte?

Share your practices and ideas?
Maybe there is a Best Practice?

Answer 1

Keep GMT or Moscow - it doesn't matter. It is important for you to know the offset for each user.
Here's how to do it:
var d = new Date()
var gmtHours = -d.getTimezoneOffset()/60;
console.log("The local time zone is: GMT " + gmtHours);


Answer 2

Store in unix timestamp. In this case, even if the server has an outdated / damaged tzdata database (I think we all remember the misfortunes of this database), then the data will go to the database correct. If you bind to zones, then there is always a chance that the formation of the date will be performed incorrectly and incorrectly recorded in the database.

Answer 3

Store in UTC, when registering / first login, substitute the client's TZ by IP, then use the saved one.

Answer 4

I don’t know about others, but the contact stores (and calculates) dates according to its own (Moscow?) time, this is noticeable when working with dates (subtraction, addition, etc.) If everyone stores it in their own way, this can be fraught, select one format and then bring it to a custom one (it doesn’t matter by ip or profile, but it’s possible this way and that)

Answer 5

>You don't need to store mappings on the server.
Additional complications. Not only do you need a script that will parse all the timestamps on the page and convert them locally (and if NoScript is worth it?), You also need to send local tz in requests related to time.
It seems to me optimal to store in timestamp / UTC, the user's zone in the profile / cookies, and if it does not match with the saved locally defined JS, ask “Your site settings have a time zone of +4, and in your computer settings +12. Do you want to adjust site settings according to local settings?”

Answer 6

or you can not bother and set the client's current time zone when initializing from the database.
those. a person with a time zone of +4 enters, you do SET TIME_ZONE = '+04:00' in the database immediately after creating the connection and work without a single thought about any conversions,
as a result, each, according to his settings, works in his own time zone.
the problem with unauthorized people - well, the previous commentator helps here.
Initially, you set the default server belt, upon loading JS, it corrects it with a local user belt and stores it in cookies until authorization / registration.