pg_ethiopian_calendar

A PostgreSQL extension for converting between Gregorian and Ethiopian calendar dates.

Description

The Ethiopian calendar (Ge'ez calendar) is a solar calendar with 13 months used in Ethiopia and Eritrea. This extension provides functions to convert dates between Gregorian and Ethiopian calendars using academically verified algorithms from "Calendrical Calculations" by Dershowitz & Reingold.

Key Features:

• Bidirectional conversion (Gregorian ↔ Ethiopian)
• IMMUTABLE functions for use in indexes and generated columns
• Time-preserving conversions
• NULL-safe operations

Installation

Choose the method that best fits your setup:

Option 1: NPM Package (Recommended for Prisma/Drizzle/ORMs)

Works on any PostgreSQL including Neon, Supabase, Railway, AWS RDS, and more. No Docker or C compilation required!

# Install
npm install @huluwz/pg-ethiopian-calendar

# Initialize (auto-detects your ORM)
npx ethiopian-calendar init

# Apply migration (example for Prisma)
npx prisma migrate dev

Supports: Prisma, Drizzle, TypeORM, and raw SQL.

📖 Full NPM Package Documentation

Option 2: Docker Hub

Pre-built PostgreSQL images with the extension installed:

# Pull the image (PostgreSQL 14, 15, 16, or 17)
docker pull huluwz/pg-ethiopian-calendar:latest        # PostgreSQL 17
docker pull huluwz/pg-ethiopian-calendar:1.1.0-pg16    # PostgreSQL 16
docker pull huluwz/pg-ethiopian-calendar:1.1.0-pg15    # PostgreSQL 15
docker pull huluwz/pg-ethiopian-calendar:1.1.0-pg14    # PostgreSQL 14

# Run the container
docker run -d \
  --name ethiopian-calendar-db \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  huluwz/pg-ethiopian-calendar:latest

# Connect and enable the extension
psql -h localhost -U postgres -c "CREATE EXTENSION pg_ethiopian_calendar;"

psql -h localhost -U postgres -c "SELECT to_ethiopian_date('2025-12-17');"

Docker Hub: https://hub.docker.com/r/huluwz/pg-ethiopian-calendar

Option 3: From PGXN

For self-hosted PostgreSQL with admin access:

pgxn install pg_ethiopian_calendar

Then enable the extension:

CREATE EXTENSION pg_ethiopian_calendar;

Option 4: From Source

git clone https://github.com/HuluWZ/pg-ethiopian-calendar.git
cd pg-ethiopian-calendar
make
sudo make install

Quick Start

-- Get current Ethiopian date
SELECT to_ethiopian_date();
-- Returns: '2018-04-23' (current date)

-- Convert specific date
SELECT to_ethiopian_date('2024-01-01'::timestamp);
-- Returns: '2016-04-23'

-- Convert Ethiopian to Gregorian  
SELECT from_ethiopian_date('2016-04-23');
-- Returns: '2024-01-01 00:00:00'

Functions

to_ethiopian_date() → text

Returns the current date in Ethiopian calendar.

SELECT to_ethiopian_date();
-- '2018-04-23'

to_ethiopian_date(timestamp) → text

Converts a Gregorian timestamp to Ethiopian date string.

SELECT to_ethiopian_date('2024-01-01'::timestamp);
-- '2016-04-23'

from_ethiopian_date(text) → timestamp

Converts an Ethiopian date string to Gregorian timestamp.

SELECT from_ethiopian_date('2016-04-23');
-- '2024-01-01 00:00:00'

to_ethiopian_timestamp() → timestamp

Returns the current timestamp in Ethiopian calendar.

SELECT to_ethiopian_timestamp();
-- '2018-04-23 14:30:00'

to_ethiopian_timestamp(timestamp) → timestamp

Converts Gregorian timestamp to Ethiopian timestamp (preserves time). Ideal for generated columns.

SELECT to_ethiopian_timestamp('2024-01-01 14:30:00'::timestamp);
-- '2016-04-23 14:30:00'

to_ethiopian_datetime(timestamp) → timestamptz

Converts Gregorian timestamp to Ethiopian timestamp with time zone.

SELECT to_ethiopian_datetime('2024-01-01 14:30:00'::timestamp);

Function Aliases

All functions have pg_ prefixed aliases following PostgreSQL naming conventions:

Function	Alias
to_ethiopian_date()	pg_ethiopian_to_date()
from_ethiopian_date()	pg_ethiopian_from_date()
to_ethiopian_timestamp()	pg_ethiopian_to_timestamp()
to_ethiopian_datetime()	pg_ethiopian_to_datetime()

Usage Examples

Generated Columns

Timestamp Type (Recommended)

Use to_ethiopian_timestamp() for full DateTime with time preserved:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP DEFAULT NOW(),
    created_at_ethiopian TIMESTAMP GENERATED ALWAYS AS 
        (to_ethiopian_timestamp(created_at)) STORED
);

