How to Explain the Result for a New \Datetime('0000-00-00 00:00:00')

How do you explain the result for a new \DateTime('0000-00-00 00:00:00')?

You are seeing two effects here. The first one is that you use a way of writing for a date that can be written in multiple forms:

0000-01-01 same as  0000-01-01
0000-01-00 same as -0001-12-31
0000-00-01 same as -0001-12-01
0000-00-00 same as -0001-11-30

So by the date itself, you already specify the 30th November -1.

Now there's the time offset left that differs about the 9 minutes and 21 seconds. That is because of a change in the clock compared to UTC in Paris/France that happened 10 Mar 1911 23:51:38/39 local time.

---

I modified your code example a bit and introduced the Europe/Paris setting as you have it, which is playing a role. This code is telling as well the offset in seconds from UTC (Z) which is what you're looking for:

$dt = new DateTime('0000-00-00 00:00:00', new DateTimeZone('Europe/Paris'));
printf("%s secs offset from UTC\n", $dt->format('r T (e) Z'));

I changed the dates a bit

Fri, 10 Mar 1911 23:51:38 +0009 PMT (Europe/Paris) 561 secs offset from UTC
                                                   ^^^

One second later:

Fri, 10 Mar 1911 23:51:39 +0000 WET (Europe/Paris) 0 secs offset from UTC

When local standard time was about to reach
Saturday, 11 March 1911, 00:01:00 clocks were turned backward 0:09:21 hours to
Friday, 10 March 1911, 23:51:39 local standard time instead.

That are 561 Secs. Reference: Clock changes in Paris - Time change dates in 1911 and Time zone changes and daylight saving time start/end dates between year 1900 and 1924.

How to properly set 0000-00-00 00:00:00 as a DateTime in PHP

Your architecture is wrong to begin with. The problem is not setting the date itself, which is so obviously invalid that both MySQL and PHP are right to reject it, as there is no year 0 and no day 0 of a month 0, and the output you see is just the correction to a sort-of-valid date (it's 1 year, 1 month and 1 day before 01/01/01). But you're also just missing the point that Doctrine abstracts this away if you just do it right:

$entEmail = new Email();
$entEmail->setViewedAt(null);

Doctrine will now happily put NULL in the database column as it should be.

datePosted always '0000-00-00 00:00:00'

You should convert it to mysql datetime as mentioned in the comment section. Or, since you use current date, just use "Now()" (without quotes) as value in the sql statement itself.

How to initialize datetime 0000-00-00 00:00:00 in Python?

There is no year 0, because people couldn't figure out how to count properly back then.

The closest you can get:

>>> from datetime import datetime
>>> datetime.min
datetime.datetime(1, 1, 1, 0, 0)

Symfony Twig (3) renders wrong datetime when datetime in database is 0000-00-00 00:00:00

Dates and times stated on our calendar get messy before the invention of the Gregorian calendar and clocks/timezones were accurately synced. The year 1 never existed at the time, and was invented well after the fact. Also note that the timezone offset is +0:53 minutes.

My guess is that this has something to do with 0000-00-00 00:00:00 being converted to a unix timestamp, and then re-formatted as a date-time again. Given that 0000-00-00 00:00:00 never existed, the algorithms to do these conversions might not be entirely the same or reversible MySQL and PHP.

My suggestion would be to not do this. If you need a value to indicate 'no time', use NULL.

Check if $date is equal to 0000-00-00 00:00:00

If you are storing strtotime() in $due, then you need to compare it like that, too

if($due == strtotime('0000-00-00 00:00:00')){
    $due =  strtotime('2000-00-00 00:00:00');
}

or, more simply

if($due == 0){
    $due =  strtotime('2000-00-00 00:00:00');
}

but if $due is coming from your db as a string, you would want to strtotime() it:

$due = strtotime($due);
if($due == 0){
    $due =  strtotime('2000-00-00 00:00:00');
}

Be aware of timezones, though. That is, strtotime('0000-00-00 00:00:00 UTC') != strtotime('0000-00-00 00:00:00 EST'). This could also break your compare.

---

Related Topics