migration table support for year 2038 and beyond

[inwoker]

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:

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

[xicond]

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

[terabytesoftw]

@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.

[inwoker]

@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:

1. for old projects the field apply_time will continue working in INT
2. for new projects the field apply_time would be created as a DATETIME field
3. 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

[mtangoo]

@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

[lubosdz]

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

[mtangoo]

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!

[inwoker]

@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:

1. 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.

2. 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.

[mtangoo]

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...

[inwoker]

@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

[mtangoo]

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

[inwoker]

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

1. 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.
2. What tech overhead? Describe what you mean

[rob006]

@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.

[inwoker]

@rob006

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