Query builder not inserting timestamps

Question

I am using Query builder to insert data all fields are been inserted but timestamps like created_at and updated_at are not inserting they all have default 0:0:0 values my insert query is

$id = DB::table('widgets')
            ->insertGetId(array(
                'creator' => Auth::user()->id,
                'widget_name' => $request->input('widget_name'),
                'pages' => json_encode($request->input('pages')),
                'domain' => $request->input('domain'),
                "settings" => $settings,
            ));

voodoo417 · Accepted Answer · 2019-05-23T23:14:42.643

86

All right. Fields created_at,update_at and deleted_at are "part" of Eloquent. You use Query Builder=> inserting doesn't affect on these two fields (created_at and updated_at). You should define it manually like:

$id = DB::table('widgets')
        ->insertGetId(array(
            'creator' => Auth::user()->id,
            'widget_name' => $request->input('widget_name'),
            'pages' => json_encode($request->input('pages')),
            'domain' => $request->input('domain'),
            "settings" => $settings,
            "created_at" =>  \Carbon\Carbon::now(), # new \Datetime()
            "updated_at" => \Carbon\Carbon::now(),  # new \Datetime()
        ));

edited May 23 '19 at 23:14

answered Oct 31 '15 at 13:42

voodoo417

11,861
3
36
40

1

isn't there any way by which it can be done automatically – Samundra Khatri Oct 31 '15 at 13:46
@Samundra KC do you want use only QueryBuilder? – voodoo417 Nov 01 '15 at 01:30
Use Laravel Macros: https://medium.com/fattihkoca/laravel-auto-save-timestamps-with-query-builder-without-using-eloquent-123f7ebfeb92 – Fatih Koca Nov 29 '18 at 13:28

score 8 · Answer 2 · edited Jan 19 '20 at 08:07

8

In my case, I'm using date('Y-m-d H:i:s'); to get current dateTime. It works every time.

    $id = DB::table('widgets')
        ->insertGetId(array(
            'creator' => Auth::user()->id,
            'widget_name' => $request->input('widget_name'),
            'pages' => json_encode($request->input('pages')),
            'domain' => $request->input('domain'),
            "settings" => $settings,
            "created_at" =>  date('Y-m-d H:i:s'),
            "updated_at" => date('Y-m-d H:i:s'),
        ));

edited Jan 19 '20 at 08:07

answered Jan 19 '20 at 07:34

akmoji

79
1
2

"It works every time" - as long as you have an equal timezone in the DB and PHP ;) – James Bond May 01 '21 at 16:15

score 1 · Answer 3 · answered Dec 19 '22 at 07:12

1

Another way is to use raw expression and 'CURRENT_TIMESTAMP' as the value assuming the database is a MySQL database.

$now = DB::raw('CURRENT_TIMESTAMP');
$rowID = DB::table('user')->insertGetId([
  'name' => $request->name,
  'updated_at' => $now,
  'created_at' => $now
]);

answered Dec 19 '22 at 07:12

MobCode100

166
1
5

1

This answer is the fastest. When you're mass assigning, Carbon::now() is slower. 110k rows is 15 seconds faster. Thanks! – PhillipMcCubbin Jan 17 '23 at 18:35

Query builder not inserting timestamps

3 Answers3

Linked