MySQL DATETIME format comparison - is strtotime needed?

Question

I've been doing something along the lines of..

$dt1 = '1000-01-01 00:00:00'; //really some val from db
$dt2 = '1000-01-01 00:00:10'; //another val maybe db maybe formatted

if(strtotime($dt1) > strtotime($dt2){
//do something
}

Is the strtotime needed? can i do a more direct comparison on the datetime formatted strings?

i.e.

if($dt1 > $dt2){
//do something
}

Will that always work?

Answer 1

If you have dates stored in string, using YYYY-MM-DD HH:MM:SS format, you can use the alphabetical order : it'll be the same as dates order.

Which means the comparisons on strings will have the same result as comparisons on dates.

I would add that there would be an advantage in not using UNIX timestamps : those are limited to a range that goes from 1970 to 2038, as they are stored on 32 bits integers that represent a number of seconds since 1970-01-01 (depending on your system, the range might be wider -- but not unlimited)

Answer 2

I usually use strtotime() to be safe in comparing dates as a timestamp value.

You can check by writing both IF statements and printing 'statement 1 true' in one and 'statement 2 true' in the other. Then try changing the values of dt1 and dt2 and see how that plays out.

Answer 3

Yes, the lexicographical order of strings in the format yyyy-mm-dd hh:ii:ss works as intended. You could even sort dates like that.

$dts = array(
  '1000-01-01 00:00:00',
  '2010-03-16 21:22:19',
  '1000-01-01 00:00:10',
  '1976-03-27 05:55:00', 
  '1976-03-27 05:54:00',
  '1968-08-21 12:00:00',
  '2001-01-01 00:00:01'
);

sort($dts);
foreach($dts as $dt) {
  echo $dt, "\n";
}

prints

1000-01-01 00:00:00
1000-01-01 00:00:10
1968-08-21 12:00:00
1976-03-27 05:54:00
1976-03-27 05:55:00
2001-01-01 00:00:01
2010-03-16 21:22:19

But keep in mind that you will get no feedback for strings that are not in the right format. So, if it could be that there are malformed strings you'd better check that, too. If that is not a concern, string1>string2 is ok.

edit: You could even let MySQL do the work. If this is better/equal/worse to doing it in php depends on what you're actually trying to achieve. E.g.

$pdo = new PDO("mysql:host=localhost;dbname=test", 'localonly', 'localonly'); 
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// setting up a sample table with some values
$pdo->exec('CREATE TEMPORARY TABLE foo (id int auto_increment, d datetime NOT NULL, primary key(id))');
$pdo->exec("INSERT INTO foo (d) VALUES ('1000-01-01 00:00:00'),('2010-03-16 21:22:19'),('1000-01-01 00:00:10'),('1976-03-27 05:55:00')");


$query = "
  SELECT
    d,
    (d>'1910-03-17 12:00:00') as flag
  FROM
    foo
";

foreach ( $pdo->query($query) as $row ) {
  echo $row['flag'] ? '+ ':'- ', $row['d'], "\n";
}

prints

- 1000-01-01 00:00:00
+ 2010-03-16 21:22:19
- 1000-01-01 00:00:10
+ 1976-03-27 05:55:00