Dating in the modern world
When it comes to dating, there is only one guide you need to read: ISO 8601. The ISO 8601 date/time format looks like this: 2009-07-25T08:29-9. The date portion sorts in a list in date order, and it reads from left to right in order of significance from greatest to least. The time is in 24 hour, and so also sorts correctly, and it’s finally followed by a timezone designator represented as an offset from UTC, or simply Z meaning zero offset, if specifying UTC. If no timezone is given, localtime is assumed. Also, all numbers are zero-padded, again, making sure the whole thing sorts.
It should be said that ISO 8601 date will not sort dates of differing timezones correctly within a list, but there is also still no cure for cancer, so I’m willing to forgive it, for now.
How the American standard date format of mm/dd/yyyy, sometimes even mm/dd/yy, has managed to claim dominance for so long into the modern computerized world has always perplexed me. It’s confusing to read for the 1st 12 days of the month, it doesn’t sort in lists, and it emphasizes the month over all & the year last. Can somebody explain where this format came from?
Following, I’ll explain how to get your stuff to work in the correct date format, so you’ll never have to worry about it again.
MySQL
Thankfully, MySQL already defaults to an ISO 8861 date format. If for some reason you find yourself in an environment where someone has perverted it, you can set it back to normal by adjusting the datetime_format system variable.
The best thing to do is set the global server date format by editing the my.cnf file usually located in /etc/my.cnf or /etc/mysql/my.cnf. Add the datetime_format variable to the mysqld section as shown below.
[mysqld]
datetime_format=%Y%m%d%H%i%s
If you don’t have root, you can still set the date format within your mysql session using the set command. Unfortunately, you would have to do this for every session.
set datetime_format='%Y%m%d%H%i%s';
If you cannot even set the date_format variable, you’ll just have to reformat the date within your own queries. To do this, use the DATE_FORMAT() function.
SELECT DATE_FORMAT( NOW(), '%Y-%m-%d %H:%i:%s' );
PHP
Most programming languages represent basic time as a value of seconds past an epoch. This is great for handling in code, but when you want to display the date, a conversion function is necessary. In PHP that function is the date() function. It behaves in much the same way the MySQL DATE_FORMAT() function does, just with its parameters in the other order. Use it as follows.
<?php
echo date( '%Y-%m-%d %H:%i:%s', time() );
?>
I have included the second parameter time() just to show you where to put the date value you want to display. time() simply returns the value, in seconds past the UNIX epoch, of the current time. This is the default behaviour of date() if you leave out the second parameter.
PHP also comes complete with a DateTime class containing a great set of date format constants for your conversion convenience. To get a fully formatted ISO 8601 date using this class, use the ISO 8601 contant.
<?php
echo date( DateTime::ISO8601 );
?>
Also… please use the whole <?php tag… pretty please?