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?

Urbain

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.

Gunpowder

Thanks; i finally managed to first convert & then store it as a gregorian date as i think this method gives me both mysql date calculations & correctness of the date itself – Urbain

@4r1y4n, that will raise problems later about how to for example group by month. – Interpol

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.

Dishtowel

Answer 3

Internal storage and binary interface of almost all database systems have nothing to do with calendars. you just store date (and possibly time) into database, providing no calendaring information. It's only a simple number of days, seconds or milliseconds past from a specific point in time (usually midnight 1970-01-01).

So you just need to provide an API abstraction of dates in your application layer. Everything else will work. You convert all your dates to Gregorian or Unix timestamp, and send queries to MySQL as usual.

Pidgin

Answer 4

Non Gregorian calendar still operate by Year, Month, Day and Time so it can be stored in 4 separate columns like:

CREATE TABLE `Calendar` (
    `Year` int,
    `Month` tinyint,
    `Day` tinyint,
    `Time` time
);

I make possible to store dates without conversion and permit group by Year and Month

Himyarite