MySQL Bugs: #49185: TO_SECONDS() value for year 0 is off by 1 day

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:	None
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

Description:
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.

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.

But then it might also be argued that select to_seconds('0000-01-01 00:00:00') should return -3600 for me... :)

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)

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)

  SYNOPSIS
    calc_daynr()
    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

  RETURN
    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.

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.

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.

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.

i.e. TO_SECONDS(DATETIME_VAL) = TO_DAYS(DATETIME_val) + HOUR(DATETIME_val)*60*60 + MINUTE(DATETIME_VAL)*60 + SECOND(DATETIME_val)

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.

Also updated version since TO_DAYS() appears prior to 5.5.

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.