What date time should I put into the database

We are designing a small application where people can leave comments.

The users of this application could be anywhere in the world. Comments from one can be viewed by everyone else.

Here comes the problem. If some one in India puts in a comment today, then I would store the date and time the comment was made, picked from the local machine.

However, the comment if viewed by someone in the US, then there is chance that the date and time according to the US user could appear to be in the future.

Alternatively, I could use the time on our server. Our server is in north Carolina.

So when some one India makes a comment today, the date that the comment could be stored in the database could be yesterday.

So I am unable to understand what date I should store in the database and how do I display a date and time that is corrected for the local date and time?

Jon Skeet
people
quotationmark

You almost certainly want to store the data in UTC. You then display the time to the user in their local time zone, so it'll never appear to be in the future. Different people around the world will see different times for the same comment, but it will always represent the same "absolute" time.

You might also want to consider using relative times for comments within the last day - so "5 minutes ago" instead of a full date/time, for example.

Using UTC has multiple advantages:

  • It doesn't matter if you move your servers, or if you have multiple servers in different time zones: they'll all be storing the same data
  • UTC doesn't observe daylight saving changes, so you never need to worry about storing an ambiguous time. (When you put the clocks back, for one hour the same times happen twice - so if you stored "1.30am" you might not know whether that's the first time that 1.30 happened or the second.
  • Clients only need to understand their time zone and UTC... and just about everything will understand both of those. You don't need to worry about clients understanding the arbitrary time zone of the original commenter.

people

See more on this question at Stackoverflow