How to perform 'between' in Arabic (hijri) calendar and save it as 'date' in MySQL?

Question

My question is actually my ultimate aim. So far, I am having 2 issues.

1. How to save arabic date as a 'date' in mysql? because, I have been converting Gregorian to Hijri and then, using preg_replace (php, for now, final is in Java) would change the numbers to arabic ascii hex... and then, save it in MySQL as varchar.

I know about collation cp1256_general_ci which allows us to store in arabic but, currently, for simplicity sake, I have put it aside. utf-8_general is doing fine too. So storing as varchar is not an issue, storing as 'date' is.

1. Performing queries on it. I thought the requirements would end there but, now the task is to perform queries like date 'between' xyz and pqr... Also, the constraint is to 'store it in arabic only'.

Any inputs are much appreciated.

Answer 1

SQL dates

I'd think about it like this: the server actually stores a date as a reference to a given day. How it does that is no concern of yours. When storing data to or reading data from such a date column, the server represents that date using a specific calendar, which is gregorian by convention. What I'm trying to say is, I wouldn't consider the stored value to be gregorian, although it may well be. I would rather consider the transferred date to be gregorian.

So the best solution, in my opinion, is accepting that fact and converting between Gregorian and Hijri on the application side. That way, you could use normal between checks on that.

Strings made up from numbers

If this is not possible, due to the fact that the locale-dependent conversion is too complicated, or because the mapping betwen Hijri and Grogorian is not unique or not known in advance, then you will have to store the date in some other form. Possible forms that come to my mind are either a varchar containing strings of the form YYYY-MM-DD, with the letters signifying digits. This scheme ensures that strings would compare like the dates they represent, so you could still use between on them. Turning these strings back into spelled out dates would still be tricky, though.

One or more numeric columns

So I would actually suggest you use three columns., each containing a number signifying a date, You could then use 10000*year + 100*month + day_of_month to obtain a single number for each day, which you could use for comparisons and between. On the other hand, you could use the function ELT in your queries to turn the number for the month back into a name. If performance is an issue, you might be better of storing just a single number, and splitting it into parts upon selection. In a Gregorian calendar, this would look like this:

CREATE TABLE tableName (myDate DECIMAL(8));

SELECT myDate DIV 10000 AS year,
       ELT((myDate DIV 100) MOD 100, "Jan", "Feb", …) AS month,
       myDate MOD 100 AS day_of_month
FROM tableName
WHERE myDate BETWEN 20121021 AND 20121023;

Compatibility and convenience

If you have to maintain read-only compatibility with code that expects a single textual date column, you could use a VIEW to provide that. For example for a German Gregorian DD. MMMM YYYY format, you could use code like this:

CREATE VIEW compatibleName AS
SELECT CONCAT(myDate MOD 100, ". ",
              ELT((myDate DIV 100) MOD 100, "Januar", "Februar", …), ". ",
              myDate DIV 10000) as dateString,
       * -- or explicitely name other columns needed for compatibility
FROM tableName

Decoding strings

If you need read-write access by another application using a string format, you'll have to parse those strings yourself. You can do that at the SQL level. Useful tools are SUBSTRING_INDEX to split the string into fields and FIELD to turn a month name into a number. You might want to add a trigger to the database which will ensure that your strings will always be in a valid format which you can decompose in this way. This question gives details on how to use triggers to enforce such checks.

Answer 2

you can store as date directly. I am usind normal date. my mysql functions are

DELIMITER $$

DROP FUNCTION IF EXISTS `kdmtest`.`IntPart` $$
CREATE FUNCTION `kdmtest`.`IntPart` (FloatNum float) RETURNS INT
BEGIN
if (floatNum< -0.0000001) then
     return ceil(floatNum-0.0000001);
else
  return floor(floatNum+0.0000001);
end if;
END $$

DELIMITER ;


DELIMITER $$

DROP FUNCTION IF EXISTS `kdmtest`.`Hicri` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `Hicri`(MiladiTarih date) RETURNS date
BEGIN
  declare d,m,y,jd,l,n,j int;
  set d=day(MiladiTarih);
  set m=month(MiladiTarih);
  set y=year(MiladiTarih);
  if ((y>1582) or((y=1582) and (m>10)) or ((y=1582) and (m=10) and (d>14))) then
    set jd=intpart((1461*(y+4800+intpart((m-14)/12)))/4)+intpart((367*(m-2-12*(intpart((m-14)/12))))/12)- intpart( (3* (intpart(  (y+4900+    intpart( (m-14)/12)     )/100)    )   ) /4)+d-32075;
  else
    set jd = 367*y-intpart((7*(y+5001+intpart((m-9)/7)))/4)+intpart((275*m)/9)+d+1729777;
  end if;
                    set l=jd-1948440+10632;
                    set n=intpart((l-1)/10631);
                    set l=l-10631*n+354;
                    set j=(intpart((10985-l)/5316))*(intpart((50*l)/17719))+(intpart(l/5670))*(intpart((43*l)/15238));
                    set l=l-(intpart((30-j)/15))*(intpart((17719*j)/50))-(intpart(j/16))*(intpart((15238*j)/43))+29;
                    set m=intpart((24*l)/709);
                    set d=l-intpart((709*m)/24);
                    set y=30*n+j-30;
return concat(y,'-',m,'-',d);
END $$

DELIMITER ;


DELIMITER $$

DROP FUNCTION IF EXISTS `kdmtest`.`Miladi` $$
CREATE FUNCTION `kdmtest`.`Miladi` (HicriTarih date) RETURNS date
BEGIN
  declare d,m,y,jd,l,n,j,i,k int;
  set d=day(HicriTarih);
    set m=month(HicriTarih);
    set y=year(HicriTarih);
    set jd=intPart((11*y+3)/30)+354*y+30*m-intPart((m-1)/2)+d+1948440-385;
  if (jd> 2299160 ) then
        set l=jd+68569;
        set n=intPart((4*l)/146097);
    set l=l-intPart((146097*n+3)/4);
        set i=intPart((4000*(l+1))/1461001);
    set l=l-intPart((1461*i)/4)+31;
    set j=intPart((80*l)/2447);
    set d=l-intPart((2447*j)/80);
        set l=intPart(j/11);
        set m=j+2-12*l;
        set y=100*(n-49)+i+l;
  else
    set j=jd+1402;
    set k=intPart((j-1)/1461);
    set l=j-1461*k;
    set n=intPart((l-1)/365)-intPart(l/1461);
    set i=l-365*n+30;
        set j=intPart((80*i)/2447);
        set d=i-intPart((2447*j)/80);
        set i=intPart(j/11);
        set m=j+2-12*i;
        set y=4*k+n+i-4716;
  end if;
  return concat(y,'-',m,'-',d);
END $$

DELIMITER ;