TIMESTAMP columns allow values up to 61 for seconds

[Patrick Crews]

TIMESTAMP columns can take values with seconds up to 61 (will fail if > 61).
The value is converted:
INSERT INTO t1 (b) VALUES('2000-01-01 12:00:61'); -> insert_value: "2000-01-01 12:01:01"

Test case:
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b TIMESTAMP);
# This INSERT should fail with ERROR 1685 (bad UNIX TIMESTAMP)
INSERT INTO t1 (b) VALUES('2000-01-01 12:00:61');
SELECT * FROM t1;

DROP TABLE t1;

[ziminq (ziminq)]

In drizzled/temporal.cc:1357

1357 bool DateTime::is_valid() const
1358 {
1359 return (_years >= DRIZZLE_MIN_YEARS_SQL && _years <= DRIZZLE_MAX_YEARS_SQL)
1360 && (_months >= 1 && _months <= 12)
1361 && (_days >= 1 && _days <= days_in_gregorian_year_month(_years, _months))
1362 && (_hours <= 23)
1363 && (_minutes <= 59)
1364 && (_seconds <= 61); /* Leap second... */
1365 }

it seems it's legal to be 61 seconds in case there is a leap second. any background on this?

backtrace:

(gdb) bt
#0 drizzled::DateTime::is_valid (this=0x6400000000) at drizzled/temporal.cc:1357
#1 0x0000000000840d5b in drizzled::Timestamp::is_valid (this=0x7f15d42579b0)
    at drizzled/temporal.cc:1370
#2 0x0000000000843186 in drizzled::DateTime::from_string (this=0x7f15d42579b0,
    from=0x3ac47c0 "2000-01-01 12:00:61", from_len=19) at drizzled/temporal.cc:161
#3 0x00000000005dc964 in drizzled::Field_timestamp::store (this=0x3acd558,
    from=0x3ac47c0 "2000-01-01 12:00:61", len=19) at drizzled/field/timestamp.cc:167
#4 0x0000000000628bd2 in drizzled::Item::save_str_value_in_field (this=0x3ac47d8,
    field=0x3acd558, result=0x3ac47e8) at drizzled/item.cc:261
#5 0x000000000066296a in drizzled::Item_string::save_in_field (this=0x3ac47d8,
    field=0x3acd558) at drizzled/item/string.cc:174
#6 0x000000000071b0ba in drizzled::fill_record (session=0x3abf1e0, fields=@0x3ac01a8,
    values=@0x3ac4710, ignore_errors=false) at drizzled/sql_base.cc:4378
#7 0x000000000072c0db in drizzled::mysql_insert (session=0x3abf1e0,
    table_list=0x3ac43e8, fields=@0x3ac01a8, values_list=@0x3ac01f0,
    update_fields=@0x3ac01d8, update_values=@0x3ac01c0, duplic=drizzled::DUP_ERROR,
    ignore=false) at drizzled/sql_insert.cc:358
#8 0x0000000000789c3c in drizzled::statement::Insert::execute (this=0x3ac7b00)
    at drizzled/statement/insert.cc:57
#9 0x000000000073ad69 in mysql_execute_command (session=0x3abf1e0)
    at drizzled/sql_parse.cc:478
#10 0x000000000073ae7d in drizzled::mysql_parse (session=0x3abf1e0,
    inBuf=0x3ac9a38 "insert into t1 (a, b) values (4, '2000-01-01 12:00:61')", length=55)
    at drizzled/sql_parse.cc:736
#11 0x000000000073b346 in drizzled::dispatch_command (command=drizzled::COM_QUERY,
    session=0x3abf1e0,
    packet=0x3abb1d1 "insert into t1 (a, b) values (4, '2000-01-01 12:00:61')",
---Type <return> to continue, or q <return> to quit---
    packet_length=55) at drizzled/sql_parse.cc:223
#12 0x0000000000705d38 in drizzled::Session::executeStatement (this=0x3abf1e0)
    at drizzled/session.cc:646
#13 0x0000000000706484 in drizzled::Session::run (this=0x3abf1e0)
    at drizzled/session.cc:505
