Convert Gregorian Date to Jalali (Shamsi - Persian) Date in Postgresql

convert_gregorian_date_to_jalali_date.md

Add Persian Calendar Functions to a Postgres Database

Here's the Ruby on Rails migration implementation:

class AddPersianCalendarFunctionsToDb < ActiveRecord::Migration[4.2]
  def up
    execute(
      <<-EOSQL
      DROP FUNCTION IF EXISTS startOfJalaaliMonth(timestamp);
      DROP FUNCTION IF EXISTS j2d(integer, integer, integer);
      DROP FUNCTION IF EXISTS d2j(integer);
      DROP FUNCTION IF EXISTS j2g(integer, integer, integer);
      DROP FUNCTION IF EXISTS jalCal(integer);
      DROP TYPE IF EXISTS jalCalOutputType;
      DROP TYPE IF EXISTS d2jOutputType;

      -- perCalOutputType
      -- Output of per_cal function as composite type of:
      --   leap: number of years since the last leap year (0 to 4)
      --   gy: Gregorian year of the beginning of Jalaali year
      --   march: the March day of Farvardin the 1st (1st day of jy)
      CREATE TYPE perCalOutputType AS (
        leap integer,
        gy integer,
        march integer
      );

      -- per_cal
      -- This function determines if the Persian year is
      -- leap (366-day long) or is the common year (365 days), and
      -- finds the day in March (Gregorian calendar) of the first
      -- day of the Persian year (jy).

      -- @param py Persian calendar year (1210 to 1635)
      -- @return PerCalOutputType
      -- @see: http://www.astro.uni.torun.pl/~kb/Papers/EMP/PersianC-EMP.htm
      -- @see: https://github.com/jalaali/jalaali-js/blob/master/index.js

      CREATE OR REPLACE FUNCTION per_cal(py integer)
        RETURNS perCalOutputType AS $$
      DECLARE
        gy integer;
        leapJ integer := 294;
        n integer;
        leapG integer;
        leap integer;
        march integer;
        jump integer := 425;
        result perCalOutputType;
      BEGIN
        gy := py + 621;
        n := py - 1210;

        -- Find the number of leap years from AD 621 to the beginning
        -- of the current Persian year in the Persian calendar.
        leapJ := leapJ + n / 33 * 8 + (MOD(n, 33) + 3) / 4;
        if (mod(jump, 33) = 4 AND jump - n = 4) then
           leapJ := leapJ + 1;
        end if;

        -- And the same in the Gregorian calendar (until the year gy).
        leapG := gy / 4 - ((gy / 100 + 1) * 3) / 4 - 150;

        -- Determine the Gregorian date of Farvardin the 1st.
        march := 20 + leapJ - leapG;

        -- Find how many years have passed since the last leap year.
        if (jump - n < 6) then
           n = n - jump + div(jump + 4, 33) * 33;
        end if;
        leap = mod(mod(n + 1, 33) - 1, 4);
        if (leap = -1) then
           leap = 4;
        end if;

        select leap, gy, march into result;
        -- select gy, 1, 2 into result;
        RETURN result;
        END;
      $$ LANGUAGE plpgsql STABLE;

      -- p2j
      -- Converts a date of the Persian calendar to the Julian Day number.
      -- @param py Persian year (1210 to 1635)
      -- @param pm Persian month (1 to 12)
      -- @param pd Persian day (1 to 29/31)
      -- @return Julian Day number
      CREATE OR REPLACE FUNCTION p2j(py integer, pm integer, pd integer)
        RETURNS integer AS $$
      DECLARE
        r perCalOutputType;
      BEGIN
        r :=  per_cal(py);
        RETURN to_char((r.gy||'-3-'||r.march)::date, 'J')::int + (pm - 1) * 31 - (pm / 7) * (pm - 7) + pd - 1;
      END;
      $$ LANGUAGE plpgsql STABLE;

      -- p2g
      -- Converts Persian date to Gregorian Calendar
      -- @param py Persian year (1210 to 1635)
      -- @param pm Persian month (1 to 12)
      -- @param pd Persian day (1 to 29/31)
      -- @return Gregorian Date

      CREATE OR REPLACE FUNCTION p2g(py integer, pm integer, pd integer)
        RETURNS date AS $$
      BEGIN
        RETURN ('J'||p2j(py, pm, pd))::date;
      END;
      $$ LANGUAGE plpgsql;

      -- persianDate
      -- Output of j2p function as composite type of:
      --   year: Jalaali year (1 to 3100)
      --   month: Jalaali month (1 to 12)
      --   day: Jalaali day (1 to 29/31)
      CREATE TYPE persianDate AS (
        year integer,
        month integer,
        day integer
      );

      CREATE OR REPLACE FUNCTION persianDateToString(p persianDate)
        RETURNS varchar(20) AS $$
      BEGIN
        RETURN p.year || '-' || p.month || '-' || p.day;
      END;
      $$ language plpgsql STABLE;

      -- j2p
      -- Converts the Julian Day number to a date in the Jalaali calendar.
      -- @param jdn Julian Day number
      -- @return persianDate
      CREATE OR REPLACE FUNCTION j2p(jdn integer) RETURNS persianDate AS $$
      DECLARE
        rr perCalOutputType;
        jdn1f integer;
        py integer;
        pd integer;
        pm integer;
        k integer;
        gy integer;
        result persianDate;
      BEGIN
        gy := extract(year from ('J'||jdn)::date)::int; -- Calculate Gregorian year (gy)
        py := gy - 621;

        rr := per_cal(py);
        jdn1f = to_char((gy||'-03-'||rr.march)::date, 'J')::int;
        -- Find number of days that passed since 1 Farvardin.
        k := jdn - jdn1f;
        if (k >= 0) then
           if (k <= 185) then
              -- The first 6 months.
              pm := 1 + div(k, 31);
              pd := mod(k, 31) + 1;
              select py, pm, pd into result;
              return result;
           else
           -- The remaining months.
           k := k - 186;
           end if;
        else
           -- Previous Jalaali year.
           py := py - 1;
           k := k + 179;
           if (rr.leap = 1) then
              k := k + 1;
           end if;
        end if;
        pm := 7 + k / 30;
        pd := mod(k, 30) + 1;
        select py, pm, pd into result;
        return result;
      END;
      $$ LANGUAGE plpgsql STABLE;

      -- convert to persian date
      CREATE OR REPLACE FUNCTION g2p(d date)
        RETURNS persianDate AS $$
      BEGIN
        RETURN (j2p(to_char(d, 'J')::int));
      END;
      $$ language plpgsql STABLE;

      -- starting date of current week in Persian Calendar
      CREATE OR REPLACE FUNCTION start_of_persian_week(d date)
        RETURNS date AS $$
      BEGIN
        RETURN d -
          mod(extract(dow from d)::int+1, 7)*'1 day'::interval;
      END;
      $$ language plpgsql STABLE;

      -- starting date of current week in Persian Calendar
      CREATE OR REPLACE FUNCTION current_persian_week()
        RETURNS date AS $$
      BEGIN
        RETURN current_date -
          mod(extract(dow from current_date)::int+1, 7)*'1 day'::interval;
      END;
      $$ language plpgsql STABLE;

      -- starting date of last week in Persian Calendar
      CREATE OR REPLACE FUNCTION last_persian_week()
        RETURNS date AS $$
      BEGIN
        RETURN current_persian_week() - '7 days'::interval;
      END;
      $$ language plpgsql STABLE;

      -- starting date of month of a given date in Persian Calendar
      CREATE OR REPLACE FUNCTION start_of_persian_month(d date)
        RETURNS date AS $$
      DECLARE
        pd integer;
        r persianDate;
      BEGIN
        pd := to_char(d, 'J')::int;
        r := j2p(pd);
        pd := p2j(r.year, r.month, 1);
        RETURN ('J'||pd)::date;
      END;
      $$ LANGUAGE plpgsql STABLE;

      -- starting date of current month in Persian Calendar
      CREATE OR REPLACE FUNCTION current_persian_month()
        RETURNS date AS $$
      BEGIN
        RETURN start_of_persian_month(current_date);
      END;
      $$ language plpgsql STABLE;

      -- starting date of n months before in Persian Calendar
      CREATE OR REPLACE FUNCTION n_months_before(d date, n integer)
        RETURNS date AS $$
      DECLARE
        p persianDate;
        months integer;
      BEGIN
        p := g2p(d);
        months := p.year * 12 + (p.month-1) - n;
        RETURN p2g(months/12, mod(months,12)+1, p.day);
      END;
      $$ language plpgsql STABLE;

      -- starting date of last month in Persian Calendar
      CREATE OR REPLACE FUNCTION last_persian_month()
        RETURNS date AS $$
      BEGIN
        RETURN start_of_persian_month(n_months_before(current_date, 1));
      END;
      $$ language plpgsql STABLE;

      CREATE CAST (persianDate AS text) WITH FUNCTION persianDateToString(persianDate) AS ASSIGNMENT;
      CREATE CAST (date AS persiandate) WITH FUNCTION g2p(date) AS ASSIGNMENT;

      EOSQL
    )
  end

  def down
    execute (
      <<-EOSQL
      DROP CAST (date AS persiandate);
      DROP CAST (persianDate AS text);
      DROP FUNCTION IF EXISTS last_persian_month();
      DROP FUNCTION IF EXISTS n_months_before(date, integer);
      DROP FUNCTION IF EXISTS current_persian_month();
      DROP FUNCTION IF EXISTS start_of_persian_month(date);

      DROP FUNCTION IF EXISTS last_persian_week();
      DROP FUNCTION IF EXISTS current_persian_week();
      DROP FUNCTION IF EXISTS start_of_persian_week(date);

      DROP FUNCTION IF EXISTS g2p(date);
      DROP FUNCTION IF EXISTS j2p(integer);
      DROP FUNCTION IF EXISTS p2j(integer, integer, integer);
      DROP FUNCTION IF EXISTS p2g(integer, integer, integer);
      DROP FUNCTION IF EXISTS per_cal(integer);

      DROP FUNCTION IF EXISTS persianDateToString(persianDate);
      DROP TYPE IF EXISTS perCalOutputType CASCADE;
      DROP TYPE IF EXISTS persianDate CASCADE;

      EOSQL
    )
  end
