64465

1292 Incorrect datetime value for column 'updated_at'

<h3>Question</h3>

I've created a table in Laravel with standard datetime columns:

Schema::create('lists', function (Blueprint $table) { $table->bigIncrements('id'); $table->string('name'); $table->string('ref'); $table->string('provider'); $table->timestamps(); $table->softDeletes(); $table->unique(['provider', 'ref']); });

When I try to do a simple record creation with Eloquent:

List::updateOrCreate([ 'provider' => 'test', 'ref' => 'S4d3g' ], [ 'name' => 'Plan' ]);

I am given this message (which is a raw console output, so ignore the lack of quotes):

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2020-03-08 02:25:07' for column 'updated_at' at row 1 (SQL: insert into `lists` (`provider`, `ref`, `name`, `updated_at`, `created_at`) values (test, S4d3g, Plan, 2020-03-08 02:25:07, 2020-03-08 02:25:07))

Running the query manually on the database as raw SQL also doesn't work:

insert into `lists` (`provider`, `ref`, `name`, `updated_at`, `created_at`) values ('test', 'S4d3g', 'Plan', '2020-03-08 02:25:07', '2020-03-08 02:25:07')

I'm using MySQL 5.7.

Inexplicably, if I change the date to anything other than 2 AM, it works:

insert into `lists` (`provider`, `ref`, `name`, `updated_at`, `created_at`) values ('test', 'S4d3g', 'Plan', '2020-03-08 01:25:07', '2020-03-08 01:25:07') insert into `lists` (`provider`, `ref`, `name`, `updated_at`, `created_at`) values ('test', 'S4d3g', 'Plan', '2020-03-08 03:25:07', '2020-03-08 03:25:07')

What could be causing this bizarre MySQL level dislike of 2 AM on the timestamp?


<h3>Answer1:</h3>

It appears you are in the US, in a timezone where daylight saving time just started.

Therefore, 2am <em>does not exist</em> today.

<blockquote>

March 8th, 2020 - Daylight Saving Time Starts

When local standard time is about to reach
Sunday, March 8th, 2020, 02:00:00 clocks are turned forward 1 hour to
Sunday, March 8th, 2020, 03:00:00 local daylight time instead.

Sunrise and sunset will be about 1 hour later on March 8th, 2020 than the day before. There will be more light in the evening.

</blockquote>

DATETIME in MySQL uses a local time (the timezone can be set in a multitude of ways) and in your case it's probably <em>your</em> local time, which is why you run into this issue. If you actually meant <em>UTC</em>, you'd have to set the timezone to UTC first using SET time_zone = "+00:00" or by setting the correct global configuration.

Since your PHP library is generating this (invalid) time, I assume you have a mismatch between the timezones used by PHP and by MySQL.

来源:https://stackoverflow.com/questions/60584021/1292-incorrect-datetime-value-for-column-updated-at

Recommend

  • Neural Network Ordinal Classification for Age
  • Unable to load RSA public key
  • Implementing History.js HTML4 Fallback
  • Retrieve user data from Firebase database
  • Using forecast.gts (package hts) with external regressor and parallel processing
  • Using Android Voice Control launch my Activity
  • thrift installation error
  • Mule:Magento connector: get-product operation bug
  • python package compiled with nuitka fails with segmentation fault
  • Java, How to refresh JTable in one frame from another frame
  • Replace Windows command FTP -s:E:\\FtpScript.txt with SFTP? [duplicate]
  • Starting a javascript prompt after a button is clicked
  • Use animate() with series of levelplots in R raster
  • How to echo values of an associative array returned by function
  • Rails Template Error with Heroku
  • Pyinstaller GLIBC_2.15 not found
  • JQuery Validate plugin submitting when it shouldn't
  • Arc gradients in Flutter?
  • Java 11 and E(fx)clipse JavaFX plugin on Eclipse 4.9: An error has occurred - see the log file
  • Use awk to convert GPS Position to Latitude & Longitude
  • Adding Dynamic Row and Data on Checkbox Click
  • android : speech recognition what are the technologies available
  • How to fetch asset modification history in hyperledger fabric
  • $this->a->b->c->d calling methods from a superclass in php
  • Example of using Service Exists MSBuild task in Microsoft.Sdc.Tasks?
  • Circular Left Rotation Algorithm in C#
  • How to use array in autohotkey?
  • Generate and export point cloud from Project Tango
  • ASP.NET MVC razor view, post to a different model from the original model binding?
  • How can i move Clearcase dyamic/snapshot views to another host (Linux)
  • Tensorflow Dataset API restore Iterator after completing one epoch
  • How to use Kaminari pagination gem with Sinatra and Mongoid?
  • Rotating Towards Path in OpenGL
  • C++ STL stack pop operation giving segmentation fault
  • How to turn off notice reporting in xampp?
  • read part of h5 dataset python
  • Grails - How to implement a foreign key relationship not using an id column?
  • XEP-0166: Jingle protocol implementation for voice/video chat in iOS
  • What does the “id” field in an Android “Google Play Music” broadcast intent correspond to?
  • convert json to excel in java