Timestamps in Drupal come in two forms:
Unix timestamps (integer):
This type has been used in Drupal from the beginning. It's mostly used for base entity fields ('created' and 'changed').
Datetime strings (varchar):
This type was adopted from the Drupal 7 Date module.
There are a few issues with the current implementation:
This duality complicates DX (Developer Experience). It's not easy to compare different types in SQL expressions.
Neither of these types uses native DB types for storing datetime values.
Mocking system time in tests is a bit tricky. The TimeInterface from the Datetime component is not compatible with PSR-20, and Drupal does not provide any helpers for mocking system time.
Create a new field type for timestamps that utilizes native DB timestamp types and integrates with PSR-20.
I created a module → that can serve as PoC for the new implementation.
Active
Idea
I think this is better discussed in the core queue, moving to database system because using native db types is probably the hardest thing to change.
There have been a lot of efforts along these lines. Adding a bunch of (very) related issues.