Breaking: #99937 - Utilize BIGINT database column type for datetime TCA

See forge#99937

Description

The TCA 'type' => 'datetime' attribute previously created integer signed types as per the new auto-creation of table columns, if not specified differently via ext_tables.sql or listed as an exception (starttime, endtime, tstamp, crdate).

A datetime field created without an exception would allow date ranges from 1901 to 2038. While that allows dates before 1970 (usual birthdays), sadly this field would "end" in 2038.

Because of this, the exceptions (starttime, endtime, tstamp, crdate) already are created as integer unsigned, which puts them from 1970 to 2106. Dates before 1970 are not needed, because you will not publish or create anything in the past, but maybe after 2038.

However, there are many usage cases where datetime TCA fields should have a much broader time span, or at least past 2038.

Now, all these fields are changed to use the bigint unsigned data type. This allows to define ranges far into the future and past. It uses a few more bytes within the database, for the benefit of being a unified solution that can apply to every usage case.

Impact

All extensions that previously declared datetime columns should remove the column definition from ext_tables.sql and utilize with the type bigint unsigned. This will allow to store timestamps after 2038.

In the rare case that your extension needed an even broader, non-negative range (bigint signed), you need to keep a custom column definition.

A future implementation may change from integer-based columns completely to a native datetime database field.

When executing the database compare utility, the column definitions for a few Core fields are changed and their storable range increases.

These fields are now able to hold a timestamp beyond 2038 (and also before 1970), and are now defined just like starttime, endtime, tstamp, crdate:

  • be_users.lastlogin

  • fe_users.lastlogin

  • pages.lastUpdated

  • pages.newUntil

  • sys_redirect.lasthiton

  • index_config.timer_next_indexing

  • tx_extensionmanager_domain_model_extension.last_updated

  • sys_workspace.publish_time

  • sys_file_metadata.content_creation_date

  • tt_content.date