Working with Dates and Time using PHP and MySQL

Over the past several years, I'm constantly amazed by the amount of questions there are on how to format a date, format time, and add or remove time from a given date. There are many other specifics too numerous to mention, but I think you get the general idea.

I've also seen a wealth of code written by some very clever people who've given the problem a lot of thought. I'm sure they've also spent ages researching, testing and rewriting what they've done. Others have rewritten that code to achieve more specific control, but all have the same final result: take a date, run it through a ton of converters and calculators, converting strings to unix time or micro time then re-converting the values back to spew out a piece of information that looks right.

Did you know that MySQL has all the functions you need, ready to use at the click of a button? For something like a PHP script to format a date, some of the examples I've seen out there consist of 10 lines, sometimes a whole lot more. Just to convert a date from one value to another! That doesn't even take into consideration a date calculation such as how many days there are from a specific date, or time, etc.

MySQL can do this and it can usually do it in 1 line of code.  Look at the following examples for an idea of how powerful your database truly is:

Convert the current date and time to yyyy-mm-dd:

SELECT DATE_FORMAT( NOW() , '%Y-%c-%d') AS mydate_fmt;
                      

Select all Open in new window

If you want to run the above formatting against a timestamp field that is stored in your table:

select date_format( `myfield` , '%Y-%c-%d') as myfield_fmt from mytable;
                      

Select all Open in new window

There are more date formatter parameters that you can use and here's the current list as per the MySQL documentation:

%a      Abbreviated weekday name (Sun..Sat)
%b      Abbreviated month name (Jan..Dec)
%c      Month, numeric (0..12)
%D      Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d      Day of the month, numeric (00..31)
%e      Day of the month, numeric (0..31)
%f      Microseconds (000000..999999)
%H      Hour (00..23)
%h      Hour (01..12)
%I      Hour (01..12)
%i      Minutes, numeric (00..59)
%j      Day of year (001..366)
%k      Hour (0..23)
%l      Hour (1..12)
%M      Month name (January..December)
%m      Month, numeric (00..12)
%p      AM or PM
%r      Time, 12-hour (hh:mm:ss followed by AM or PM)
%S      Seconds (00..59)
%s      Seconds (00..59)
%T      Time, 24-hour (hh:mm:ss)
%U      Week (00..53), where Sunday is the first day of the week
%u      Week (00..53), where Monday is the first day of the week
%V      Week (01..53), where Sunday is the first day of the week; used with %X
%v      Week (01..53), where Monday is the first day of the week; used with %x
%W      Weekday name (Sunday..Saturday)
%w      Day of the week (0=Sunday..6=Saturday)
%X      Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x      Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y      Year, numeric, four digits
%y      Year, numeric (two digits)
%%      A literal “%” character
%x      x, for any “x” not listed above

Source: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

A lot of people like to use the unix timestamp.

Get the current date and time in unix timestamp:

SELECT UNIX_TIMESTAMP();
                      

Select all Open in new window

This leads me to another point I would like to mention before more code. How to store a date in the database. I highly recommend using the timestamp, date or datetime field types. The reason for this is that although your database may need a specific date format to understand what it's being fed, it can't calculate against those values without having to use a conversion back into a workable data type - which usually involves lines of PHP code, then feed it back to be calculated, and then it needs to be converted AGAIN to output a nicely formatted date. This also facilitates date formatting such as the example I provided above against a field. Speaking from years of experience, I would NEVER use a unix timestamp. Sure, it's small in comparison to a datetime, or timestamp field, but did you know that a unix timestamp will only go back to January 1, 1970 (otherwise known as the Unix Epoch)? So you're out of luck when trying to calculate an age if your application demographics include people older than early 40. That's not good.

My personal favorite field type is DATETIME as it's specific, however for automated dates when a record gets created I love to use timestamp with an auto population on insertion. MySQL is ready to make this happen for you, so you don't need to feed it a NOW() value for the field and all you have to do is populate the other fields. That's not to say you can't use a value to populate it, but that's another step for PHP to include in it's database query. When you eventually deal with millions of records being created, this could cause quite a bit of extra overhead that you really could do without.

Now for some fun part. If you want to add 5 days onto today's date and get the result what would you do?

Add x days to NOW()

SELECT DATE_ADD( NOW( ) , INTERVAL 5 DAY ) as mydate_fmt;
                      

Select all Open in new window

Subtract 5 days, is as simple as:

SELECT DATE_SUB( NOW( ) , INTERVAL 5 DAY ) as mydate_fmt;
                      

Select all Open in new window

To add hours or minutes:

SELECT DATE_ADD( NOW( ) , INTERVAL 10 MINUTE ) as mydate_fmt;
                      SELECT DATE_ADD( NOW( ) , INTERVAL 28 HOUR ) as mydate_fmt;
                      

Select all Open in new window

And the really cool thing when doing date calculations is that you can wrap those functions in formatting functions too, or you can do those calculations against fields already in your tables:

SELECT DATE_FORMAT( date_add(now(), interval 10 day), '%Y-%c-%d') as mydate_fmt;
                      
                      SELECT DATE_FORMAT( date_add( `myfield`, interval 10 day), '%Y-%c-%d') as myfield_fmt from mytable;
                      

Select all Open in new window

As you can see, with a single line of simple MySQL query, I'm achieving what would take 10 lines of PHP, (or more) can do. Plus I have so much flexibility.

When feeding back to PHP, just ensure you are arranging your fields right, especially when you have dropdowns for years, months and dates. It doesn't matter whether you're using an American date MMDDYYYY when your database datetime field looks like the European date format of YYYY-MM-DD HH:MM:SS, it's OK. Just feed it what it knows and it will produce flawless results for you every time. What's more, those results will come back to you in a flash.

You will also notice that I didn't select from a table when executing most of the above queries. MySQL is really flexible at allowing us to run these commands which it will then use it's powerful features to perform, and it really does perform them well. With the above code examples, you can save not only lines and lines of PHP code, but your sanity, your hair, and your rest. Please people, as much as you are tempted to write the next novel length date formatting, calculating and rearranging PHP script or class, please don't. Using what I have above works the best hands down every time. Dates SHOULD be a fun thing, and not a complicated math and calculus (you see, I even hate math) nightmare that causes you endless grief. Break free from "the norm" and try the above out, I am certain you won't be disappointed.

Hope this helps.