1

Below is the CREATE TABLE statement used to create my table:

CREATE TABLE IF NOT EXISTS `data_received` (
  `id` int(10) unsigned NOT NULL,
  `date_received` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `edit_time` datetime NOT NULL
}

Below is how data is saved in the table upon INSERT, if 'edit_time' value is not provided:

id   date_received         edit_time
1    2012-10-12 12:15:46   0000-00-00 00:00:00

Additionally, I get this warning message: Warning: #1264 Out of range value for column 'edit_time' at row 1

  • So, my question is, is there any impact of ignoring this warning message?
  • I am expecting 10 to 15 rows to be inserted every minute. So, will there be any performance degradation?
  • Is there anything I can do to stop this warning message from occurring?
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Shahriar N Khondokar
  • 578
  • 2
  • 7
  • 19

1 Answers1

0

When MySQL stores a value in a numeric column that is outside the permissible range of the column data type, the result depends on the SQL mode in effect at the time: If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard.If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.

Sivagopal Manpragada
  • 1,554
  • 13
  • 33
  • I don't get why 0000-00-00 00:00:00 is considered out of range. – John Dvorak Oct 17 '12 at 04:55
  • 1
    @JanDvorak ["mysql datetime range"](http://dev.mysql.com/doc/refman/5.0/en/datetime.html) –  Oct 17 '12 at 04:57
  • 2
    month limit is 1-12 days limit is 1-31 00 is out of range – Sivagopal Manpragada Oct 17 '12 at 04:58
  • "Illegal DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00')." gives rise to the thinking that 0000-00-00 00:00:00 is legal – John Dvorak Oct 17 '12 at 05:00
  • As of MySQL 5.0.2, MySQL does not accept TIMESTAMP values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special “zero” value '0000-00-00 00:00:00'. that is an exception not legal – Sivagopal Manpragada Oct 17 '12 at 05:07