Description
Something I came across while looking into another matter so documenting here for later.
The datetime type (via doctrine-dbal) has different year ranges depending on the database in-use.
This may be problematic in a few spots1 and thus we may want to consider setting a reasonable upper/lower bound range when saving dates in the database so that we never end up with dates we can never save back/etc.
Here are the ranges each db says they support:
(ignoring time and day since the point is more about the year part)
| db | Low | High |
|---|---|---|
| MariaDB2 | 1000 CE | 9999 CE |
| PostgreSQL3 | 4713 BCE (!) | 294276 CE (!) |
| SQLite4 | 4714 BCE | ? |
| Oracle5 | 4712 BCE | 9999 CE |
| MySQL6 | 1000 CE | 9999 CE |
I currently propose 1000 CE to 9999 CE since it's basically the only common range.
Doctrine mapping matrix: https://www.doctrine-project.org/projects/doctrine-dbal/en/3.8/reference/types.html#mapping-matrix
Footnotes
-
Try setting a date to >9999 in Deck with a MariaDB/MySQL backend; I presume db conversion break too. Ironically, with a PostgreSQL backend we appear to detect >9999 as an invalid date, but I can't figure out where. Though we do enforce limits before it gets to the database in some places for other reasons (e.g. https://github.com/nextcloud/calendar/blob/427dc3aebc6b16d69269b26fce11ca353ace528f/src/store/davRestrictions.js#L12), but this isn't universal. ↩
-
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME ↩
-
Varies because it can use TEXT/REAL/INTEGER internally, but best documented is as stated ↩
-
https://docs.oracle.com/cd/B13789_01/server.101/b10759/sql_elements001.htm#SQLRF00200 ↩