end

Usage

Schema	Name	Result data type	Argument data types	Type
public	current_persian_month	date		func
public	current_persian_week	date		func
public	g2p	persiandate	d date	func
public	j2p	persiandate	jdn integer	func
public	last_persian_month	date		func
public	last_persian_week	date		func
public	n_months_before	date	d date, n integer	func
public	p2g	date	py integer, pm integer, pd integer	func
public	p2j	integer	py integer, pm integer, pd integer	func
public	per_cal	percaloutputtype	py integer	func
public	persiandatetostring	character varying	p persiandate	func
public	start_of_persian_month	date	d date	func
public	start_of_persian_week	date	d date	func

TLDR;

Migrate the sql file. Use it like so:

SELECT g2p(DATE(created_at)) as created_at_jalali from folan_table limit 5;

Result:

created_at_jalali     
-------------
 (1398,8,22)
 (1398,8,22)
 (1398,8,22)
 (1398,8,22)
 (1398,8,22)
(5 rows)

Here's the laravel migration for it (the query is the same):

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        DB::unprepared(<<<SQL
      DROP FUNCTION IF EXISTS startOfJalaaliMonth(timestamp);
      DROP FUNCTION IF EXISTS j2d(integer, integer, integer);
      DROP FUNCTION IF EXISTS d2j(integer);
      DROP FUNCTION IF EXISTS j2g(integer, integer, integer);
      DROP FUNCTION IF EXISTS jalCal(integer);
      DROP TYPE IF EXISTS jalCalOutputType;
      DROP TYPE IF EXISTS d2jOutputType;

      -- perCalOutputType
      -- Output of per_cal function as composite type of:
      --   leap: number of years since the last leap year (0 to 4)
      --   gy: Gregorian year of the beginning of Jalaali year
      --   march: the March day of Farvardin the 1st (1st day of jy)
      CREATE TYPE perCalOutputType AS (
        leap integer,
        gy integer,
        march integer
      );

      -- per_cal
      -- This function determines if the Persian year is
      -- leap (366-day long) or is the common year (365 days), and
      -- finds the day in March (Gregorian calendar) of the first
      -- day of the Persian year (jy).

      -- @param py Persian calendar year (1210 to 1635)
      -- @return PerCalOutputType
      -- @see: https://www.astro.uni.torun.pl/~kb/Papers/EMP/PersianC-EMP.htm
      -- @see: https://github.com/jalaali/jalaali-js/blob/master/index.js

      CREATE OR REPLACE FUNCTION per_cal(py integer)
        RETURNS perCalOutputType AS $$
      DECLARE
        gy integer;
        leapJ integer := 294;
        n integer;
        leapG integer;
        leap integer;
        march integer;
        jump integer := 425;
        result perCalOutputType;
      BEGIN
        gy := py + 621;
        n := py - 1210;

        -- Find the number of leap years from AD 621 to the beginning
        -- of the current Persian year in the Persian calendar.
        leapJ := leapJ + n / 33 * 8 + (MOD(n, 33) + 3) / 4;
        if (mod(jump, 33) = 4 AND jump - n = 4) then
           leapJ := leapJ + 1;
        end if;

        -- And the same in the Gregorian calendar (until the year gy).
        leapG := gy / 4 - ((gy / 100 + 1) * 3) / 4 - 150;

        -- Determine the Gregorian date of Farvardin the 1st.
        march := 20 + leapJ - leapG;

        -- Find how many years have passed since the last leap year.
        if (jump - n < 6) then
           n = n - jump + div(jump + 4, 33) * 33;
        end if;
        leap = mod(mod(n + 1, 33) - 1, 4);
        if (leap = -1) then
           leap = 4;
        end if;

        select leap, gy, march into result;
        -- select gy, 1, 2 into result;
        RETURN result;
        END;
      $$ LANGUAGE plpgsql STABLE;

      -- p2j
      -- Converts a date of the Persian calendar to the Julian Day number.
      -- @param py Persian year (1210 to 1635)
      -- @param pm Persian month (1 to 12)
      -- @param pd Persian day (1 to 29/31)
      -- @return Julian Day number
      CREATE OR REPLACE FUNCTION p2j(py integer, pm integer, pd integer)
        RETURNS integer AS $$
      DECLARE
        r perCalOutputType;
      BEGIN
        r :=  per_cal(py);
        RETURN to_char((r.gy||'-3-'||r.march)::date, 'J')::int + (pm - 1) * 31 - (pm / 7) * (pm - 7) + pd - 1;
      END;
      $$ LANGUAGE plpgsql STABLE;

      -- p2g
      -- Converts Persian date to Gregorian Calendar
      -- @param py Persian year (1210 to 1635)
      -- @param pm Persian month (1 to 12)
      -- @param pd Persian day (1 to 29/31)
      -- @return Gregorian Date

      CREATE OR REPLACE FUNCTION p2g(py integer, pm integer, pd integer)
        RETURNS date AS $$
      BEGIN
        RETURN ('J'||p2j(py, pm, pd))::date;
      END;
      $$ LANGUAGE plpgsql;

      -- persianDate
      -- Output of j2p function as composite type of:
      --   year: Jalaali year (1 to 3100)
      --   month: Jalaali month (1 to 12)
      --   day: Jalaali day (1 to 29/31)
      CREATE TYPE persianDate AS (
        year integer,
        month integer,
        day integer
      );

      CREATE OR REPLACE FUNCTION persianDateToString(p persianDate)
        RETURNS varchar(20) AS $$
      BEGIN
        RETURN p.year || '-' || p.month || '-' || p.day;
      END;
      $$ language plpgsql STABLE;

      -- j2p
      -- Converts the Julian Day number to a date in the Jalaali calendar.
      -- @param jdn Julian Day number
      -- @return persianDate
      CREATE OR REPLACE FUNCTION j2p(jdn integer) RETURNS persianDate AS $$
      DECLARE
        rr perCalOutputType;
        jdn1f integer;
        py integer;
        pd integer;
        pm integer;
        k integer;
        gy integer;
        result persianDate;
      BEGIN
        gy := extract(year from ('J'||jdn)::date)::int; -- Calculate Gregorian year (gy)
        py := gy - 621;

        rr := per_cal(py);
        jdn1f = to_char((gy||'-03-'||rr.march)::date, 'J')::int;
        -- Find number of days that passed since 1 Farvardin.
        k := jdn - jdn1f;
        if (k >= 0) then
           if (k <= 185) then
              -- The first 6 months.
              pm := 1 + div(k, 31);
              pd := mod(k, 31) + 1;
              select py, pm, pd into result;
              return result;
           else
           -- The remaining months.
           k := k - 186;
           end if;
        else
           -- Previous Jalaali year.
           py := py - 1;
           k := k + 179;
           if (rr.leap = 1) then
              k := k + 1;
           end if;
        end if;
        pm := 7 + k / 30;
        pd := mod(k, 30) + 1;
        select py, pm, pd into result;
        return result;
      END;
      $$ LANGUAGE plpgsql STABLE;

      -- convert to persian date
      CREATE OR REPLACE FUNCTION g2p(d date)
        RETURNS persianDate AS $$
      BEGIN
        RETURN (j2p(to_char(d, 'J')::int));
      END;
      $$ language plpgsql STABLE;

      -- starting date of current week in Persian Calendar
      CREATE OR REPLACE FUNCTION start_of_persian_week(d date)
        RETURNS date AS $$
      BEGIN
        RETURN d -
          mod(extract(dow from d)::int+1, 7)*'1 day'::interval;
      END;
      $$ language plpgsql STABLE;

      -- starting date of current week in Persian Calendar
      CREATE OR REPLACE FUNCTION current_persian_week()
        RETURNS date AS $$
      BEGIN
        RETURN current_date -
          mod(extract(dow from current_date)::int+1, 7)*'1 day'::interval;
      END;
      $$ language plpgsql STABLE;

      -- starting date of last week in Persian Calendar
      CREATE OR REPLACE FUNCTION last_persian_week()
        RETURNS date AS $$
      BEGIN
        RETURN current_persian_week() - '7 days'::interval;
      END;
      $$ language plpgsql STABLE;

      -- starting date of month of a given date in Persian Calendar
      CREATE OR REPLACE FUNCTION start_of_persian_month(d date)
        RETURNS date AS $$
      DECLARE
        pd integer;
        r persianDate;
      BEGIN
        pd := to_char(d, 'J')::int;
        r := j2p(pd);
        pd := p2j(r.year, r.month, 1);
        RETURN ('J'||pd)::date;
      END;
      $$ LANGUAGE plpgsql STABLE;

      -- starting date of current month in Persian Calendar
      CREATE OR REPLACE FUNCTION current_persian_month()
        RETURNS date AS $$
      BEGIN
        RETURN start_of_persian_month(current_date);
      END;
      $$ language plpgsql STABLE;

      -- starting date of n months before in Persian Calendar
      CREATE OR REPLACE FUNCTION n_months_before(d date, n integer)
        RETURNS date AS $$
      DECLARE
        p persianDate;
        months integer;
      BEGIN
        p := g2p(d);
        months := p.year * 12 + (p.month-1) - n;
        RETURN p2g(months/12, mod(months,12)+1, p.day);
      END;
      $$ language plpgsql STABLE;

      -- starting date of last month in Persian Calendar
      CREATE OR REPLACE FUNCTION last_persian_month()
        RETURNS date AS $$
      BEGIN
        RETURN start_of_persian_month(n_months_before(current_date, 1));
      END;
      $$ language plpgsql STABLE;

      CREATE CAST (persianDate AS text) WITH FUNCTION persianDateToString(persianDate) AS ASSIGNMENT;
      CREATE CAST (date AS persiandate) WITH FUNCTION g2p(date) AS ASSIGNMENT;
SQL
        );
    }

    public function down(): void
    {
        DB::unprepared(<<<SQL
      DROP CAST (date AS persiandate);
      DROP CAST (persianDate AS text);
      DROP FUNCTION IF EXISTS last_persian_month();
      DROP FUNCTION IF EXISTS n_months_before(date, integer);
      DROP FUNCTION IF EXISTS current_persian_month();
      DROP FUNCTION IF EXISTS start_of_persian_month(date);

      DROP FUNCTION IF EXISTS last_persian_week();
      DROP FUNCTION IF EXISTS current_persian_week();
      DROP FUNCTION IF EXISTS start_of_persian_week(date);

      DROP FUNCTION IF EXISTS g2p(date);
      DROP FUNCTION IF EXISTS j2p(integer);
      DROP FUNCTION IF EXISTS p2j(integer, integer, integer);
      DROP FUNCTION IF EXISTS p2g(integer, integer, integer);
      DROP FUNCTION IF EXISTS per_cal(integer);

      DROP FUNCTION IF EXISTS persianDateToString(persianDate);
      DROP TYPE IF EXISTS perCalOutputType CASCADE;
      DROP TYPE IF EXISTS persianDate CASCADE;
SQL
        );
    }
};

