Going Beyond 4713 BC: Storing Ancient Dates in YugabyteDB

				
					-- Leap year check
CREATE OR REPLACE FUNCTION ext_is_leap_year(y integer)
RETURNS boolean LANGUAGE sql IMMUTABLE AS $$
  SELECT (y % 400 = 0) OR ((y % 4 = 0) AND (y % 100 <> 0));
$$;

-- Days in month
CREATE OR REPLACE FUNCTION ext_days_in_month(y integer, m integer)
RETURNS integer LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE m
    WHEN 1 THEN 31
    WHEN 2 THEN CASE WHEN ext_is_leap_year(y) THEN 29 ELSE 28 END
    WHEN 3 THEN 31
    WHEN 4 THEN 30
    WHEN 5 THEN 31
    WHEN 6 THEN 30
    WHEN 7 THEN 31
    WHEN 8 THEN 31
    WHEN 9 THEN 30
    WHEN 10 THEN 31
    WHEN 11 THEN 30
    WHEN 12 THEN 31
  END;
$$;

-- Validate Y/M/D
CREATE OR REPLACE FUNCTION ext_valid_ymd(y integer, m integer, d integer)
RETURNS boolean LANGUAGE sql IMMUTABLE AS $$
  SELECT m BETWEEN 1 AND 12
     AND d BETWEEN 1 AND ext_days_in_month(y, m);
$$;

-- Julian Day Number for astronomical years
CREATE OR REPLACE FUNCTION ext_jdn_gregorian(y integer, m integer, d integer)
RETURNS bigint LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE
  a int; yp int; mp int; jdn bigint;
BEGIN
  IF NOT ext_valid_ymd(y,m,d) THEN
    RAISE EXCEPTION 'Invalid date parts: %-%-%', y,m,d;
  END IF;

  a  := (14 - m) / 12;
  yp := y + 4800 - a;
  mp := m + 12*a - 3;

  jdn := d + ((153*mp + 2)/5)
       + 365*yp + (yp/4) - (yp/100) + (yp/400)
       - 32045;

  RETURN jdn;
END;
$$;

-- Pretty AD/BC format
CREATE OR REPLACE FUNCTION ext_fmt_ad_bc(y int, m int, d int)
RETURNS text LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE
  era text; y_abs int;
BEGIN
  IF y >= 1 THEN
    era := 'AD'; y_abs := y;
  ELSE
    era := 'BC'; y_abs := 1 - y;  -- 0 -> 1 BC, -1 -> 2 BC, etc.
  END IF;
  RETURN lpad(y_abs::text,4,'0') || '-' ||
         lpad(m::text,2,'0')    || '-' ||
         lpad(d::text,2,'0')    || ' ' || era;
END;
$$;

-- Parser for 'YYYY-MM-DD' + optional 'BC'
CREATE OR REPLACE FUNCTION ext_parse_text_date(t text)
RETURNS TABLE (y integer, m integer, d integer)
LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE
  s text := trim(t);
  is_bc boolean := false;
  core text;
  parts text[];
BEGIN
  IF s ~* '\s*B\s*C\s*$' THEN
    is_bc := true;
    core := regexp_replace(s, '\s*B\s*C\s*$', '', 'i');
  ELSE
    core := s;
  END IF;

  parts := regexp_matches(core, '^\s*([+-]?\d+)-(\d{1,2})-(\d{1,2})\s*$', 'i');
  IF parts IS NULL THEN
    RAISE EXCEPTION 'Invalid date literal: %', t;
  END IF;

  y := parts[1]::int;
  m := parts[2]::int;
  d := parts[3]::int;

  IF is_bc THEN y := 1 - y; END IF;
  RETURN NEXT;
END;
$$;