Jump to content

1292 Incorrect datetime value - converting to MySQL 8


ober

Recommended Posts

I am converting a site to MySQL 8 and PHP 8.1 (from MySQL 5.7 and PHP 7.4).  I have a number of datetime fields in the database and when I try to update one of them, I am getting the error: 

Quote

PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2022-07-09 21:02:04 EDT'

The code I am using is this:

$this->userobj->uaction = date("Y-m-d H:i:s");

If I output that code to the error log I get the date fully as expected.  But for some reason, MySQL is getting it with the timezone attached.  I'm about to pull my hair out because I cannot figure out how that's getting changed between me setting the value and MySQL processing it.  Does anyone know why this might be happening?  Some kind of automatic formatting in MySQL?

Link to comment
Share on other sites

this is the first time i/we have seen this problem. it wouldn't be anything MySql is doing since it results in an error. most likely something between the point where you are logging the value and where it is being sent to the database server. the only thing that comes to mind that results in a three character time zone value is the date 'T' format character. could this value actually be coming from some sort of date/time picker, rather than that date() call you are showing? it would take seeing all the code needed to reproduce the problem.

Edited by mac_gyver
Link to comment
Share on other sites

1 hour ago, ober said:

But for some reason, MySQL is getting it with the timezone attached.

There is no automatic thing anywhere that will change a string value to attach a timezone to it simply because it looks like a date. Your application will have some sort of explicit code to do this, and the first place I would look is whatever powers that "userobj" object. Try searching there, or worst case search your whole codebase for usages of terms like "timezone" or maybe even "EDT" itself.

Link to comment
Share on other sites

Thanks for the replies.  There is no datepicker involved.  I'm literally updating a 'last action' field on the user table by setting it to the current date and time.  The more I thought about it the more I came to the conclusion that it's not the database doing it (as you all said).  I am using phpactiverecord as an ORM so I guess it could be something in there that's translating it.  I'll have to search through that library I guess.  I really need to get away from that library since it's no longer supported but that will require a pretty extensive rewrite.  I've been updating pieces of it myself to support PHP 8.1.

Link to comment
Share on other sites

So, just posting the fix here in case someone else stumbles on this.  In the PHPActiveRecord Connection file, there is a date format with a T in it.  No idea why they are completely overriding the built in PHP DateTime object and doing a bunch of stuff with dates, but changing that format seems to resolve my problems at least.  I haven't fully tested the app but it got me past the major hurdle for now.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.