Are unix timestamps the best way to store timestamps?

However you choose to store a timestamp, it is important to avoid regional interpretation problems and time offset problems. A Unix timestamp is interpreted the same regardless of region, and is calculated from the same point in time regardless of time zone – these are good things.

Beware storing timestamps as ambiguous strings such as 01/02/2008, since that can be interpreted as January 02, 2008 or February 01, 2008, depending on locale.

When storing hours/minutes/seconds, it is important to know “which” hour/minute/second is being specified. You can do this by including timezone information (not needed for a Unix timestamp, since it is assumed to be UTC).

However, note that Unix timestamps cannot uniquely represent some instants in time: when there is a leap second in UTC, the Unix timestamp does not change, so both 23:59:60 UTC and 00:00:00 the next day have the same Unix representation. So if you really need one second or better resolution, consider another format.

If you prefer a more human readable format for storage than a Unix timestamp, consider ISO 8601.

One technique that helps keep things straight-forward is to store dates as UTC and only apply timezone or DST offsets when displaying a date to a user.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)