Here is the Django Migration

# Generated by Django 2.2.28 on 2024-10-07 12:40

from django.db import migrations


class Migration(migrations.Migration):
    dependencies = []

    operations = [
        migrations.RunSQL(
            """
DROP FUNCTION IF EXISTS startOfJalaaliMonth(timestamp);
DROP FUNCTION IF EXISTS j2d(integer, integer, integer);
DROP FUNCTION IF EXISTS d2j(integer);
DROP FUNCTION IF EXISTS j2g(integer, integer, integer);
DROP FUNCTION IF EXISTS jalCal(integer);
DROP TYPE IF EXISTS jalCalOutputType;
DROP TYPE IF EXISTS d2jOutputType;
DROP TYPE IF EXISTS perCalOutputType CASCADE;
DROP TYPE IF EXISTS persianDate CASCADE;

      -- perCalOutputType
      -- Output of per_cal function as composite type of:
      --   leap: number of years since the last leap year (0 to 4)
      --   gy: Gregorian year of the beginning of Jalaali year
      --   march: the March day of Farvardin the 1st (1st day of jy)
      CREATE TYPE perCalOutputType AS (
        leap integer,
        gy integer,
        march integer
      );

      -- per_cal
      -- This function determines if the Persian year is
      -- leap (366-day long) or is the common year (365 days), and
      -- finds the day in March (Gregorian calendar) of the first
      -- day of the Persian year (jy).

      -- @param py Persian calendar year (1210 to 1635)
      -- @return PerCalOutputType
      -- @see: http://www.astro.uni.torun.pl/~kb/Papers/EMP/PersianC-EMP.htm
      -- @see: https://github.com/jalaali/jalaali-js/blob/master/index.js

      CREATE OR REPLACE FUNCTION per_cal(py integer)
        RETURNS perCalOutputType AS $$
      DECLARE
        gy integer;
        leapJ integer := 294;
        n integer;
        leapG integer;
        leap integer;
        march integer;
        jump integer := 425;
        result perCalOutputType;
      BEGIN
        gy := py + 621;
        n := py - 1210;

        -- Find the number of leap years from AD 621 to the beginning
        -- of the current Persian year in the Persian calendar.
        leapJ := leapJ + n / 33 * 8 + (MOD(n, 33) + 3) / 4;
        if (mod(jump, 33) = 4 AND jump - n = 4) then
           leapJ := leapJ + 1;
        end if;

        -- And the same in the Gregorian calendar (until the year gy).
        leapG := gy / 4 - ((gy / 100 + 1) * 3) / 4 - 150;

        -- Determine the Gregorian date of Farvardin the 1st.
        march := 20 + leapJ - leapG;

        -- Find how many years have passed since the last leap year.
        if (jump - n < 6) then
           n = n - jump + div(jump + 4, 33) * 33;
        end if;
        leap = mod(mod(n + 1, 33) - 1, 4);
        if (leap = -1) then
           leap = 4;
        end if;

        select leap, gy, march into result;
        -- select gy, 1, 2 into result;
        RETURN result;
        END;
      $$ LANGUAGE plpgsql STABLE;

      -- p2j
      -- Converts a date of the Persian calendar to the Julian Day number.
      -- @param py Persian year (1210 to 1635)
      -- @param pm Persian month (1 to 12)
      -- @param pd Persian day (1 to 29/31)
      -- @return Julian Day number
      CREATE OR REPLACE FUNCTION p2j(py integer, pm integer, pd integer)
        RETURNS integer AS $$
      DECLARE
        r perCalOutputType;
      BEGIN
        r :=  per_cal(py);
        RETURN to_char((r.gy||'-3-'||r.march)::date, 'J')::int + (pm - 1) * 31 - (pm / 7) * (pm - 7) + pd - 1;
      END;
      $$ LANGUAGE plpgsql STABLE;

      -- p2g
      -- Converts Persian date to Gregorian Calendar
      -- @param py Persian year (1210 to 1635)
      -- @param pm Persian month (1 to 12)
      -- @param pd Persian day (1 to 29/31)
      -- @return Gregorian Date

      CREATE OR REPLACE FUNCTION p2g(py integer, pm integer, pd integer)
        RETURNS date AS $$
      BEGIN
        RETURN ('J'||p2j(py, pm, pd))::date;
      END;
      $$ LANGUAGE plpgsql;

      -- persianDate
      -- Output of j2p function as composite type of:
      --   year: Jalaali year (1 to 3100)
      --   month: Jalaali month (1 to 12)
      --   day: Jalaali day (1 to 29/31)
      CREATE TYPE persianDate AS (
        year integer,
        month integer,
        day integer
      );

      CREATE OR REPLACE FUNCTION persianDateToString(p persianDate)
        RETURNS varchar(20) AS $$
      BEGIN
        RETURN p.year || '-' || p.month || '-' || p.day;
      END;
      $$ language plpgsql STABLE;

      -- j2p
      -- Converts the Julian Day number to a date in the Jalaali calendar.
      -- @param jdn Julian Day number
      -- @return persianDate
      CREATE OR REPLACE FUNCTION j2p(jdn integer) RETURNS persianDate AS $$
      DECLARE
        rr perCalOutputType;
        jdn1f integer;
        py integer;
        pd integer;
        pm integer;
        k integer;
        gy integer;
        result persianDate;
      BEGIN
        gy := extract(year from ('J'||jdn)::date)::int; -- Calculate Gregorian year (gy)
        py := gy - 621;

        rr := per_cal(py);
        jdn1f = to_char((gy||'-03-'||rr.march)::date, 'J')::int;
        -- Find number of days that passed since 1 Farvardin.
        k := jdn - jdn1f;
        if (k >= 0) then
           if (k <= 185) then
              -- The first 6 months.
              pm := 1 + div(k, 31);
              pd := mod(k, 31) + 1;
              select py, pm, pd into result;
              return result;
           else
           -- The remaining months.
           k := k - 186;
           end if;
        else
           -- Previous Jalaali year.
           py := py - 1;
           k := k + 179;
           if (rr.leap = 1) then
              k := k + 1;
           end if;
        end if;
        pm := 7 + k / 30;
        pd := mod(k, 30) + 1;
        select py, pm, pd into result;
        return result;
      END;
      $$ LANGUAGE plpgsql STABLE;

      -- convert to persian date
      CREATE OR REPLACE FUNCTION g2p(d date)
        RETURNS persianDate AS $$
      BEGIN
        RETURN (j2p(to_char(d, 'J')::int));
      END;
      $$ language plpgsql STABLE;

      -- starting date of current week in Persian Calendar
      CREATE OR REPLACE FUNCTION start_of_persian_week(d date)
        RETURNS date AS $$
      BEGIN
        RETURN d -
          mod(extract(dow from d)::int+1, 7)*'1 day'::interval;
      END;
      $$ language plpgsql STABLE;

      -- starting date of current week in Persian Calendar
      CREATE OR REPLACE FUNCTION current_persian_week()
        RETURNS date AS $$
      BEGIN
        RETURN current_date -
          mod(extract(dow from current_date)::int+1, 7)*'1 day'::interval;
      END;
      $$ language plpgsql STABLE;

      -- starting date of last week in Persian Calendar
      CREATE OR REPLACE FUNCTION last_persian_week()
        RETURNS date AS $$
      BEGIN
        RETURN current_persian_week() - '7 days'::interval;
      END;
      $$ language plpgsql STABLE;

      -- starting date of month of a given date in Persian Calendar
      CREATE OR REPLACE FUNCTION start_of_persian_month(d date)
        RETURNS date AS $$
      DECLARE
        pd integer;
        r persianDate;
      BEGIN
        pd := to_char(d, 'J')::int;
        r := j2p(pd);
        pd := p2j(r.year, r.month, 1);
        RETURN ('J'||pd)::date;
      END;
      $$ LANGUAGE plpgsql STABLE;

      -- starting date of current month in Persian Calendar
      CREATE OR REPLACE FUNCTION current_persian_month()
        RETURNS date AS $$
      BEGIN
        RETURN start_of_persian_month(current_date);
      END;
      $$ language plpgsql STABLE;

      -- starting date of n months before in Persian Calendar
      CREATE OR REPLACE FUNCTION n_months_before(d date, n integer)
        RETURNS date AS $$
      DECLARE
        p persianDate;
        months integer;
      BEGIN
        p := g2p(d);
        months := p.year * 12 + (p.month-1) - n;
        RETURN p2g(months/12, mod(months,12)+1, p.day);
      END;
      $$ language plpgsql STABLE;

      -- starting date of last month in Persian Calendar
      CREATE OR REPLACE FUNCTION last_persian_month()
        RETURNS date AS $$
      BEGIN
        RETURN start_of_persian_month(n_months_before(current_date, 1));
      END;
      $$ language plpgsql STABLE;

      CREATE CAST (persianDate AS text) WITH FUNCTION persianDateToString(persianDate) AS ASSIGNMENT;
      CREATE CAST (date AS persiandate) WITH FUNCTION g2p(date) AS ASSIGNMENT;            """,
            reverse_sql="""

DROP CAST (date AS persiandate) CASCADE;
DROP CAST (persianDate AS text) CASCADE;

DROP FUNCTION IF EXISTS last_persian_month();
DROP FUNCTION IF EXISTS n_months_before(date, integer);
DROP FUNCTION IF EXISTS current_persian_month();
DROP FUNCTION IF EXISTS start_of_persian_month(date);

DROP FUNCTION IF EXISTS last_persian_week();
DROP FUNCTION IF EXISTS current_persian_week();
DROP FUNCTION IF EXISTS start_of_persian_week(date);

DROP FUNCTION IF EXISTS g2p(date) CASCADE;
DROP FUNCTION IF EXISTS j2p(integer);
DROP FUNCTION IF EXISTS p2j(integer, integer, integer);
DROP FUNCTION IF EXISTS p2g(integer, integer, integer);
DROP FUNCTION IF EXISTS per_cal(integer);
DROP FUNCTION IF EXISTS persianDateToString(persianDate) CASCADE;


-- Dropping types
DROP TYPE IF EXISTS perCalOutputType CASCADE;
DROP TYPE IF EXISTS persianDate CASCADE;

DROP FUNCTION IF EXISTS startOfJalaaliMonth(timestamp);
DROP FUNCTION IF EXISTS j2d(integer, integer, integer);
DROP FUNCTION IF EXISTS d2j(integer);
DROP FUNCTION IF EXISTS j2g(integer, integer, integer);
DROP FUNCTION IF EXISTS jalCal(integer);
DROP TYPE IF EXISTS jalCalOutputType;
DROP TYPE IF EXISTS d2jOutputType;
DROP TYPE IF EXISTS perCalOutputType CASCADE;

            """
        )
    ]

