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']));