How to prevent PHP to convert a DateTime object with 0000-00-00 value to -0001-11-30

Question

Currently i'm working with Doctrine 2 and MySQL. I encounter some problems when I work with datetime fields in mysql respectively DateTime in PHP. When in my database the date value is "0000-00-00", in PHP this value is converted to -0001-11-30. I'm note pleased about that, so I need to make a check for "0000-00-00" value of date. Does anybody have some idea on this to help? Thanks.

N.B. I'm thinking if it is right to make checking on "-0001-11-30" instead of "0000-00-00".

Answer 1

If the date is not set use NULL to indicate that state. That solves your problem and makes database schema much more clear and verbose.

Answer 2

An alternative is to set the NO_ZERO_DATE SQL mode in your MySQL server. You can set it for your current session as explained at http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

Answer 3

I use this function throughout my code. Pass it a date-like string or a DateTime(Immutable) object; it will spit out a PHP DateTime or DateTimeImmutable object, or false if input is a "0000-00-00"-like string. With the second parameter it can also force the result to be immutable or not:

function ensureDateTime ( $input, $immutable = NULL ) {
    if ( ! $input instanceof \DateTimeInterface ) {
        if ( in_array( $input, ['0000-00-00', '0000-00-00 00:00:00'], true ) ) {
            $input = false;
        } elseif ( $immutable ) {
            $input = new \DateTimeImmutable( $input );
        } else {
            $input = new \DateTime( $input );
        }
    } elseif ( true === $immutable && $input instanceof \DateTime ) {
        $input = new \DateTimeImmutable( $input->format(TIMESTAMPFORMAT), $input->getTimezone() );
    } elseif ( false === $immutable && $input instanceof \DateTimeImmutable ) {
        $input = new \DateTime( $input->format(TIMESTAMPFORMAT), $input->getTimezone() );
    }
    return $input;
}

Basically a "I'm not sure what I started with, but I know what I want", function.

(Note: A bit of PHP 7 syntax here, but easily adapted to PHP 5)

Answer 4

If you can't handle this on your database, you can use this snippet to avoid this behavior anda validate the date

$date = new DateTime('0000-00-00 00:00');
if (DateTime::createFromFormat('Y-m-d', $date->format('Y-m-d'))) {
    // Valid
} else {
    // Not valid
}