Problem/Motivation

Core's datetime and daterange fields are always stored as varchar strings, not using native DB-specific date fields. This is much more portable and simple, which is why it's in core this way.
See #2366213: Date field doesn't use database-native date storage
https://groups.drupal.org/node/221254#comment-730579
...

One of the only downsides of this is DB query performance. The cost can be considerable, especially on enterprise sites with a huge quantity of date-specific content.

Proposed resolution

Ideally, support alternate field storage for core datetime and daterange fields, so nothing else (widgets, formatters, config, etc) has to know that you've swapped out the storage.

If we're already doing separate / custom storage, perhaps we can also add an 'all-day' bit, unless #2734255: Support a per-instance "all-day" option for datetime and datetime range fields is going to fix that in core.
See also #3021557: Make date_all_day a set of widgets and formatters for core's datetime_range field type

TBD @todo

Remaining tasks

  1. Design a solution
  2. Implement it
  3. Write tests
  4. Verify tests, check portability
  5. Write/update docs
  6. Review
  7. Commit

User interface changes

TBD, hopefully none.

API changes

TBD. Definitely API additions, probably no breaks or changes.

Data model changes

TBD. New DB storage for datetime + daterange fields. @todo

Release notes snippet

@todo

Comments

dww created an issue. See original summary.

dww’s picture

dww
we/he/they
commented
Issue summary: View changes
Related issues: +#2734255: Support a per-instance "all-day" option for datetime and datetime range fields

Adding #2734255: Support a per-instance "all-day" option for datetime and datetime range fields to related issues and summary.

mpdonadio’s picture

mpdonadio
he/him
Primary language English
Location Philadelphia/PA/USA (UTC-5)
commented

Big +1 to this if it really is feasible, and it would also help solve the headaches of implicit UTC and not storing offset w/ the string (eg, the issue about the storage vs datatype mismatch).

I want to say I looked into this once, but I honestly don't recall (I don't seem to have a branch for it). Services support backend overrides, but I am not 100% positive for fields (default would be varchar, then backend specific ones).

I honestly wonder if a totally new FieldItem w/ specific backends culminating in a migration path would be the easier approach, too, and then could also get into core proper via the experimental path.

mrpauldriver’s picture

mrpauldriver commented

I know that @dpi also has an opinion on this (not sure if the same).

See https://www.drupal.org/project/date_recur/issues/3059140#comment-13140985

daffie’s picture

daffie commented

Big + 1

dww’s picture

dww
we/he/they
commented
Related issues: +#2657888: Add Date function support in DBTNG

I came upon #2657888: Add Date function support in DBTNG today while searching the core queue for something else. Wanted to leave it here as a related issue.

mandclu’s picture

mandclu commented

I would also upvote this. Datetime and Datetime Range fields convert their values to Drupal Date objects anyway, so if usage of these fields could be built around methods that use these objects instead of the underlying stored values, it shouldn't matter how they go into the database.

freelock’s picture

freelock
Location Seattle
commented

I also greatly prefer timestamps in the database. However, one big question: are we setting up for a Unix Y2K issue? I've already hit this on a D7 site scheduling out recurring events that end up running past 2038. That was one problem that the string-based date formats solved...

mandclu’s picture

mandclu commented

The 2038 problem is a valid concern. That said, Javascript and Postgres both natively handle dates as timestamps (and timestamps are also widely used in other parts of Drupal core, among numerous other places), so suffice it to say this is something that will be solved.

So far it seems the easiest solution is to move to 64-bit integers. Which I believe would still be less storage than storing dates and times as strings, but don't quote me on that.

freelock’s picture

freelock
Location Seattle
commented

Yes I'm sure it will eventually be solved -- but I'm saying we've started hitting it already, less than 20 years out. So I hate to switch to something with known issues we've already hit...

I like the idea of delegating the actual format to the database drivers, with a fallback to varchar...

mandclu’s picture

mandclu commented

To me the spirit of this discussion is to offer site builders choices in how the dates will be stored, and try to abstract away those differences, so developers wouldn't have to worry about how the data is stored, they could call a common method that would retrieve the values, probably returning Drupal Datetime objects, or some variant.

Under the hood, a site builder might be able to choose:
- storage as strings
- storage as 32-bit timestamps
- storage as 64-bit timestamps (we know these will be needed eventually)
- storage as db-native Datetime fields (also timestamps in Postgres AFAIK)
- others?

Ideally contrib modules could provide additional storage methods, that would work seamlessly with modules that use the common method to retrieve values.

mandclu’s picture

mandclu commented

One other observation: core datetime and datetime range fields already appear to return datetime objects, so changing how they're stored shouldn't actually impact anything that processes/modifies them after the values are returned.

Also, core's date formatter service expects dates to be passed in as timestamps, so all values are converted to that before being being formatted anyway.