1292 Incorrect datetime value - converting to MySQL 8

[ober]

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?

[mac_gyver]

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 July 10, 2022 by mac_gyver

[requinix]

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.

[ober]

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.

[ober]

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.