3

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)
jamesbtate
  • 567
  • 2
  • 6
  • 14

2 Answers2

5

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)
Warner
  • 23,756
  • 2
  • 59
  • 69
1

you should try now() instead.

Pledge
  • 81
  • 3