Menu
I always use unix timestamps for everything, but am wondering if there is a better way.
What do you use to store timestamps and why?
712
As of 2019, the extra 27 leap seconds are missing. And so our falsehoods go as follows: Unix time is the number of seconds since 1 January 1970 00:00:00 UTC, minus leap seconds. If I wait exactly one second, Unix time advances by exactly one second, unless a leap second has been removed.
When should I use a timestamp. A Unix timestamp is interpreted the same regardless of region and is calculated from the same point in time regardless of the time zone. If you have a web application that is used over multiple timezones and you need date/time to reflect individual users' settings, use a timestamp.
Unix time is a way of representing a timestamp by representing the time as the number of seconds since January 1st, 1970 at 00:00:00 UTC. One of the primary benefits of using Unix time is that it can be represented as an integer making it easier to parse and use across different systems.
What is the difference between UNIX TIMESTAMPS and MySQL TIMESTAMPS? In MySQL, UNIX TIMESTAMPS are stored as 32-bit integers. On the other hand MySQL TIMESTAMPS are also stored in similar manner but represented in readable YYYY-MM-DD HH:MM:SS format.
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.
105
If you are storing a log file, please for the love of pete make it something human readable and lexically-sortable.
2008-10-07 09:47:02 for example.
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
