Bug #49185 TO_SECONDS() value for year 0 is off by 1 day
Submitted: 29 Nov 2009 12:58 Modified: 20 Dec 2009 14:31
Reporter: Jon Stephens Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:ALL OS:Any (openSUSE11.1/64bit, Mac OS X)
Assigned to: Jon Stephens CPU Architecture:Any
Tags: to_days(), to_seconds(), zero date

[29 Nov 2009 12:58] Jon Stephens
TO_SECONDS() is supposed to return number of seconds since year 0, but supplying year 0 datetime or datetime yields number of seconds in 1 day.

How to repeat:
mysql> select to_seconds('0000-01-01');
| to_seconds('0000-01-01') |
|                    86400 |
1 row in set (0.00 sec)

mysql> select to_seconds('0000-01-01 00:00:00');
| to_seconds('0000-01-01 00:00:00') |
|                             86400 |
1 row in set (0.00 sec)

Suggested fix:
TO_SECONDS('0000-01-01') and TO_SECONDS('0000-01-01 00:00:00') should return 0, not 86400.
[29 Nov 2009 13:13] Jon Stephens
Note: My server timezone is UTC+1, so I suppose that to_seconds('0000-01-01 00:00:00') should actually return 3600, not 0.

Note that select to_seconds('0000-01-00 00:00:00') returns NULL.
[29 Nov 2009 13:15] Jon Stephens
But then it might also be argued that select to_seconds('0000-01-01 00:00:00') should return -3600 for me... :)
[29 Nov 2009 14:38] Valeriy Kravchuk
Verified just as described with recent mysql-trunk from bzr:

77-52-12-228:trunk openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.0-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  select to_seconds('0000-01-01');
| to_seconds('0000-01-01') |
|                    86400 |
1 row in set (0.00 sec)

mysql>  select to_seconds('0000-01-01 00:00:00');
| to_seconds('0000-01-01 00:00:00') |
|                             86400 |
1 row in set (0.00 sec)
[3 Dec 2009 7:22] Mikael Ronström
Not a bug.
The definition in MySQL of to_days and to_seconds is number
of seconds/days since year 0. However the 1 january year is
actually reported as to_days('0000-01-01'), so the start of
time is actually 0000-00-00, while it can easily be argued
this is a non-existent date, this is the date choosen as the
start of MySQL time.

This is the comment in the code:
  Calculate nr of day since year 0 in new date-system (from 1615)

    year                 Year (exact 4 digit year, no year conversions)
    month                Month
    day                  Day

  NOTES: 0000-00-00 is a valid date, and will return 0

    Days since 0000-00-00

Actuallt the comment about 0000-00-00 being a valid date has changed,
so it is reported back as a warning and the result of to_days/to_seconds
is NULL, but the start of time hasn't changed.
[3 Dec 2009 15:26] Peter Gulutzan
By analogy with the fact that TO_DAYS('0000-01-01') = 1
(strange but true), we have to accept this is not a bug.

Does that mean "the start of time is actually 0000-00-00"?
No, we can't count impossible dates. A better guess would
be that 0000 = 1 BC since it's the year before 1 AD,
therefore the day before 0000-01-01 is December 31, 2 BC.
[3 Dec 2009 16:21] Peter Gulutzan
Jon's other comments trouble me.

Is the zero second a UTC time, in which
case TO_SECONDS should vary if it's given
an argument in a non-UTC time?

Or is the zero second in the same time
zone as the argument?

WL#3352 didn't specify. I'm only asking
Mikael to make this clear, please, before
closing the bug again.
[3 Dec 2009 23:07] Mattias Jonsson
TO_SECONDS() should not care about timezone (and it should only be possible to use with DATE and DATETIME data types, _not_ TIMESTAMP when used as a partitioning function).

TO_SECONDS() is implemented the same way as TO_DAYS() with the addition that the days are multiplied with 86400 and the remaining seconds are added.

[18 Dec 2009 15:38] Jon Stephens
This is expected behaviour and should be noted in the documentation; thus, it's actually a Docs bug.

Since I'm the one who brought this up in the first place, it's only right that I should take care of it.

Changed category/status/lead to Docs/Verified/Stefan, and reassigned to myself.
[18 Dec 2009 15:39] Jon Stephens
Also updated version since TO_DAYS() appears prior to 5.5.
[20 Dec 2009 14:31] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.