Storing non-Gregorian dates in Mysql's date type
Is it a good idea to store non-Gregorian dates in date type?
No. Aside that some valid date in one calendar system doesn't exist in another calendar, functions working on DATE
typed columns may not work properly. The matter is not just storing data, you need to process this data and for example compare them with CURDATE()
.
storing it as varchar or as timestamp or timestamp as Int or something else?
If you choose a proper formatting, use two digits for month and day and static number of digits for year, a character string type, CHAR
or VARCHAR
is fine. Comparing theme against each other is just a lexical comparison and you still can write your functions o procedures to extend functionality.
Choosing TIMESTAMP
or DATE
changes the question as former represents a specific time but latter represents a specific entry in calendar. If you want put time beside date they still differ in meaning. You should think about issues like daylight-saving time changes which cause some people prefer to put calendar entry (DATE) and some prefer seconds passed from 1 Jan 1970 (TIMESTAMP). e.g. there is two timestamps for 1393-06-30 23:30:00
in Hijri Shamsi calendar based on current Iran government laws.
You can store non-gregorian dates in an integer
field in the database.
Examples:
year:1396, month:11, day:17
=>13961117
year:1393, month:4, day:9
=>13930409
by using this, you can query rows to find a specific date and dates that are <=>
than a specific date, but unfortunately, you can't compare them against each other.