Feature #1004
open
Dates before 4713 BC
Description
Dates before 4713 BC are problematic because they can't be saved as a PostgreSQL timestamp.
One solution would be to implement a parallel date system (e.g. using only years) but this would be way to cumbersome and error prone, we really like using the database for date operations.
So I ask in the PostgreSQL mailing list about it.
https://www.postgresql.org/message-id/ca438ff8331c4e109aa1b75a130948ac%40oeaw.ac.at
Updated by Alexander Watzinger over 3 years ago
- Description updated (diff)
One solution would be to implement a parallel date system (e.g. using only years) but this would be way to cumbersome and error prone, we really like using the database for date operations.
So I ask in the PostgreSQL mailing list about it: https://www.postgresql.org/message-id/ca438ff8331c4e109aa1b75a130948ac%40oeaw.ac.at
After it passed the moderation I got responses immediately. None of them saying they would implement it but still great it got taken seriously and got some feedback. Maybe Stefan or somebody else want to join this discussion?
Updated by Alexander Watzinger over 3 years ago
Great news, the people at the PostgreSQL mailing list (https://www.postgresql.org/message-id/ca438ff8331c4e109aa1b75a130948ac%40oeaw.ac.at) were very friendly and supporting. They are now discussing how this could be implemented and they will maybe do it in 2023. Anyway, it is very nice they take the time looking into it.
Updated by Nina Richards about 3 years ago
Is there any news about this issue? We have finds and sites to track within bITEM that are from 50.000 BC and it is not possible.
Updated by Alexander Watzinger about 3 years ago
- Status changed from Acknowledged to Assigned
- Assignee set to Stefan Eichert
I haven't heard anything new. Maybe Stefan, whom I just assigned it to, would like to post in the mailing list again.
Just to keep things going and to show our continued interest in this topic.
Updated by Alexander Watzinger over 1 year ago
- Assignee changed from Stefan Eichert to Bernhard Koschiček-Krombholz
Assigning to Bernhard to post a reminder/question in the PostgreSQl mailing list.
Updated by Bernhard Koschiček-Krombholz over 1 year ago
- Assignee changed from Bernhard Koschiček-Krombholz to Nina Richards
Thank you, Nina, for writing a new Mail. I assign this issue to you for further documentation. I hope they will answer soon.
Updated by Alexander Watzinger over 1 year ago
- Assignee changed from Nina Richards to Bernhard Koschiček-Krombholz
Updated by Bernhard Koschiček-Krombholz about 1 year ago
- Status changed from Assigned to Acknowledged
Updated by Bernhard Koschiček-Krombholz about 1 year ago
- Assignee deleted (
Bernhard Koschiček-Krombholz)
Updated by Stefan Eichert 15 days ago
In order to find a solution I see some possible ways. Please feel free to add more ideas.
1. We restructure our system and split year, month, day for every date as separate infos (int resp. smallint).
year INTEGER NOT NULL,
month SMALLINT CHECK (month BETWEEN 1 AND 12),
day SMALLINT CHECK (day BETWEEN 1 AND 31)
This allows for sorting, calculating etc. if we want be more detailed we could also add hours, mins, secs.
In the documentation we should also note that dates before 1582 are julian calendar and before -4713 are solar years
2. We leave everything as is and just add another field
yearbefore4713 INTEGER NOT NULL,
if a year before 4713bc is entered the value is saved here.
UI resp. backend however must handle checks and validation and not allow days or any other smaller units for dates before -4713 and save the value in the respective field
Also in the manual we should consider to document that every date before this is meant as solar year
For sorting these dates in bITEM or THANADOS I implemented functions that calculate days before present on the fly