Considering DATETIME over TIMESTAMP for created_at/updated_at?

[iwasherefirst2]

In Laravel, the created_at and updated_at columns typically use the timestamp data type in MySQL. This approach might stem from earlier MySQL versions where only TIMESTAMPS columns could automatically be set to CURRENT_TIMESTAMP at the database level, unlike DATETIME columns. However, since MySQL 5.6.5, DATETIME columns have gained the capability to also default to CURRENT_TIMESTAMP.

While TIMESTAMP and DATETIME fields serve similar purposes, there are two key differences to consider:

• TIMESTAMP fields are limited to dates up to the year 2038.
• TIMESTAMP fields undergo automatic timezone conversion based on the database's timezone settings. For instance, if your database's timezone is set to SET time_zone = '+02:00'; and you attempt to set created_at to a specific time, the actual stored time in the database will be adjusted to UTC, effectively reducing it by two hours. This behavior could lead to unintended side effects if not properly understood.

Given these distinctions, should we perhaps consider using DATETIME as the default column type for created_at and updated_at columns to avoid these limitations and potential complications?

[bulletproof-coding]

Hi. I always use in my migrations

   $table->dateTime('created_at');
   $table->dateTime('updated_at')->nullable();

And use a boot function in the model to populate them.

You are refering to
public $timestamps = true;

which populates them automatically with timestamps?

[shaedrich]

There's also a useCurrent() and useCurrentOnUpdate() for migrations.

[henzeb]

I think that by the time we get to the year 2038 they have increased that limit and you have upgraded to the latest database version. So that is not really an argument.

[henzeb]

In 2038? Even if, no one is going to update laravel 11 with code changesin 2038 just because some lousy company failed to upgrade things and still runs it. I upgrade several apps at work and it really is 1 hour work. Less maybe if you use tools like shift.

[iwasherefirst2]

I understand the point about upgrading, but it's not just about companies; individual developers, bloggers, or small-scale projects might not have the resources or necessity to update frequently. Upgrading, especially from versions as old as Laravel 5—which was released back in February 2015—can be particularly challenging due to dependency issues and significant changes in the framework. The discussion about Laravel updates is separate from the core database concerns we're addressing. Considering many Laravel 5 applications could be 10 years old or older and still in use without maintenance, changing the default from TIMESTAMP to DATETIME in Laravel could prevent future issues and is a straightforward modification that benefits all users. While 2038 might seem distant, it's prudent to plan ahead.

[macropay-solutions]

@iwasherefirst2 you can always use this free lib https://packagist.org/packages/macropay-solutions/laravel-crud-wizard-free and you have your issue solved because it needs datetime columns instead pf timestamp ones.

[iwasherefirst2]

There are other workarounds without even using the package. My question is not, how can I personally avoid this issue, but should we care about the default settings in laravel framework.

[bulletproof-coding]

@iwasherefirst2 The only default I see here is the artisan make:migration create_x_table generation that is probably adding

$table->timestamps();

instead of

$table->dateTime('created_at');
$table->dateTime('updated_at')->nullable();


$timestamps = true; // will set the value of the updated at to Date::now() that is a Carbon instance, so it should work both with timestamp and also date time.

[DarkGhostHunter]

The meaning of using timestamp() is to ensure the timestamp is UNIX Epoch. By being binary, not only is smaller, but also makes the datetime comparisons and ordering faster, AFAIK.

The datetime() should be reserved for historical dates (outside UNIX Epoch), and datetime that is not used in queries for comparison.

[macropay-solutions]

"datetime that is not used in queries for comparison." . You mean it should not be used? Even if it is slower, it is usable (and you have less head aches).

[iwasherefirst2]

Why should it make the comparison faster? Never heard that.

[bulletproof-coding]

@iwasherefirst2 int vs string comparison is faster

[browner12]

in MySQL both TIMESTAMP and DATETIME are stored internally as binary values. As of v5.6.4, TIMESTAMP is stored with 4 bytes, and DATETIME is stored with 5 bytes. Both can add up to 3 additional bytes for fractional second values.

this means performance differences should be negligible due to data type.

https://dev.mysql.com/doc/refman/8.4/en/storage-requirements.html

[lukeescude]

I absolutely agree with the OP, Laravel should switch to DateTime columns. Otherwise, all laravel-based apps will begin failing in 2038.

[henzeb]

The earliest moment to think about that is 2034.

[Sophist-UK]

I agree - SQL databases have 14 years to work out how to make timestamps work past 2038.

There is no need to panic for at least the next 10 years.

[DarkGhostHunter]

By the time this becomes a problem, most database engines will move to 64-bit timestamps, if not before.

PHP already uses 64-bit timestamps (if you run PHP 64-bit).

This discussion is pointless.

[lukeescude]

So Laravel should just stick with mediocrity in the hopes that "most database engines" will be updated, and that most applications will begin to use them? Nice. Instead, it should be a made a choice within the framework itself to give us devs the power to choose one over the other.

[bulletproof-coding]

@lukeescude you have that solution/choice. Use migrations with datetime instead of timestamps like i said here
#50578 (comment)
put $timestamps in your model(base model) as false and register a boot function for creating and updating events just like in @macropay-solutions lib to autopopulate them with date time values (or make mysql do it for you).

[lukeescude]

I suppose my point was to make it more automatic than that - But I definitely appreciate your solution, thanks!

[bulletproof-coding]

@lukeescude i never tried $timestamps = true; in model with datetime in db, maybe it works.