CODEWITHSUNDEEP
databasedatedatabase-design
user3260014
user3260014

Reputation: 13

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?

Upvotes: 1

Views: 315

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1499780

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.

Upvotes: 3

Anid Monsur
Anid Monsur

Reputation: 4538

You should store the times in your database using your servers local time. When setting up a user, you should let them set their timezone or you can detect it via JavaScript (although not very reliably).

After someone leaves a comment, you can convert it to the local server time and store it.

When someone views comments, you can use their timezone to convert from the server timezone to their own so that they see the correct time.

The key is storing timezones for each user.

Upvotes: -1

Related Questions