Bug #10078 timestamp changed during insert

• Submitted: 21 Apr 2005 22:34
• Modified: 21 Apr 2005 23:02
• Reporter: Carl Ludewig
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.10
• OS: Linux (Linux, Max OS X)
• CPU Architecture: Any

[21 Apr 2005 22:34] Carl Ludewig

Description:
I discovered during testing that certain timestamps get altered during insert. Specifically, it seems that any timestamp in the range '2005-04-03 02:00:00' to '2005-04-03 02:59:59' is changed to '2005-04-03 03:00:00'. The bug is specific to the day/hour. If I change the date or hour, the bug does not appear. This is a serious bug, because it can go undetected during testing and cause mysterious problems in production.

Seems like a very strange and data-specific problem. I tried different data entry formats and achieved the same results. I tested on 3 machines: 4.1.10 on Mac OS X, 4.1.10a on Linux and  3.23.33 on Solaris. All produced the same results. FYI - I performed my testing 2005-04-21 1-3 PM PDT, which I only mention because this is a timestamp problem.

How to repeat:
These commands:

drop table if exists mytable;
create table mytable (id integer, value timestamp);
insert into mytable values (1, '2005-04-03 02:00:00');
insert into mytable values (2, '2005-04-03 02:45:00');
insert into mytable values (3, '2005-04-03 02:15:12');
insert into mytable values (4, '2005-04-03 02:59:59');
insert into mytable values (5, '2005-04-03 03:00:00');
insert into mytable values (6, '2005-05-03 02:45:00');
insert into mytable values (7, '2005-04-02 02:15:12');
insert into mytable values (8, '2005-04-03 03:45:00');
select * from mytable;

Produce this result:

id      value
1       2005-04-03 03:00:00
2       2005-04-03 03:00:00
3       2005-04-03 03:00:00
4       2005-04-03 03:00:00
5       2005-04-03 03:00:00
6       2005-05-03 02:45:00
7       2005-04-02 02:15:12
8       2005-04-03 03:45:00

Note that rows 1-4  return values different from those inserted.

Suggested fix:
No idea.

[21 Apr 2005 22:48] Carl Ludewig

Daylight's Savings Time! Sorry about that - that explains it. Not a bug, I suppose.

[21 Apr 2005 22:53] Carl Ludewig

not a bug

[21 Apr 2005 23:02] Alexander Keremidarski

Not a bug of course.

Daylight Saving Time changes are *very* confusing not only for the servers world, but to normal human beings too. However they exist and we do our best to deal with them.

Thanks for your effort to report that problem. We appreciate your effort.