Bug #68931 saving some timestamp values are incorrect in mysql 5.5.29

• Submitted: 11 Apr 2013 10:49
• Modified: 12 Apr 2013 12:12
• Reporter: sedat sevgili
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.5.29
• OS: Any
• CPU Architecture: Any
• Tags: timestamp insert

[11 Apr 2013 10:49] sedat sevgili

Description:
When we try to add some timestamp values to any table in any os with mysql 5.5.29, we saw that some values are not saved correctly and also gives a warning message like: "Warning: #1299 Invalid TIMESTAMP value in column ..". The mysql server generally rounds the hour-minute-time section. Here are some timestamp values are incorrectly: 

1971-05-02 00:01:01
1972-05-07 00:01:01
1973-06-03 01:01:01
1976-06-01 00:01:01
1977-04-03 00:01:01
1978-04-02 00:01:01
1978-10-15 00:01:01
1980-04-06 03:01:01
1982-03-28 03:01:01
1988-03-27 02:01:01
1989-03-26 02:01:01
1990-03-25 02:01:01
1993-03-28 01:01:01
1994-03-27 01:01:01
1995-03-26 01:01:01
1999-03-28 01:01:01
2000-03-26 01:01:01
2001-03-25 01:01:01
2004-03-28 01:01:01
2005-03-27 01:01:01
2006-03-26 01:01:01
2007-03-25 03:01:01
2010-03-28 03:01:01
2011-03-28 03:01:01
2012-03-25 03:01:01
2016-03-27 03:01:01
2017-03-26 03:01:01
2018-03-25 03:01:01
2021-03-28 03:01:01
2022-03-27 03:01:01
2023-03-26 03:01:01
2027-03-28 03:01:01
2028-03-26 03:01:01
2029-03-25 03:01:01
2032-03-28 03:01:01
2033-03-27 03:01:01
2034-03-26 03:01:01
2035-03-25 03:01:01

Summarily, when you insert a timestamp with value: 2035-03-25 03:01:01, it saves  it to 2035-03-25 04:00:00 and gives warning: Warning: #1299 Invalid TIMESTAMP value in column ..

How to repeat:
Here is the gist to catch it: https://gist.github.com/sedatsevgili/5362339

[11 Apr 2013 10:50] sedat sevgili

Furthermore, in mysql 5.5.16, it saves correctly.

[11 Apr 2013 11:02] Shane Bester

looks simply like DST issue to me?

[11 Apr 2013 11:12] Miguel Solorzano

Please check your DST how Shane commented. Thanks.

[11 Apr 2013 11:15] sedat sevgili

when i run "SELECT COUNT(*) FROM mysql.time_zone_name;"  it gives 0 to me. Are there any way to check my dst of my server?

[11 Apr 2013 11:36] sedat sevgili

my time related variables are: 

datetime_format => %Y-%m-%d %H:%i:%s
lc_time_names => en_US
system_time_zone => EEST
time_format => %H:%i:%s
time_zone => SYSTEM

[11 Apr 2013 17:44] Miguel Solorzano

Please check the Daylight Save Time for EEST.

[12 Apr 2013 12:10] sedat sevgili

so, is there any way to fetch always what i write with timestamp, whatever my eest setting is?

[12 Apr 2013 12:12] sedat sevgili

and also isn't interesting that when we use mysql 5.5.16 this issue doesnt arise?