Level 75
You do have same field type on local and production?
Jun 23, 2021
10
Level 9
Invalid datetime format when saving model
I am getting the following error when trying to save the model to the database.
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2021-03-14 02:48:04' for column `cref_radmin`.`mrelevance_realestate_listings`.`photos_updated_at` at row
1
Which is very strange because when I test this exact same thing locally it works without any problems and the even more bizarre thing is the datetime value looks correctly formatted to me and I don't understand why MySQL/MariaDB is complaining about it being incorrect. The photos_updated_at column is just a regular timestamp field, nothing special.
Does anyone know what might be causing this issue?
Level 9
Yes, both have the same migrations applied and I've even tried by importing mysqldump from production into local database. Both are using the same database and database version. The only difference is the production server is running WHM/CPanel, but I have the same my.cnf config file applied to my local database server (which is a docker container).
Level 122
You said column is a timestamp, not a datetime ?
Level 75
If you make it a date field, does it work? Is it currently a timestamp field.
Level 9
Currently a timestamp field: https://photos.app.goo.gl/2ii9ZXPDSTyRiCju8
It seems when I convert it to datetime it doesn't throw the error anymore, however I then get the same previous error on the created_at column which is a timestamp field. So it seems like something happened with the database and it doesn't like the timestamp fields anymore?
I'm currently running MariaDB 10.3 on WHM/Cpanel (I have full root access on the dedicated server)
Level 54
as snapey mentioned, datetime and timestamp look the same but both are not exactly same.
https://www.eversql.com/mysql-datetime-vs-timestamp-column-types-which-one-i-should-use/
Level 9
And the really weird thing is I am able to create a new model just fine, so it seems like something weird is happening for this one specific record/data even though it looks fine and normal to me.
Level 75
What are you exactly doing? Show your code and models as well.
Level 9
I am just trying to take some data from a 3rd party API do some transformations on it and save it to the database.
All I am trying to do is take the following array, fill the model and then save it.
$data = [
"name" => "9031 Southfield Drive, Bridgeview, IL 60455"
"description" => "FULLY REHABBED OPEN FLOOR PLAN 3 BED 2 FULL BATH CAPE COD ON QUIT BLOCK IN THE MIDDLE OF BRIDGEVIEW, ALL NEW ELECTRIC, NEW PLUMBING, NEW WINDOWS, NEW ROOF AND MULTIPLE SKYLIGHTS AND LIST GOES ON AND ON... THESE PICS DON'T DO THIS HOME JUSTICE OVERSIZED GARAGE AND LARGE BACKYARD!"
"listing_id" => "MRD11020457"
"status" => "Closed"
"price" => 313313
"beds" => 3
"baths_full" => 2
"half_baths" => 0
"living_area" => 1450
"originating_system_name" => "mred"
"type" => "Residential"
"ownership" => "Fee Simple"
"subtype" => "Detached Single"
"lot_size_dimensions" => "7800"
"lot_size_acres" => 0.179
"year_built" => 1958
"has_garage" => true
"garage_spaces" => 2.5
"parking_spaces" => null
"parking_features" => null
"has_basement" => false
"basement_type" => "None"
"tax_annual_amount" => 1077.99
"tax_year" => 2019
"tax_id" => "24061180050000"
"virtual_tour_url" => null
"elementary_school_name" => "George W Lieb Elementary School"
"elementary_school_district" => "122"
"middle_school_name" => "Simmons Middle School"
"middle_school_district" => "122"
"high_school_name" => "Oak Lawn Comm High School"
"high_school_district" => "229"
"photos_updated_at" => "2021-03-14 02:48:04"
"created_at" => "2021-03-14 02:47:53"
"updated_at" => "2021-06-21 21:08:03"
"metadata" => array:3 [
"parking_type" => "Garage"
"garage_type" => "Detached"
"garage_details" => "Garage Door Opener(s),Carport"
]
]
$model = new Listing();
$model->fill($data);
$model->save();
Level 9
Doing some further testing directly in PhpMyAdmin, it looks like even if I directly type in the date and time as it appears in the array I posted in the comment above it still throws an exception saying invalid TIMESTAMP value in column. So, it isn't actually an issue with Laravel and is an issue with the database for some reason.
It seems like it has something to do with the hour value in the timestamp where if I set it to 02 is throws the exception, but if I set it to any other hour it saves correctly. But, I'm still confused as to why it would work on my local database but not on the actual staging/production server/database in CPanel.
Please or to participate in this conversation.