laravel eloquent orm insert query auto generated created at and updated at are not coherent with the database

Question

I'm using Lumen 5.2 to access MySql. After listen the query and log the sql, I see

[object] (Illuminate\\Database\\Events\\QueryExecuted: {\"sql\":\"insert into
`RedEnvelopes` (`User_id`, `amount`, `remain`, `expiredAt`, `to_User_id`,        
`message`, `type`, `dividend`, `updatedAt`, `createdAt`) values (?, ?, ?, ?, 
?, ?, ?, ?, ?, ?)\",\"bindings\":
[5000219,\"1\",\"1\",1478073412,\"8000228\",\"你的名字\",1,1,\"2016-11-02 
15:51:52\",\"2016-11-02 15:51:52\"],\"time\":0.77,\"connection\":
{},\"connectionName\":\"mysql\"})

And using Model::find() to get the record, the return is coherent with the sql above. But when I access MySql server directly and get the record by SQL, the createdAt and updatedAt are later than the the binding values by 8 hours.

I'm totally confused. Anyone else have encountered this question? I need some help.

Look here http://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql Your local time is probably different from mysql timezone — Mihai, Nov 02 '16 at 08:13
I believe laravel stores dates in UTC. When retrieving it, sets it back to the system timezone unless you specify a timezone. EDIT: tried to confirm this but don't really have time to go through the [Carbon docs](http://carbon.nesbot.com/docs/). — Laurent Meganck, Nov 02 '16 at 08:29
Is you're Laravel applications timezone set to the correct one? — Rwd, Nov 02 '16 at 08:29
@Mihai mysql> select now(); +---------------------+ | now() | +---------------------+ | 2016-11-02 16:21:12 | +---------------------+ 1 row in set (0.01 sec) I think Mysql timezone is the same as my local timezone. — limen, Nov 02 '16 at 08:34
@LaurentMeganck I overwrite the freshTimestamp method { return new Carbon(null, config('app.timezone')); } The app.timezone is set to my local timezone. — limen, Nov 02 '16 at 08:36
More information. I run the laravel query sql on mysql client, the inserted row is correct. — limen, Nov 02 '16 at 08:51

limen · Answer 1 · 2017-03-09T06:02:05.160

0

I think the problem could be resolved by 3 steps.

First, set the timezone to your local timezone. Mine is Asia/Shanghai. See .env

APP_TIMEZONE=Asia/Shanghai

Second, set app timezone in config/app.php

'timezone' => env('APP_TIMEZONE'),

Last, change created_at/updated_at column to datetime type.

edited Mar 09 '17 at 06:02

answered Nov 03 '16 at 08:44

limen

67
7

laravel eloquent orm insert query auto generated created at and updated at are not coherent with the database

1 Answers1