best way to store timestamps

Yii 1.1 General Discussion
1

I’ve been researching the best way to store timestamps in a MySQL DB with no clear answer. I’d like some advice from here.

I am running an app that requires localization. This means users will be entering time values into forms and that need to be converted to the “server’s timezone” before saving, and time values will also be retrieved by users, which in turn need to be converted from the server’s time to the user’s time.

What is the best way to do this? With some researching, it seems that storing time as UTC is the best solution. Is this agreeable?

Are any datetime formats better for this case? Eg, I could do datetime, timestamp, int (holding a Unix timestamp). What’s the best way to FILL these values? Options I see are the mysql functions NOW(), UTC_TIMESTAMP(), or using php functions such as time().

My hypothesis is that UTC_TIMESTAMP() is the best bet for localization. What do you guys think? I’ve never heard of this until today

Also, what is the best method to convert from/to a user’s timezone and displaying these times? Where do you recommend putting this logic? I’m a noob when it comes to localization/internationalization

2

Your approach sounds reasonable. Did you check the DateTime class in PHP? It allows for setting a default timezone and changing the timezone per DateTime object with setTimezone(). So you could use UTC as default zone, create a DateTime object with UTC value from db and afterwards change to the current user’s timezone.

Same on saving a time, except that you’ll have to change timezone to UTC before.

3

This sounds good. I’ll take this approach unless anyone else comments otherwise. Looks pretty darn solid though. I switched hosts a few times, messing up my database each time as the timezones changed. With using UTC_TIMESTAMP() this won’t be a problem. The timezone of mysql is not easily configurable on a external host but the php timezone is.

4

You should indeed store a time in the UTC time zone.

You still have some options: you can store it as text (i.e. Mon, 15 Aug 2005 15:12:46 UTC) or you can store a timestamp (i.e. 1262472353). Which one you want to use depends on the range of times. The former can take any date/time men knows and is described in the RFC 2822 document (link: http://www.faqs.org/rfcs/rfc2822), while the latter is bound by its 32-bit integer limits (currently it rougly ranges from dec 1901 to jan 2038).

Remember that all php functions (like time() or strtotime() ) are bound by the 32-bit integer limits.

5

Seems either will work in my case then. I’m thinking I’ll go with either the datetime or timestamp MySQL data types

6

That makes me wonder again, why some people (including master Qiang ;) ) prefer to store timestamps as INT (aka. UNIX timestamps) even though DBMS like MySQL provide a DATETIME field. What comes to my mind is:

Pro:

  • If importing data to a different server, timezone will be preserved, independent of MySQL server timezone

  • Enables using a different DBMS that might not provide DATETIME type

Con:

  • Timestamps are not human readable anymore in DB

  • Complex SQL date/time calculations are harder to use, since timestamps need to be converted first

Are there more?

7

In general I use the date format my DBMS provides. The argument that other DBMS might not provide the same type is of minor importance - at least in my case :)

I have never experience a change of DBMS wihtin a lifetime of a product.

8

Pro:

  • A unix timestamp field is 4 bytes, a date time field is 8 bytes

  • Selecting or sorting a unix timestamp field is (much) faster I guess

I see no benefit in using datetime all the time. In my opinion, everything that happens at runtime (eg a login, a created post) should be stored as timestamp. Everything that comes from a user (eg birthdate) should be stored as date/datetime.

9

Good points. Thanks. :)

10

I seriously doubt that selecting a few timestamps will be the bottleneck of your application :)

The only reason I saw people using timestamps because it’s easier to format dates in PHP (which could be done in SQL already)

11

What I ended up using was the MySQL datetime type. I set the server time to UTC, and when I store timestamps in the database I use the MySQL function UTC_TIMESTAMP(). This way when I change mysql servers in different timezones, no harm as done as the date is stored in UTC regardless. For date conversions, I use the DateTime class supplied with PHP. Seems to work pretty well

12

I wonder why you use UTC_TIMESTAMP() before saving. Doesn’t the DateTime class already take care of the conversion to UTC?

I also see potentially wrong offset handling if importing UTC datetime into another MySQL server with a different timezone. Maybe this matters only in rare cases but you should keep that in mind for 100% correct datetime information.

13

UTC_TIMESTAMP()doesn’t convert anything. It is similar to NOW() only the timezone it generates the date for is UTC instead of whatever timezone the server is configured to.

14

I see, thanks for clarification.

15

Just for save our time. I remember that some methods of DateTime class must run under PHP 5.3.x version. Many hosting servers have installed Zend Optimizer and PHP 5.2.15 is still running by default.