#14 0x00007f15e7b97b50 in MultiThreadScheduler::runSession (this=0x32a0960,
    session=0x3abf1e0) at ./plugin/multi_thread/multi_thread.h:67
#15 0x00007f15e7b95fa2 in session_thread (arg=0x3abf1e0)
    at plugin/multi_thread/multi_thread.cc:43
#16 0x00000033d84073da in start_thread () from /lib64/libpthread.so.0
#17 0x00000033d78e62bd in clone () from /lib64/libc.so.6

[Hartmut Holzgraefe (hartmut-php)]

The value should be 60, not 61? As far as i understand there can be multiple leap seconds per year (as we had 1972), but not on the same day ...

Extra checks that *could* be added if _seconds==60:

* leap seconds can only happen in the last minute of an UTC day
* leap seconds usually happen on Jun 30th or Dec 31st (and so far exclusively have)
** English Wikipedia says theoretically they could be inserted at the end of any month
** German Wikipedia says prefered dates are in June or December, alternative dates are end of March or September, so impiles they could happen at the end of a quarter only

[Patrick Crews (patrick-crews)]

I'm thinking the value should be 60 as well (not 61):
Modern Unix time is based strictly on
UTC<http://en.wikipedia.org/wiki/Coordinated_Universal_Time>.
UTC counts time using SI <http://en.wikipedia.org/wiki/SI> seconds, and
breaks up the span of time into days. UTC days are almost always 86 400 s
long, but due to "leap seconds <http://en.wikipedia.org/wiki/Leap_seconds>"
are occasionally 86 401 s and could be 86 399 s long (though the latter
option has never been used as of January 2010)

On Sun, Jul 4, 2010 at 3:59 AM, Hartmut Holzgraefe <email address hidden> wrote:

> The value should be 60, not 61? As far as i understand there can be
> multiple leap seconds per year (as we had 1972), but not on the same day
> ...
>
> Extra checks that *could* be added if _seconds==60:
>
> * leap seconds can only happen in the last minute of an UTC day
> * leap seconds usually happen on Jun 30th or Dec 31st (and so far
> exclusively have)
> ** English Wikipedia says theoretically they could be inserted at the end
> of any month
> ** German Wikipedia says prefered dates are in June or December,
> alternative dates are end of March or September, so impiles they could
> happen at the end of a quarter only
>
> --
> TIMESTAMP columns allow values up to 61 for seconds
> https://bugs.launchpad.net/bugs/597778
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in A Lightweight SQL Database for Cloud and Web: Confirmed
>
> Bug description:
> TIMESTAMP columns can take values with seconds up to 61 (will fail if >
> 61).
> The value is converted:
> INSERT INTO t1 (b) VALUES('2000-01-01 12:00:61'); -> insert_value:
> "2000-01-01 12:01:01"
>
> Test case:
> --disable_warnings
> DROP TABLE IF EXISTS t1;
> --enable_warnings
>
> CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b TIMESTAMP);
> # This INSERT should fail with ERROR 1685 (bad UNIX TIMESTAMP)
> INSERT INTO t1 (b) VALUES('2000-01-01 12:00:61');
> SELECT * FROM t1;
>
> DROP TABLE t1;
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/drizzle/+bug/597778/+subscribe
>

[Andrew Hutchings (linuxjedi)]

The IERS will only insert a leap second at the last second of a calendar quarter (as previously stated, this is usually the last second of June or December. I believe the SQL standard allows for (but doesn't define) 2 leap seconds in this period (2 leap seconds has never happened in one go yet though). Different databases implement this in different ways. Oracle and MSSQL I believe ignore it completely. DB2 uses 24:00:00 instead (probably a bad idea).

My suggestion is to make the rule stricter: Allow 2 leap seconds but only on the last second of a calendar quarter.

[Andrew Hutchings (linuxjedi)]

Although the actual bug here is that seconds '60' and '61' are not stored correctly. So I guess either we keep them and fix it or drop them.

[Andrew Hutchings (linuxjedi)]

And the reason is you can't store leap seconds in a timestamp field (since it is a timestemp, doh!). So this needs to be fixed.