Comparing Dates in PHP and MySQL

Sometimes you need to know compare PHP dates. You need to know what is later or if both dates are the same.

PHP

time formats and functions

PHP knows these time / date formats:

• UNIX Timestamp: Integer - The number of seconds after 1970. Related functions are
  • int `mktime([ int \$hour = date("H") [, int \$minute = date("i") [, int \$second = date("s") [, int \$month = date("n") [, int \$day = date("j") [, int \$year = date("Y") [, int \$is_dst = -1 ]]]]]]])`
  • int `time()`
  • string `date( string \$format [, int $timestamp = time() ] )`
  • int `strtotime( string \$time [, int \$now ] )` I recommend using `YYYY-MM-DD HH:mm:ss` if possible.
• Associative Arrays. The array looks like this

  Array
  (
      [year] => 2006
      [month] => 12
      [day] => 12
      [hour] => 10
      [minute] => 0
      [second] => 0
      [fraction] => 0.5
      [warning_count] => 0
      [warnings] => Array()
      [error_count] => 0
      [errors] => Array()
      [is_localtime] =>
  )
  

  The related functions are:
  • array date_parse (string $date)
  • array getdate ([ int $timestamp = time() ] )
• DateTime Class: This class can do quite a lot. You should read the manual if you're interested in using it.

Comparisons

Comparing UNIX Timestamps is like comparing integers. No problem.

Comparing Arrays is more interesting. What do you think will the following script print?

<?php

$d1 = date_parse ("2011-05-11");
$d2 = date_parse ("2011-05-11 13:00:00");

print_r($d1);
print_r($d2);

if ($d1 < $d2) {
    echo '$d1 is less than $d2.';
} else if ($d1 == $d2) {
    echo '$d1 is equal to $d2.';
} else {
    echo '$d1 is greater than $d2.';
}

?>

It prints '$d1 is less than $d2.' as

date_parse ("2011-05-11");

is basically the same as

date_parse ("2011-05-11 00:00:00");

You can compare the Array to an integer, but I don't know what PHP does. It seems as if the array would always be considered as being greater. If you use the functions you'll be fine.

MySQL

time formats and functions

MySQL knows these date and time types and those functions. Here is a very short overview:

• DATETIME: 'YYYY-MM-DD HH:MM:SS' range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
• DATE: 'YYYY-MM-DD' range is from '1000-01-01' to '9999-12-31'
• TIMESTAMP: like DATETIME, but range is from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC

Those examples show more than a long explanation:

mysql> SELECT CURTIME();
        -> '23:50:26'
# Adding zero will NOT convert it to a UNIX timestamp:
mysql> SELECT CURTIME() + 0;
        -> 235026.000000 # An "integered" TIME

mysql> SELECT NOW( )
        -> '2011-10-04 18:33:45'
# Adding zero is a bad idea here, too:
mysql> SELECT NOW( ) +0
        -> 20111004190945.000000 # An "integered" DATETIME
# If you want a UNIX Timestamp, use this function
mysql> SELECT UNIX_TIMESTAMP();
        -> 1317746025
mysql> SELECT UNIX_TIMESTAMP('2011-10-04 18:33:45');
        -> 1317746025
# You can also convert it:
mysql> SELECT UNIX_TIMESTAMP(`my_datetime_row`) FROM `my_table`

Comparisons

You can compare two DATETIMEs like this:

SELECT `my_row` FROM `my_table` WHEN `datetime1` < `datetime2`

It's of course not problem if you compare two UNIX Timestamps which are stored as integers in the database:

SELECT `my_row` FROM `my_table` WHEN `int1` < `int2`

But what happens if you compare a DATETIME with a Timestamp (integer)?

SELECT `my_row` FROM `my_table` WHEN `datetime1` < UNIX_TIMESTAMP()

This is basically:

SELECT `my_row` FROM `my_table` WHEN `datetime1` < 1317750167

And it compares the "integered" DATETIME 20111004210710 for 2011-10-04 21:07:10 with 1317750167. This is obviously crap. Don't do it. Never. Instead you should convert your dates with UNIX_TIMESTAMP(your_datetime) or FROM_UNIXTIME(unix_timestamp).

Comparing MySQL types with PHP types

The simplest way to compare MySQL DATE formats with PHP types is using strtotime(...) or date(...) if needed. If you have a DATETIME and you want to know if it's in the past, you can use

if (strtotime($datetime) < time()) {
   echo '$datetime is in the past.';
}

From PHP to MySQL

If you have a date you got via an date input field and want to submit it to MySQL, just use this piece of code:

$mysqlFormat = date('Y-m-d H:i:s', strtotime($_POST['my_date']));