INSERT INTO orders DEFAULT VALUES;
SELECT * FROM orders;
-- created_at: 2026-01-04 12:30:00
-- created_at_ethiopian: 2018-04-26 12:30:00

Text Type

Use to_ethiopian_date() for date-only string format:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_date TIMESTAMP NOT NULL,
    event_date_ethiopian VARCHAR(10) GENERATED ALWAYS AS 
        (to_ethiopian_date(event_date)) STORED
);
-- event_date_ethiopian: '2018-04-26'

Default Values

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_date TEXT DEFAULT current_ethiopian_date()
);

Indexing

CREATE INDEX idx_orders_ethiopian 
ON orders (to_ethiopian_date(created_at));

Using with ORMs

Prisma

Schema

model Order {
  id                 Int       @id @default(autoincrement())
  createdAt          DateTime  @default(now()) @map("created_at")
  createdAtEthiopian DateTime? @map("created_at_ethiopian")  // Generated column

  @@map("orders")
}

⚠️ Important: Migration Workflow

Prisma doesn't natively support GENERATED ALWAYS AS columns. You must create migrations manually:

# ❌ DON'T do this - Prisma will generate wrong SQL
npx prisma migrate dev

# ✅ DO this instead - create empty migration, then edit
npx prisma migrate dev --create-only --name add_orders_table

Then manually edit migration.sql:

CREATE TABLE "orders" (
    "id" SERIAL PRIMARY KEY,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "created_at_ethiopian" TIMESTAMP(3) GENERATED ALWAYS AS 
        (to_ethiopian_timestamp(created_at)) STORED
);

Finally apply:

npx prisma migrate deploy

Usage

// Ethiopian date is automatically populated via generated column!
const order = await prisma.order.create({
  data: { customerName: 'Abebe Kebede' }
})
console.log(order.createdAtEthiopian) // Ethiopian timestamp (DateTime)

// Raw query
const today = await prisma.$queryRaw`SELECT to_ethiopian_date()`

📖 Full Prisma Guide

Drizzle

import { sql } from 'drizzle-orm'

export const orders = pgTable('orders', {
  id: serial('id').primaryKey(),
  createdAt: timestamp('created_at').defaultNow(),
  createdAtEthiopian: timestamp('created_at_ethiopian')
    .generatedAlwaysAs(sql`to_ethiopian_timestamp(created_at)`),
})

📖 Full Drizzle Guide

Other ORMs

• 📖 TypeORM Guide
• 📖 Raw SQL Guide

Examples

Complete working demos are available for each ORM:

Example	ORM	Use Case	Features
prisma/	Prisma	E-commerce	Orders, customers, generated columns
drizzle/	Drizzle	Event Management	Holidays, appointments, Ethiopian-only dates
typeorm/	TypeORM	Blog Platform	Posts, comments, publish workflow

Quick start:

cd examples/drizzle  # or prisma, typeorm
npm install
cp env.example .env  # Set your DATABASE_URL
npm run dev

Requirements: Node.js >= 16, PostgreSQL >= 12

📖 All Examples & Documentation

Ethiopian Calendar Overview

Property	Value
Months	13 (12 × 30 days + 1 × 5-6 days)
Leap Year	Every 4 years (year % 4 == 3)
New Year	September 11-12 (Gregorian)
Year Offset	~7-8 years behind Gregorian

Month Names: Meskerem, Tikimt, Hidar, Tahsas, Tir, Yekatit, Megabit, Miazia, Ginbot, Sene, Hamle, Nehase, Pagumē

Compatibility

• PostgreSQL 11, 12, 13, 14, 15, 16, 17
• Prisma, Drizzle, TypeORM (more ORMs coming soon)
• All PostgreSQL hosting providers (Neon, Supabase, Railway, AWS RDS, etc.)

Testing

# Using Docker
make docker-test

# Manual
psql -d mydb -f test/tests/ethiopian_calendar_tests.sql

Author

Hulunlante Worku — hulunlante.w@gmail.com

License

Released under the PostgreSQL License.

Links

• NPM: https://www.npmjs.com/package/@huluwz/pg-ethiopian-calendar
• PGXN: https://pgxn.org/dist/pg_ethiopian_calendar/
• Docker Hub: https://hub.docker.com/r/huluwz/pg-ethiopian-calendar
• GitHub: https://github.com/HuluWZ/pg-ethiopian-calendar
• Issues: https://github.com/HuluWZ/pg-ethiopian-calendar/issues

References

• Dershowitz, N. & Reingold, E.M. (2008). Calendrical Calculations (3rd ed.). Cambridge University Press.