I have manually (GASP!) entered a MySQL command at the command line, and I received a Warning which I cannot even begin to understand. (And before anyone says anything, yes, I KNOW: 1. Using the command line interface is not the best approach; 2. My table is NOT named "TABLE_NAME" and my column is NOT named "DateColumn" and my RecordID value is NOT really "1234"; 3. Maybe my column type should be TIMESTAMP, but for now, it's not. Moving on....)
我手动（GASP！）在命令行输入了一个MySQL命令，我收到了一个警告，我甚至无法理解。 （在任何人说什么之前，是的，我知道：1。使用命令行界面不是最好的方法; 2.我的表没有命名为“TABLE_NAME”而我的列没有命名为“DateColumn”而我的RecordID值不是真的是“1234”; 3.也许我的列类型应该是TIMESTAMP，但是现在，它不是。继续......
Attempting to enter a value for the date "July 26th, 2012 at 2:27 PM (GMT)", I keyed:
试图输入“2012年7月26日下午2:27（格林威治标准时间）”的日期值，我键入：
mysql> update TABLE_NAME set DateColumn="2012-07-26 14:27:00" where RecordID="1234";
I received:
我收到了：
Query OK, 1 row affected, 1 warning (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 1
So, I keyed:
所以，我键入了：
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value for column 'DateColumn' at row 1 |
+---------+------+-----------------------------------------------------+
Weird, I thought. So I checked the table first to confirm the column (field) type:
很奇怪，我想。所以我先检查了表格以确认列（字段）类型：
mysql> describe TABLE_NAME;
+------------+----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
| DateColumn | datetime | YES | | NULL | |
+------------+----------+------+-----+-------------------+-------+
BUT the value DOES get written properly to the database, and not truncated, AFAIK:
但是，值正确地写入数据库，而不是被截断，AFAIK：
mysql> select * from TABLE_NAME where RecordID="1234";
+-----------------------------------------------+
| RecordID | Date_Column | BlahBlahBlah |
+----------+---------------------+--------------+
| 1234 | 2012-07-26 14:27:00 | something.. |
+----------+---------------------+--------------+
I've already searched *.com for a solution. I've already Googled for an explanation. I've already read up at http://dev.mysql.com/doc/refman/5.5/en/datetime.html where it says:
我已经在*.com上搜索了一个解决方案。我已经谷歌搜索了解释。我已经在http://dev.mysql.com/doc/refman/5.5/en/datetime.html上阅读了它说：
MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
I even had a slight suspicion that it had something to do with the date or time at which I was making the entry; so I will state that the server on which the database is located is on Pacific Daylight Time (GMT-8, except right now GMT-7 for DST); I am logged in (SSH) from a client on EDT (which should not matter); and I am storing all Date_Column values as GMT. At the time I was entering the value "2012-07-26 14:27:00" all three dates were well AFTER that, on 7/30/12. Not that it should matter -- I should be able to enter future dates without getting an error -- but thought it might be helpful for you to know. So --
我甚至怀疑它与我进入的日期或时间有关;所以我将说明数据库所在的服务器位于太平洋夏令时（格林威治标准时间-8，现在除了现在的格林尼治标准时间-7用于夏令时）;我从EDT的客户端登录（SSH）（这应该不重要）;我将所有Date_Column值存储为GMT。在我输入价值“2012-07-26 14:27:00”时，所有三个日期都在7月30日之后。并不重要 - 我应该能够输入未来的日期而不会出现错误 - 但是认为它可能对您有所帮助。所以 -
WHY, OH WHY is "2012-07-26 14:27:00" an Out-of-Range Value?
为什么，OH为什么“2012-07-26 14:27:00”是一个超出范围的价值？
My MySQL client API version is 5.1.49.
我的MySQL客户端API版本是5.1.49。
This is the first time I've ever posted on *. Thank you in advance for your suggestions.
这是我第一次在*上发布。提前感谢您的建议。
1 个解决方案
#1
3
I wonder if it is converting it to some date format from string. That dateformat would then have too much precision which would be truncated. Try casting it to a datetime before assigning.
我想知道它是否将它从字符串转换为某种日期格式。然后，该dateformat将具有过多的精度，这将被截断。在分配之前尝试将其投射到日期时间。
#1
3
I wonder if it is converting it to some date format from string. That dateformat would then have too much precision which would be truncated. Try casting it to a datetime before assigning.
我想知道它是否将它从字符串转换为某种日期格式。然后，该dateformat将具有过多的精度，这将被截断。在分配之前尝试将其投射到日期时间。