Skip to content

Instantly share code, notes, and snippets.

@omidp
Created November 13, 2014 08:29
Show Gist options
  • Save omidp/15b17b349e520b889612 to your computer and use it in GitHub Desktop.
Save omidp/15b17b349e520b889612 to your computer and use it in GitHub Desktop.
postgresql persian to gregorian function
select g2j(now())
CREATE OR REPLACE FUNCTION g2j(in_date timestamp with time zone)
RETURNS character varying AS
$BODY$
DECLARE
y smallint;
aday smallint;
amonth smallint;
ayear smallint;
value smallint;
a1 char(4);
b1 char(2);
c1 char(2);
Tday smallint;
Tmonth smallint;
Tyear smallint;
temp smallint;
CabisehYear smallint;
TMonthEnd smallint;
numdays int;
now_day timestamp without time zone;
a timestamp without time zone;
Const_Date timestamp without time zone;
BEGIN
Const_Date = cast('3/21/1921' as timestamp without time zone);
numdays = DATE_PART('day',in_date - Const_Date);
aday = 1;
amonth = 1;
ayear = 1300;
CabisehYear =cast((numdays / 1461) as int);
numdays = numdays - CabisehYear * 1461;
Tyear = cast((numdays / 365) as int);
If Tyear = 4 then
Tyear = Tyear - 1;
end if;
numdays = numdays - Tyear * 365;
Tmonth =cast((numdays / 31) as int);
If (Tmonth > 6) then
Tmonth = 6;
end if;
numdays = numdays - Tmonth * 31;
TMonthEnd = 0;
If (numdays >= 30 And Tmonth = 6 ) then
TMonthEnd =cast((numdays / 30) as int);
If TMonthEnd >= 5 then
TMonthEnd = 5;
end if;
numdays = numdays - TMonthEnd * 30;
End if;
Tmonth = (TMonthEnd + Tmonth);
Tday = numdays;
Tyear = (Tyear + CabisehYear * 4);
ayear = (ayear + Tyear);
amonth = amonth + Tmonth;
aday = aday + Tday;
a1 = ayear;
b1 = amonth;
c1 = aday;
If length(b1) = 1 then
b1 = '0' || b1;
end if;
If length(c1) = 1 then
c1 = '0' || c1;
end if;
return a1 || '-' || b1 || '-' || c1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION g2j(timestamp without time zone)
OWNER TO postgres;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment