Migrations: timestamp VS dateTime VS date VS timestamps

Can you give me clarification and explain the differences between:

$table->timestamp('published_at');
$table->dateTime('published_at');
$table->date('published_at');
$table->timestamps('published_at');

Also, what are the advantages or disadvantages of using one over another? Are there some reasons when we need to avoid the use of some of these?

michaeldyrynda

9 years ago

Best Answer

Level 41

timestamp and dateTime are similar - they store a date (YYYY-MM-DD) and time (HH:MM:SS) together in a single field i.e. YYYY-MM-DD HH:MM:SS. The difference between the two is that timestamp can use (MySQL's) CURRENT_TIMESTAMP as its value, whenever the database record is updated. This can be handled at the database level and is great for Laravel's created_at and updated_at fields.

Note also that timestamp has a limit of 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC (source), whilst dateTime has a range of 1000-01-01 00:00:00 to 9999-12-31 23:59:59, so consider what you plan on storing in that field before determining which type to use.

date stores just the date component i.e. YYYY-MM-DD (1000-00-01 to 9999-12-31).

timestamps doesn't take an argument, it's a shortcut to add the created_at and updated_at timestamp fields to your database.

74 likes

Willchan

9 years ago

Level 3

I have tried both timestamp('completed_at') and timestamps(). Somehow the columns created by timestamps() are recorded in UTC time, while completed_at is in local time.

Jam0r

8 years ago

Level 8

Good information.

If one was planning ahead, out of interest, could you use datetime fields for created_at and updated_at for laravel?

How could this be done? Through an observer perhaps?

Does carbon work with datetime fields?

Khuthaily

6 years ago

Level 3

I know it has been three years since the question was posted, but I thought of sharing what I do in migrations. To make sure my database will handle dates beyond the year 2038, I replace

$table->timestamps();

with

$table->dateTime('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));

$table->dateTime('updated_at')->default(DB::raw('CURRENT_TIMESTAMP'))->onUpdate(DB::raw('CURRENT_TIMESTAMP'));

In other words, instead of using the 'timestamps()' shortcut for 'created_at' and 'updated_at' with the default 'timestamp' type, I create each column separately as 'datetime'.

Feedback is welcome.

1 like

ChrLu

6 years ago

Level 1

$table->dateTime('updated_at')->default(DB::raw('CURRENT_TIMESTAMP'))->onUpdate(DB::raw('CURRENT_TIMESTAMP'));

did not create the on update during migration in my environment. So I am using this instead, what works fine.

$table->dateTime('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));

$table->dateTime('updated_at')->nullable()->default(DB::raw('NULL on update CURRENT_TIMESTAMP'));

4 likes

Please or to participate in this conversation.