I’ve been looking at the MySQL date/time fields and also the option of just storing a UNIX timestamp. What I’ve always done, however, is store as date and time as a 14 digit int (YYYMMDDHHMMSS). It’s easy to order by and is human readable.
From what I can tell the advantage of using a UNIX timestamp is that it’s primed for use with PHP’s date function and MySQL has the function to query them—but really formatting YYYMMDDHHMMSS for whatever use you want is pretty easy. Am I missing something other than the int is smaller?
I can see the advantage of using MySQL’s datetime if you’re going to be doing more complex date-based queries and need to make use of some of MySQL’s date functions. But, aren’t they stored as strings and therefore slower than ints?
I’m not sure whether to carry on as I am or start to use either of the above. I quite like the UNIX timestamp idea as the raw date being readable in the database is not really an argument to keep it that way. And is it safe to say by 2038 UNIX timestamp will still be working?
There’s a good Stack Overflow discussion on datetime vs timestamp fields. One compelling argument for using a UNIX timestamp is that it represents an absolute moment in time, whereas a datetime is relative to the timezone the user is in.
No they are not stored as strings, they are stored as binary numbers and since they use the full range of values they are smaller numbers than the ones you are using where you would not have 20139597999999. They are only converted to strings when you extract them from the database and after any date functions have been applied.
Also your way does not provide an easy way to change the timezone so that all users can see the time relative to where they are.
You are effectively using larger numbers and offloading a lot of the work that SQL can do more efficiently when you use an int instead of a datetime or timestamp for the field type.
Thanks to you both. felgall, does using a slightly larger int have that much of an impact on performance?
I’m confused as to how this relative timezone thing works. Surely, if it’s only the one server access it, it will always use local time zone, no?
So basically, would you always use MySQL datetime for dates and times and SELECT with UNIX_TIMESTAMP—even if you don’t need most of MySQL’s date functions?
I was thinking of scenarios where you get dates as input from users in different timezones, but thinking about it, you could could convert all input to a specific timezone (UTC perhaps) and then store it in a DATETIME field, so I don’t suppose it makes that much difference. I guess it comes down to preference at the end of the day. I tend to use DATETIME for the simple reason that it’s human readable.
It does as soon as you need to perform any calculations at all that involve the date - not because of the size of the number though but simply because you can’t simply add or subtract a number to get another date and time a fixed distance from the first one.
Only if you are simply treating the date as a string of numbers that never needs to be changed should you not start by converting it to a date format when you first validate it and only convert back when you need to display it.
