Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a MySQL server installed on Solaris 10u8. Until recently, everything was working fine. Then all of a sudden, the current time according to MySQL is always 00:00:00. CURDATE() seems to work fine except that the time it gives is still 00:00:00. I have some illustrations of my problem below. I have tried rebooting the machine. I have no idea what to do and this is messing up my web application. Any ideas?

-bash-4.1$ Fri Jul  9 11:01:42 EDT 2010
.......
.......
mysql> create table timetest (datetime datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into timetest values (curtime());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from timetest;
+---------------------+
| datetime            |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into timetest values (curdate());
Query OK, 1 row affected (0.00 sec)

mysql> select * from timetest;
+---------------------+
| datetime            |
+---------------------+
| 0000-00-00 00:00:00 |
| 2010-07-09 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)
share|improve this question

3 Answers 3

up vote 5 down vote accepted

The warning you're getting is as followed:

+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1265 | Data truncated for column 'datetime' at row 1 |
+---------+------+-----------------------------------------------+

You can see it by running show warnings; when before executing another query when a warning is identified.

The schema does not support the time of data you are attempting to insert. For example:

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 11:18:19  |
+-----------+
1 row in set (0.00 sec)

curdate() only produces the date:

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2010-07-09 |
+------------+
1 row in set (0.00 sec)

now() produces the data in the format you want:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-07-09 11:20:31 |
+---------------------+
1 row in set (0.00 sec)

See:

mysql> insert into timetest values(now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from timetest;
+---------------------+
| datetime            |
+---------------------+
| 0000-00-00 00:00:00 |
| 2010-07-09 11:20:56 |
+---------------------+
2 rows in set (0.00 sec)
share|improve this answer
    
+1 This version of mySQL, 5.0.51a-24+lenny4, returns "Warning | 1264 | Out of range value adjusted for column 'datetime' at row 1" -- Tomato, tomatoe.... –  jscott Jul 9 '10 at 15:22
    
MySQL 5.0.45 here. –  Warner Jul 9 '10 at 15:23
    
Thanks for the help; you were right. I realized the bug is coming from a different part of my application. –  Puddingfox Jul 9 '10 at 21:18

Did you see the 1 warning message from insert into timetest values (curtime());? You're using the return value of select CURTIME() -- HH:MM:SS, and attempting to use it as a datetime format -- YYYY-MM-DD HH:MM:SS. CURTIME() does not convert to a valid datetime. From the docs at http://dev.mysql.com/doc/refman/5.1/en/datetime.html:

... Illegal DATETIME, DATE, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00 00:00:00' or '0000-00-00'). ...
share|improve this answer
    
+1 for beating me to the post. –  Warner Jul 9 '10 at 15:24

you should try now() instead.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.