"Warning: Out of range value for column" inserting a datetime value into MySQL
13,922
You are receiving an warning because you are inserting a long date into a date field. The date filed is "YYYY-MM-DD" (http://dev.mysql.com/doc/refman/5.1/en/datetime.html).
I did my own test in Mysql, take a look:
mysql> create table bigtable (created_at date);
Query OK, 0 rows affected (0.07 sec)
mysql>
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1265 | Data truncated for column 'created_at' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from bigtable;
+------------+
| created_at |
+------------+
| 2012-06-08 |
+------------+
1 row in set (0.00 sec)
if you want to avoid the warning, just pass "YYYY-MM-DD" or change the column to datetime,
Good luck
Author by
seanieb
Updated on June 09, 2022Comments
-
seanieb almost 2 years
Pulling some data from MongoDB and inserting it into a MySQL database, using Python and the Mysqldb library.
+-------------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+------------+------+-----+---------+----------------+ | id | int(16) | NO | PRI | NULL | auto_increment | | subject | tinytext | NO | | NULL | | | created_at | datetime | NO | | NULL | | +-------------------+------------+------+-----+---------+----------------+
created_at = "2012/06/08 11:47:40 -0700" sql1 = ("INSERT INTO `items` (`description`, `created_at`) VALUES (%s, %s)", (description, created_at) try: cursor.execute(*sql1) except MySQLdb.Error, e: print "Error %s: %s" % (e.args[0], e.args[1])
For the first insert into the database I get a warning, however I don't receive that warning for subsequent inserts.
conf/db_tools-import.py:53: Warning: Out of range value for column 'created_at' at row 1 cursor.execute(*sql1)
How can I resolve this? Thanks.
-
Derek Litz about 11 yearsWhy is this the accepted answer? I can see in the question that the column is already a DATETIME...
-
jcho360 about 11 years@DerekLitz the problem was not the datetime,he was trying to insert complete time and the value was truncate and generating a waning
-
Matthew about 8 yearsI had the same problem. Changing the column type from TIMESTAMP to DATETIME fixed the out of range error.