yii2 migration table support for year 2038 and beyond

Currently migration table uses unsigned INT for date in the field apply_time. Which will overflow in the year of 2038 and will break support.

Possible solutions:

change to DATETIME, imho the better solution
make it unsigned INT, which will extend support to the year 2106
make it (unsigned) BIG INT, which will also extend support to the year far beyond

Sep 15 '25 10:09 inwoker

I think v2.0 wont support that long until 2038 @terabytesoftw but maybe can support in v2.2, IMHO better with timestamp

Sep 15 '25 11:09 xicond

@xicond This should be resolved on the master branch without doing BC, if possible, as it is entirely possible to use that year currently, @inwoker you have time for a PR.

Sep 15 '25 11:09 terabytesoftw

@xicond TIMESTAMP is limited to the year of 2038 @terabytesoftw Do I understand it correctly that you want me to write code and submit it for PR? If it's that, I would suggest the following:

for old projects the field apply_time will continue working in INT
for new projects the field apply_time would be created as a DATETIME field
there would be a console command for migration of apply_time from INT to DATETIME

If that's okay plan, I'll start working on it

Sep 15 '25 13:09 inwoker

@inwoker using Bigint will make it to year 2262 which should be more than enough. And makes changes to code minimal. I suggest you go with that one

Sep 15 '25 14:09 mtangoo

Why not simply unsigned INT ? No BC break, valid way loong till you live :-) Bigint may not be compatible across various DB systems.

Sep 16 '25 07:09 lubosdz

Big integers are well supported in major databases. I just didn't check what are minimal versions for each database supported vs what Yii2 supports. I will find time and verify.

If versions required by Yii and the databases to support bigint conflicts, yours will be the way to go!

Sep 16 '25 08:09 mtangoo

@lubosdz @mtangoo My arguments for DATETIME

We don't know how our software will be used in the future and for how long. Maybe at some point it will become part of some embedded system and will work for thousands of years. And maybe it will die in 5 years.

Programmers of the past assumed their software will not last and we know at least two examples:

problem 2000, the programmers assumed their systems will never need more than two digits for a year in a date. But than their systems actually reached that date and millions were spent to alleviate it. There's whole wikipedia articles about it: https://en.wikipedia.org/wiki/Year_2000_problem And that's considering how much less computerised we were in the year of 2000 and prior years.
problem 2038: everyone assumed 2038 is a long time from now, we are yet to reach 2038, but we can already see there would be problems and millions will be spent

So, my argument is the following. If we're making changes to make our software more compatible with the future, we should make it compatible to the latest year possible on the current tech. And DATETIME seems much more futureproof than BIG INT and UNSIGNED INT.

The second argument: DATETIME is simply much more usable. I need only to glance at my table and I see the date and time it was applied. I don't need special queries to convert the date into human-readable format.

Sep 17 '25 03:09 inwoker

How is datetime future proof than unsigned/big int? Yii2 support won't go to like 50 years or something. And probably we won't be here anyway and we would be, we would be super old. So qhy bother with that? 😉

I still suggest we extend integer to bigger valued. Drop in replacement for existing tables no concern at all. Unless there a better argument that program that will work hundreds of years from now...

Sep 17 '25 04:09 mtangoo

@mtangoo I did some research. Yeah, you're right. BIG INT is more futureproof than DATETIME. At least in current versions of sql software. It goes much beyond 9999-12-31, the maximum date of a DATETIME As to 'why bother with that?' - I believe I already laid out why - to make our software as futureproof as possible. So to not make future generations fix it.

Anyway, since BIG INT is kinda more futureproof than DATETIME, I guess my only argument is usability. Personally I don't use INT for dates in my projects. It's just very unconvenient and unusable, but maybe it's only my preference. I prefer when dates are dates, numbers are numbers, strings are strings, etc. When types are used for what they actually store

Sep 17 '25 04:09 inwoker

Now let's talk userbility. How is that using datetime makesnit more usable than big integers? Is there anything unusable or that will have practical improvement if it uses datetime instead? Remember if changed, it will add some tech overhead on existing apps

Sep 17 '25 05:09 mtangoo

@mtangoo I already said how it's more usable. So do you use INT for date fields in your projects?

Yes, being able to look at which date the migration was applied is more usable than not being able to. Here's you practical improvement.
What tech overhead? Describe what you mean

Sep 17 '25 13:09 inwoker

@inwoker if you want to browse migration history, you have migrate/history console command for that. I don't think that convenience of browsing migrations history directly in DB should be a goal here.

Also, changing type of column of such crucial table from number to string is a big BC break and it may mess with existing tools and solutions. Changing type to bigint sounds like a much less problematic change.

Sep 17 '25 13:09 rob006

@rob006

there is migrate/history. But it doesn't affect that browsing migrations through DB would be more convenient if it's changed to DATETIME
what exact tools and solution will it break? Any examples?

Sep 17 '25 13:09 inwoker

2. what exact tools and solution will it break? Any examples?

migrate/history command would be a first "victim" of such change. I would guess that https://github.com/bizley/yii2-migration and similar extensions might also have problems.

Sep 17 '25 14:09 rob006

Yes, I would have to make adjustments for sure. While it would be a really happy surprise to see Yii 2 kicking in 2038, I'm against this change for now.

Sep 17 '25 14:09 bizley

I think that changing type from integer to bigint in createMigrationHistoryTable() is a no-brainer - it is very unlikely it would create any problems.

Sep 17 '25 14:09 rob006

@inwoker you can use Yii2 formatter to format it to your liking.

Do I use Int in my project? Yes I do. I prefer created_at as bigint storing unix timestamp. I can the format to whatever format I like

Sep 17 '25 14:09 mtangoo

@bizley you're against changing the field to BIG INT? Or only against changing it to DATETIME? As for yii2 existing in 2038, I'm pretty sure it will. I encounter projects on kohana, zend and laravel 5 in 2025. Yii2 itself is from 2015

@mtangoo Understood. In this case, it makes sense from your perpective

Sep 17 '25 20:09 inwoker

Against datetime.

Sep 17 '25 20:09 bizley