日期从ActiveRecord恢复为MySQL的2000

本文介绍了日期从ActiveRecord恢复为MySQL的2000-01-01的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试在MySQL表中节省时间。但是，日期恢复为2000年1月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">]

因此，当我检索记录时，日期为2000-01-01。

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

表在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

$ b $中，查看与您的MySQL服务器版本相对应的手册以获取在'CURRENT_DATE（）'附近使用的正确语法。 b

显然，这很难。

推荐答案

这是因为您的 last_visit 列是 time 而不是 datetime 类型。最初，我假设mysql只使用 01-01-2000 作为某种默认日期来内部表示时间字段，但是看来01-01-2000是这样做的Rails而不是mysql。查看表示没有日期的时间在红宝石中

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