Here's the alembic migration for it (the query is the same):

"""

Revision ID: <Revision-ID>
Revises: <Revises>
Create Date: ...

"""

from typing import Sequence, Union

from alembic import op

# revision identifiers, used by Alembic.
revision: str = "<Revision-ID>"
down_revision: Union[str, None] = "<Revises>"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.execute("DROP FUNCTION IF EXISTS startOfJalaaliMonth(timestamp);")
    op.execute("DROP FUNCTION IF EXISTS j2d(integer, integer, integer);")
    op.execute("DROP FUNCTION IF EXISTS d2j(integer);")
    op.execute("DROP FUNCTION IF EXISTS j2g(integer, integer, integer);")
    op.execute("DROP FUNCTION IF EXISTS jalCal(integer);")
    op.execute("DROP TYPE IF EXISTS jalCalOutputType;")
    op.execute("DROP TYPE IF EXISTS d2jOutputType;")
    op.execute("DROP TYPE IF EXISTS perCalOutputType CASCADE;")
    op.execute("DROP TYPE IF EXISTS persianDate CASCADE;")
    op.execute(
        """
      -- perCalOutputType
      -- Output of per_cal function as composite type of:
      --   leap: number of years since the last leap year (0 to 4)
      --   gy: Gregorian year of the beginning of Jalaali year
      --   march: the March day of Farvardin the 1st (1st day of jy)
      CREATE TYPE perCalOutputType AS (
        leap integer,
        gy integer,
        march integer
      );
    """
    )
    op.execute(
        """
      -- per_cal
      -- This function determines if the Persian year is
      -- leap (366-day long) or is the common year (365 days), and
      -- finds the day in March (Gregorian calendar) of the first
      -- day of the Persian year (jy).

      -- @param py Persian calendar year (1210 to 1635)
      -- @return PerCalOutputType
      -- @see: http://www.astro.uni.torun.pl/~kb/Papers/EMP/PersianC-EMP.htm
      -- @see: https://github.com/jalaali/jalaali-js/blob/master/index.js

      CREATE OR REPLACE FUNCTION per_cal(py integer)
        RETURNS perCalOutputType AS $$
          DECLARE
            gy integer;
            leapJ integer := 294;
            n integer;
            leapG integer;
            leap integer;
            march integer;
            jump integer := 425;
            result perCalOutputType;
          BEGIN
            gy := py + 621;
            n := py - 1210;
    
            -- Find the number of leap years from AD 621 to the beginning
            -- of the current Persian year in the Persian calendar.
            leapJ := leapJ + n / 33 * 8 + (MOD(n, 33) + 3) / 4;
            if (mod(jump, 33) = 4 AND jump - n = 4) then
               leapJ := leapJ + 1;
            end if;
    
            -- And the same in the Gregorian calendar (until the year gy).
            leapG := gy / 4 - ((gy / 100 + 1) * 3) / 4 - 150;
    
            -- Determine the Gregorian date of Farvardin the 1st.
            march := 20 + leapJ - leapG;
    
            -- Find how many years have passed since the last leap year.
            if (jump - n < 6) then
               n = n - jump + div(jump + 4, 33) * 33;
            end if;
            leap = mod(mod(n + 1, 33) - 1, 4);
            if (leap = -1) then
               leap = 4;
            end if;
    
            select leap, gy, march into result;
            -- select gy, 1, 2 into result;
            RETURN result;
            END;
      $$ LANGUAGE plpgsql STABLE;
    """
    )
    op.execute(
        """
      -- p2j
      -- Converts a date of the Persian calendar to the Julian Day number.
      -- @param py Persian year (1210 to 1635)
      -- @param pm Persian month (1 to 12)
      -- @param pd Persian day (1 to 29/31)
      -- @return Julian Day number
      CREATE OR REPLACE FUNCTION p2j(py integer, pm integer, pd integer)
        RETURNS integer AS $$
      DECLARE
        r perCalOutputType;
      BEGIN
        r :=  per_cal(py);
        RETURN to_char((r.gy||'-3-'||r.march)::date, 'J')::int + (pm - 1) * 31 - (pm / 7) * (pm - 7) + pd - 1;
      END;
      $$ LANGUAGE plpgsql STABLE;
    """
    )
    op.execute(
        """
      -- p2g
      -- Converts Persian date to Gregorian Calendar
      -- @param py Persian year (1210 to 1635)
      -- @param pm Persian month (1 to 12)
      -- @param pd Persian day (1 to 29/31)
      -- @return Gregorian Date

      CREATE OR REPLACE FUNCTION p2g(py integer, pm integer, pd integer)
        RETURNS date AS $$
      BEGIN
        RETURN ('J'||p2j(py, pm, pd))::date;
      END;
      $$ LANGUAGE plpgsql;
    """
    )
    op.execute(
        """
      -- persianDate
      -- Output of j2p function as composite type of:
      --   year: Jalaali year (1 to 3100)
      --   month: Jalaali month (1 to 12)
      --   day: Jalaali day (1 to 29/31)
      CREATE TYPE persianDate AS (
        year integer,
        month integer,
        day integer
      );
    """
    )
    op.execute(
        """
      CREATE OR REPLACE FUNCTION persianDateToString(p persianDate)
        RETURNS varchar(20) AS $$
      BEGIN
        RETURN p.year || '-' || p.month || '-' || p.day;
      END;
      $$ language plpgsql STABLE;
    """
    )
    op.execute(
        """
      -- j2p
      -- Converts the Julian Day number to a date in the Jalaali calendar.
      -- @param jdn Julian Day number
      -- @return persianDate
      CREATE OR REPLACE FUNCTION j2p(jdn integer) RETURNS persianDate AS $$
      DECLARE
        rr perCalOutputType;
        jdn1f integer;
        py integer;
        pd integer;
        pm integer;
        k integer;
        gy integer;
        result persianDate;
      BEGIN
        gy := extract(year from ('J'||jdn)::date)::int; -- Calculate Gregorian year (gy)
        py := gy - 621;

        rr := per_cal(py);
        jdn1f = to_char((gy||'-03-'||rr.march)::date, 'J')::int;
        -- Find number of days that passed since 1 Farvardin.
        k := jdn - jdn1f;
        if (k >= 0) then
           if (k <= 185) then
              -- The first 6 months.
              pm := 1 + div(k, 31);
              pd := mod(k, 31) + 1;
              select py, pm, pd into result;
              return result;
           else
           -- The remaining months.
           k := k - 186;
           end if;
        else
           -- Previous Jalaali year.
           py := py - 1;
           k := k + 179;
           if (rr.leap = 1) then
              k := k + 1;
           end if;
        end if;
        pm := 7 + k / 30;
        pd := mod(k, 30) + 1;
        select py, pm, pd into result;
        return result;
      END;
      $$ LANGUAGE plpgsql STABLE;
    """
    )
    op.execute(
        """
      -- convert to persian date
      CREATE OR REPLACE FUNCTION g2p(d date)
        RETURNS persianDate AS $$
      BEGIN
        RETURN (j2p(to_char(d, 'J')::int));
      END;
      $$ language plpgsql STABLE;
    """
    )
    op.execute(
        """
      -- starting date of current week in Persian Calendar
      CREATE OR REPLACE FUNCTION start_of_persian_week(d date)
        RETURNS date AS $$
      BEGIN
        RETURN d -
          mod(extract(dow from d)::int+1, 7)*'1 day'::interval;
      END;
      $$ language plpgsql STABLE;
    """
    )
    op.execute(
        """

      -- starting date of current week in Persian Calendar
      CREATE OR REPLACE FUNCTION current_persian_week()
        RETURNS date AS $$
      BEGIN
        RETURN current_date -
          mod(extract(dow from current_date)::int+1, 7)*'1 day'::interval;
      END;
      $$ language plpgsql STABLE;
    """
    )
    op.execute(
        """

      -- starting date of last week in Persian Calendar
      CREATE OR REPLACE FUNCTION last_persian_week()
        RETURNS date AS $$
      BEGIN
        RETURN current_persian_week() - '7 days'::interval;
      END;
      $$ language plpgsql STABLE;
    """
    )
    op.execute(
        """
      -- starting date of month of a given date in Persian Calendar
      CREATE OR REPLACE FUNCTION start_of_persian_month(d date)
        RETURNS date AS $$
      DECLARE
        pd integer;
        r persianDate;
      BEGIN
        pd := to_char(d, 'J')::int;
        r := j2p(pd);
        pd := p2j(r.year, r.month, 1);
        RETURN ('J'||pd)::date;
      END;
      $$ LANGUAGE plpgsql STABLE;
    """
    )
    op.execute(
        """

      -- starting date of current month in Persian Calendar
      CREATE OR REPLACE FUNCTION current_persian_month()
        RETURNS date AS $$
      BEGIN
        RETURN start_of_persian_month(current_date);
      END;
      $$ language plpgsql STABLE;
    """
    )
    op.execute(
        """

      -- starting date of n months before in Persian Calendar
      CREATE OR REPLACE FUNCTION n_months_before(d date, n integer)
        RETURNS date AS $$
      DECLARE
        p persianDate;
        months integer;
      BEGIN
        p := g2p(d);
        months := p.year * 12 + (p.month-1) - n;
        RETURN p2g(months/12, mod(months,12)+1, p.day);
      END;
      $$ language plpgsql STABLE;
    """
    )
    op.execute(
        """
      -- starting date of last month in Persian Calendar
      CREATE OR REPLACE FUNCTION last_persian_month()
        RETURNS date AS $$
      BEGIN
        RETURN start_of_persian_month(n_months_before(current_date, 1));
      END;
      $$ language plpgsql STABLE;
"""
    )
    op.execute(
        "CREATE CAST (persianDate AS text) WITH FUNCTION persianDateToString(persianDate) AS ASSIGNMENT;"
    )
    op.execute(
        "CREATE CAST (date AS persiandate) WITH FUNCTION g2p(date) AS ASSIGNMENT;"
    )


