TYPO3 Core

My 2 cents on this. This database change is doable with v12, but there's no need to backport such huge changes to older versions. By today, even v12 ELTS ends in 2029, leaving 9 years until we'll hit the 2038 bug.

I'd rather propose native DATETIME fields with v13 and deprecating the old int-based fields.

There are today some TYPO3 6 Versions online, if i remember it correctly.
I think, it is okay, to make the change to bigint in version 12.

1. which timezone is used for the field,
2. which calendar is used (chinese, hebrew, ....) and
3. which hour is used (The DATETIME-Field can mean two times in october, when the daylight saving time is reseted at that time. )

The Timestamp is defined in seconds for the timezone UTC. The timestamp is simple and easily converted with the Datetime-Object or with the IntlDateFormatter-Object to the calendar or timezone, you wish. (see my viewhelper <timer:format.calendarDate> in my extension `timer`)

I think, the unix-timestamp (in a bigint-field) is better than a DATETIME-field.

Dieter Porth wrote in #note-2:

The Timestamp is defined in seconds for the timezone UTC. The timestamp is simple and easily converted with the Datetime-Object or with the IntlDateFormatter-Object to the calendar or timezone, you wish.

Even timestamps have edge cases to consider, you might find this article interesting: https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/

Changing it from INT to BIGINT would be a handy patch for the time being. Implementing DATETIME may cause a much larger refactoring I'd assume, and may need check how other DB backends handle these.

I'll try to prepare a patch (targetting v13 of course, because of the possible breaking impact) for getting BIGINT off the ground, because: It not only affects FUTURE timestamps, but also any other date field for i.e. sys_file_metadata.

We are working on a site where historical images are presented, and want to use content_creation_date for that. This only goes back to 1901 on INT columns, but we need of course until like... 1400BC. BIGINT (signed) would allow that. ;-)

Actually: tstamp and some other INT UNSIGNED values allow to go up to the year 2106 and not only 2038. In the core these are:

starttime, endtime, tstamp, crdate

So that means we'd "only" need a way to define any TCA datetime to be either INT UNSIGNED instead of INT SIGNED.

And for some fields, support BIGINT (but let's not add extra storage space for EVERY field to BIGINT, because we're here to save the environment, right?)

Patch incoming.

Patch set 2 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 3 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 4 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 5 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 6 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 7 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 8 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 9 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 10 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 11 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 12 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 13 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 14 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 15 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 16 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668

Patch set 17 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/80668