The best property to store the current datetime in database

Question

I'm storing the registered time of a user as DATETIME, as seen below

+-----+--------------------+
| id  |     Join_Date      |
+-----+--------------------+
|  1  | 2014-12-5 03:21:46 |
+-----+--------------------+
|  2  | 2014-12-3 03:21:46 |
+-----+--------------------+

Does it make a difference between country?

Let's say maybe in China the local time is 3am so the database stores the datetime as 3am while in India the local time is 5am so the database stores the datetime as 5am. Is this logic correct? If yes this is not what I want. What I want is store the datetime in the same timestamp. How can I do this?

mysql has no concept of timezones, nor should it. it's up to you to store what timezone a particular date/time is in. you won't get different times out of the db no matter where you select them. — Marc B, Dec 04 '14 at 20:20
Your application logic depends on this. If this question was tagged as PHP I would recommend using `DateTime` objects [which can handle timezones for you](http://php.net/manual/en/datetime.settimezone.php) and come up with a universal zone, or use UNIX timestamps which use a UTC. — sjagr, Dec 04 '14 at 20:22
You can use a 'timestamp' column to know when a row was created this is always the 'unix epoch' (UTC from a fixed date). The 'join date' column value is up to you. I suggest that you always use 'UTC' time and a separate column that holds the user 'timezone offset' or the 'timezone string'. You may need to ask the user for their 'time zone'. See [the 'Carbon' php date library for flexible date processing](https://github.com/briannesbitt/Carbon). — Ryan Vincent, Dec 04 '14 at 20:33
@RyanVincent you should post your comment as the answer since you mentioned the only sane data type to use in this context - a `timestamp`. — N.B., Dec 04 '14 at 22:02

score 0 · Answer 1 · answered Dec 04 '14 at 20:23

0

Storing it as a DATETIME data type is the best datatype to use. But you should store the date as UTC date and then convert it to the timezone you need.

That way the times are consistent across timezones. Otherwise you can store them in the full time including timezone in the database. For example, in MS Sql Server you can use datetimeoffset.

answered Dec 04 '14 at 20:23

cpisano

11
1

If we really want to discuss what's the best given the parameters in question - the only sane choice is `timestamp` data type, not `datetime`. – N.B. Dec 04 '14 at 22:01

score 0 · Accepted Answer · answered Dec 04 '14 at 20:30

It's rarely needed to store datetime info using locale other than the server. Most of the time, a datetime event is calculated by the server, so unless you are storing a user provided value (birth date, for example) you ought to have it stored as UTC.

And, even with most user-provided datetime values (scheduling an appointment, for example) you have to normalize that value to UTC before storing it in the database. That way dates will remain consistent even if the user timezone changes (during a trip, for example, or when daylight saving are effective.)

The best property to store the current datetime in database

2 Answers2