def downgrade() -> None:
    op.execute("DROP CAST (date AS persiandate);")
    op.execute("DROP CAST (persianDate AS text);")
    op.execute("DROP FUNCTION IF EXISTS last_persian_month();")
    op.execute("DROP FUNCTION IF EXISTS n_months_before(date, integer);")
    op.execute("DROP FUNCTION IF EXISTS current_persian_month();")
    op.execute("DROP FUNCTION IF EXISTS start_of_persian_month(date);")

    op.execute("DROP FUNCTION IF EXISTS last_persian_week();")
    op.execute("DROP FUNCTION IF EXISTS current_persian_week();")
    op.execute("DROP FUNCTION IF EXISTS start_of_persian_week(date);")

    op.execute("DROP FUNCTION IF EXISTS g2p(date);")
    op.execute("DROP FUNCTION IF EXISTS j2p(integer);")
    op.execute("DROP FUNCTION IF EXISTS p2j(integer, integer, integer);")
    op.execute("DROP FUNCTION IF EXISTS p2g(integer, integer, integer);")
    op.execute("DROP FUNCTION IF EXISTS per_cal(integer);")

    op.execute("DROP FUNCTION IF EXISTS persianDateToString(persianDate);")
    op.execute("DROP TYPE IF EXISTS perCalOutputType CASCADE;")
    op.execute("DROP TYPE IF EXISTS persianDate CASCADE;")
‍```