1

I am trying to save a time in a MySQL table. However, the date reverts to 2000-01-01.

1.9.2p320 :036 > vv = Visitor.new
 => #<Visitor id: nil, ip_address: nil, num_day_visits: nil, last_visit: nil> 
1.9.2p320 :037 > vv.last_visit = Time.now; vv.ip_address = "3.3.3.3"
 => "3.3.3.3" 
1.9.2p320 :038 > vv.num_day_visits = 1
 => 1 
1.9.2p320 :039 > vv
 => #<Visitor id: nil, ip_address: "3.3.3.3", num_day_visits: 1, last_visit: "2012-10-11 01:31:04"> 
1.9.2p320 :040 > vv.save
  SQL (0.2ms)  BEGIN
  SQL (0.7ms)  INSERT INTO `visitors` (`ip_address`, `last_visit`, `num_day_visits`) VALUES (?, ?, ?)  [["ip_address", "3.3.3.3"], ["last_visit", 2012-10-11 01:31:04 -0400], ["num_day_visits", 1]]
   (0.5ms)  COMMIT
 => true 
1.9.2p320 :042 > vv
 => #<Visitor id: 1199, ip_address: "3.3.3.3", num_day_visits: 1, last_visit: "2012-10-11 01:31:04"> 
1.9.2p320 :043 > Visitor.find(:all,:conditions=>{:ip_address => "3.3.3.3"})
  Visitor Load (1.4ms)  SELECT `visitors`.* FROM `visitors` WHERE `visitors`.`ip_address` = '3.3.3.3'
 => [#<Visitor id: 1199, ip_address: "3.3.3.3", num_day_visits: 1, last_visit: "2000-01-01 05:31:04">]

So when I retrieve the record, the date is 2000-01-01.

The table in MySQL:

mysql> describe visitors ;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| ip_address     | varchar(255) | NO   |     | NULL    |                |
| num_day_visits | int(11)      | NO   |     | NULL    |                |
| last_visit     | time         | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Update: I created a toy table to play around the conversion from time to datetime. This is what happens:

mysql> select * from example ;
+----+----------+
| id | mytime   |
+----+----------+
|  1 | 11:13:00 |
+----+----------+
1 row in set (0.00 sec)
mysql> alter table example change mytime mytime datetime;
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 1

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

So that destroys the value. I tried to go back by altering the table to time, and using a new row. Starting over:

mysql> select * from example ;
+----+----------+
| id | mytime   |
+----+----------+
|  2 | 11:13:00 |
+----+----------+
1 row in set (0.00 sec)
mysql> ALTER TABLE example CHANGE mytime mytime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP();
ERROR 1067 (42000): Invalid default value for 'mytime'
mysql> ALTER TABLE example CHANGE mytime mytime DATETIME NOT NULL DEFAULT CURRENT_DATE();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_DATE()' at line 1

Apparently, this is hard.

Community
  • 1
  • 1
highBandWidth
  • 16,751
  • 20
  • 84
  • 131

3 Answers3

6

This is because your last_visit column is of the time and not datetime type. Initially I assumed that mysql just uses 01-01-2000 as some sort of default date for representing time fields internally, however it appears that 01-01-2000 is the doing of rails and not mysql. Look at Represent a time with no date in ruby

Community
  • 1
  • 1
saihgala
  • 5,724
  • 3
  • 34
  • 31
1

Run this on your mysql prompt,

ALTER TABLE visitors CHANGE last_visit last_visit datetime;

beck03076
  • 3,268
  • 2
  • 27
  • 37
0

Understanding my problem using Ashish and Beck's answers, I was only storing the time in MySQL, and rails was adding on a default 2000-01-01 to it. I tried to find a way to update existing data to include the date, using today's date to update the records. The following is what I could get.

Assume we have

mysql> select * from example ;
+----+----------+
| id | mytime   |
+----+----------+
|  2 | 11:13:00 |
+----+----------+
1 row in set (0.00 sec)

This how I change mytime to an updated datetime

mysql> alter TABLE example ADD  new_time datetime;
Query OK, 1 row affected (0.04 sec)
mysql> UPDATE example SET new_time = CONCAT( CURDATE(), " ", mytime );
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> ALTER TABLE example DROP mytime ;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE example CHANGE new_time mytime DATETIME;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from example ;
+----+---------------------+
| id | mytime              |
+----+---------------------+
|  2 | 2012-10-11 11:13:00 |
+----+---------------------+
1 row in set (0.00 sec)

I couldn't find a way to combine the data update and changing the field type in one example. Anyone have better ideas?

Community
  • 1
  • 1
highBandWidth
  • 16,751
  • 20
  • 84
  • 131
  • well the issue is that the _default value must be a constant; it cannot be a function or an expression_ More info [here](http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html). One way to get around firing 3 alters and one update is to create a new table like `create table updated_example as select id, CAST(CONCAT(CURDATE()," ", mytime) AS DATETIME) mytime from example` – saihgala Oct 12 '12 at 05:39
  • @AshishSaihgal, then we'd have to delete the old one and change the name of the new table. I guess it'd still be faster to create a new table, if there aren't too many other columns? – highBandWidth Oct 12 '12 at 15:17