php/mysql: strToTime error manipulating dates

Question

I have some code for rendering dates that was working great for a couple of years and is now broken. I don't know if it has something to do with my host changing version of PHP or somehow an error crept in.

Basically, dates such as 11/30/15 are now getting rendered as 11/30/-1

Here is an example of what is going on:

$olddate = $row['date'];//in database this looks like:0000-00-00 00:00:00
$newdate = nicedate($olddate);
echo "starting date time: ".$olddate;//displays as 'starting date time: 0000-00-00 00:00:00'
echo "after transforming it with nice date: ".$newdate; //displays as 'after transforming it with nice date: 11/30/-1'

I also tried just running strtotime and get this:

echo "after transforming it with strtotime: ".nicedate($row['starttime']);//-62169966000

    // gets nice data
    function nicedate($datetime) {
    $niceDate = strtotime($datetime);
    $niceDate = date("m/d/y",$niceDate);
    return $niceDate;
    }

echo out the $olddate to see if it is printed right. If it does than it has something to do with the version you are running. Also recommend using mysqli or PDO if you are not already doing. — Puya Sarmidani, Nov 20 '15 at 13:09

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

As you can see here https://3v4l.org/8gqCK it really depends on which PHP version you are using.

PHP will render the string 0000-00-00 00:00:00 to -0001-11-30 00:00:00.000000

This is an example output from DateTime:

object(DateTime)#1 (3) {
  ["date"]=>
  string(27) "-0001-11-30 00:00:00.000000"
  ["timezone_type"]=>
  int(3)
  ["timezone"]=>
  string(13) "Europe/Berlin"
}

The OUTPUT also depends on the time zone:

object(DateTime)#1 (3) {
  ["date"]=>
  string(27) "-0001-11-29 23:06:32.000000"
  ["timezone_type"]=>
  int(3)
  ["timezone"]=>
  string(3) "UTC"
}

This would output:

...with nice date: 11/29/-1

And here strtotime():

int(-62169987208)

So you see the date function were changed on different version, what you can do now ist just to check if the date is 0000-00-00 00:00:00:

function nicedate ( $datetime ) {
    if( $datetime == "0000-00-00 00:00:00" ) return "Some value or false?";
    ...
    ...
}

You also have a look at the MySQL mode: NO_ZERO_IN_DATE

Updated approach due to the comments below:

function nicedate( $datetime ) {
    if( !validateDate( $datetime ) ) return "something";
    $dateTimeObject = DateTime::createFromFormat('Y-m-d H:i:s', $datetime );
    return $dateTimeObject->format( "m/d/y" );
}

function validateDate($date) {
    $d = DateTime::createFromFormat('Y-m-d H:i:s', $date);
    return $d && $d->format('Y-m-d H:i:s') == $date;
}

^{function was copied from this answer or php.net}

This is because 0000-00-00.... is not a valid date. What happens is simple, from year, month, day,... is alway one (1) substracted from the last.

0000 -1 = -1// year
00 - 1 = 11// month, because 12 is the highest value that datetime recognizes as month
00 - 1 = 30// day, because 31 is the highest value that datetime recognizes as day

For hours, minutes, seconds,.. it's ok, because zero is a valid value for time.

That's why it is rendered like this: -0001-11-30 00:00:00.000000

edited May 23 '17 at 11:50

Community

1
1

answered Nov 20 '15 at 13:34

swidmann

2,787
1
18
32

Can you recommend way to bullet proof the code to PHP versions so it always returns a normal date? Not clear if the only time there is an issue is if the date time is 000-00 ... and you just need to check for that as in your function above or if this error can crop up in other cases. I would like to put something in a function – Nov 20 '15 at 15:48
Bulletproof is nothing ;), but you can try the function in my updated answer – swidmann Nov 20 '15 at 15:59
This produced same result with year as -1. I think you have diagnosed problem. Question is how to make year display properly. Month and day are okay In other words, if you test for 00s, what how could I get it to display correctly with what must be PHP 5.3 or so. – Nov 20 '15 at 16:55
I think I can't follow you: maybe you can show me here https://3v4l.org/EJvfQ what problem you have and with which datetime string – swidmann Nov 20 '15 at 18:27
How would you manipulate 0000-00-00 00:00:00 so that it shows up correctly as today's date i.e. 11/30/15, NOT 11/30/-1 – Nov 20 '15 at 18:30
you can put this: `if( $datetime == "0000-00-00 00:00:00" ) {$datetime = "2015-11-30 00:00:00";}` in the function nicedate() on top of the first return, but think about it: `0000-00-00 00:00:00` **is NOT a valid date* so you should handle this and not manipulate this date – swidmann Nov 20 '15 at 19:19
I hear you. There are a lot these dates in the dbase as it seems to have been a default base in mysql if not null – Nov 22 '15 at 01:30

php/mysql: strToTime error manipulating dates

1 Answers1