| Bug #57680 | Incorrect Datetime | ||
|---|---|---|---|
| Submitted: | 23 Oct 2010 10:56 | Modified: | 24 Nov 2010 12:14 |
| Reporter: | dalceu vaz | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.1 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[23 Oct 2010 11:04]
Peter Laursen
On 5.1.51 also I get
select cast('00:00:00' as time) -- returns '00:00:00'
select cast('2010-10-17 00:00:00' as datetime) -- Error Code : 1064 You have an error in your SQL syntax blahblahblah
'00:00:00' is definitely a valid TIME. TIMESTAMP has same issue as DATETIME btw.
Peter
(not a MySQL person)
[23 Oct 2010 11:24]
Peter Laursen
I tried both 5.0.90 and 5.5.6. They behave identically:
select cast('00:00:00' as time); -- returns '00:00:00'
select cast('2010-10-17 00:00:00' as datetime); -- returns '2010-10-17 00:00:00'
select cast('2010-10-17 00:00:00' as timestamp); -- syntax error
[23 Oct 2010 11:44]
Davi Arnaut
Dalceu, It is a invalid date, you are probably forgetting about DST... :-)
[23 Oct 2010 11:46]
Davi Arnaut
Just to make my remark clear, Brazil started their daylight saving time (DST) on October 17, 2010.
[23 Oct 2010 11:49]
Peter Laursen
@Davi .. about thio "Dalceu, It is a invalid date, you are probably forgetting about DST... :-)" ..what exact statement are you referring to with this comment? DST and non-DST timezones change at 2am in Europe (but not sure about US). There is a bug in either 5.1.51 or 5.5.6 (I think 5.1.51).
[23 Oct 2010 11:52]
Peter Laursen
@Davi .. I believe you miss the point. Compare this
select cast('2010-10-17 00:00:00' as datetime);
.. in different server versions. And I am not using a Brazilian TZ at all. DST will stop here (Denmark) Sunday October 31st 2 am.
[23 Oct 2010 11:53]
Davi Arnaut
Peter, TIMESTAMP values are converted from the server's current time zone. Since Dalceu's name appears to be Brazilian (I'm also), the DST change we had would explain the invalid date error.
[23 Oct 2010 12:17]
Peter Laursen
On all my servers I have
show variables like '%time_zone%';
/*
Variable_name Value
---------------- --------------
system_time_zone Rom, sommerti -- means 'Rome, summer time'
time_zone SYSTEM
*/
but I am not able to reproduce now
select version(); -- 5.1.51-community
select cast('2010-10-17 00:00:00' as timestamp); -- error
select cast('2010-10-18 00:00:00' as timestamp); -- error
select cast('2010-10-19 00:00:00' as timestamp); -- error
select cast('2010-12-19 00:00:00' as timestamp); -- error
select cast('2010-10-17 00:00:00' as datetime); -- works
select cast('2010-10-18 00:00:00' as datetime); -- works
select cast('2010-10-19 00:00:00' as datetime); -- works
select cast('2010-12-19 00:00:00' as datetime); -- works
.. and
I really doubt than any DST implementation it the wordl
[24 Oct 2010 12:14]
Davi Arnaut
Peter, As documented in the manual, CAST (and CONVERT) do not accept the TIMESTAMP type. That's why you get a syntax error -- it has nothing todo with the "invalid date" error from the bug reporter.
[24 Oct 2010 12:18]
Davi Arnaut
A quick explanation of Brazil's DST: http://en.wikipedia.org/wiki/Daylight_saving_time_around_the_world#Brazil
[25 Nov 2010 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Strange "incorrect datetime value". How to repeat: CREATE TABLE `teste` ( `teste` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`teste`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into teste values ('2010-10-17 00:00:00');//Incorrect datetime value: '2010-10-17 00:00:00' for column 'teste' at row 1 insert into teste values ('2010-10-16 00:00:00');// ok insert into teste values ('2010-10-18 00:00:00');// ok