RYN RYN - 1 month ago 8
MySQL Question

Storing non-Gregorian dates in Mysql's date type

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 Source

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.