Storing non-Gregorian dates in Mysql's date type

Question

I have a mysql table that has a column with date type.
I'm going to store non-Gregorian date in this column (i.e. Jalali date).
I tested this by using phpMyadmin and storing a Jalali date and no error happend.
Is it a good idea to store non-Gregorian dates in date type?
If not; which is better? storing it as varchar or as timestamp or timestamp as Int or something else?

Answer 1

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.

Answer 2

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.