Contents

• Ethiopian Calendar Extension for PostgreSQL
  • Overview
  • Quick Start with Docker (Recommended)
    • Using Makefile Commands (Recommended)
    • Using Docker Compose Directly
    • Quick Test
    • Configuration with .env File
  • Installation (Manual)
    • Prerequisites
    • Build and Install
  • Functions
    • Primary Functions
    • Function Naming
  • Usage Examples
    • Basic Conversion
    • Generated Columns
    • Querying with Ethiopian Dates
  • Implementation Details
    • Conversion Algorithm
    • Technical Notes
  • Quick Testing Guide
    • Quick Start Testing
    • Quick Command-Line Test
  • Testing
    • Running Tests with Docker (Recommended)
    • Running Tests from test/ Directory
    • Test Coverage
    • Manual Testing
  • Project Structure
  • References
  • License
  • Extension Standards
  • Contributing
  • PostgreSQL Version Support
    • Using Different PostgreSQL Versions with Docker
  • License

Ethiopian Calendar Extension for PostgreSQL

A PostgreSQL extension that converts Gregorian timestamps to Ethiopian calendar dates using academically verified formulas from “Calendrical Calculations” by Nachum Dershowitz & Edward M. Reingold (Cambridge University Press).

Overview

The Ethiopian calendar is a solar calendar with 13 months: - 12 months of 30 days each (Meskerem, Tikimt, Hidar, Tahsas, Tir, Yekatit, Megabit, Miazia, Ginbot, Sene, Hamle, Nehase) - 1 month of 5 or 6 days (Pagumē, month 13) - Leap years occur every 4 years (years where year % 4 == 3), adding a 6th day to Pagumē - The Ethiopian New Year (Meskerem 1) typically falls on September 11 or 12 in the Gregorian calendar - The Ethiopian calendar is approximately 7-8 years behind the Gregorian calendar

Quick Start with Docker (Recommended)

The easiest way to get started is using Docker. No need to install PostgreSQL development headers or build tools!

Using Makefile Commands (Recommended)

All operations are handled through Docker Compose via Makefile targets:

# Start PostgreSQL with the extension pre-installed (production mode)
make docker-start

# Or start in development mode (mounts source code)
make docker-dev

# Connect to database and create extension
make docker-shell
# Then in psql: CREATE EXTENSION pg_ethiopian_calendar;

# Run tests
make docker-test

# View logs
make docker-logs

# Show status
make docker-status

# Stop containers
make docker-stop

# Clean up everything
make docker-clean

# See all available commands
make docker-help

Using Docker Compose Directly

You can also use docker compose commands directly:

# Start PostgreSQL (production)
docker compose --profile default up -d postgres

# Start PostgreSQL (development)
docker compose --profile dev up -d postgres-dev

# Run tests
docker compose --profile test up --build --abort-on-container-exit test

# Connect to PostgreSQL
docker compose exec postgres psql -U postgres

# Or from your host machine (if you have psql installed)
psql -h localhost -U postgres -d postgres
# Password: postgres (or from .env file)

Quick Test

Once PostgreSQL is running:

# Connect to database
make docker-shell

# In psql, create the extension and test:
CREATE EXTENSION pg_ethiopian_calendar;
SELECT to_ethiopian_date(NOW());

# Or one-liner:
docker compose exec postgres psql -U postgres -c "CREATE EXTENSION IF NOT EXISTS pg_ethiopian_calendar; SELECT to_ethiopian_date(NOW());"

Configuration with .env File

The .env file is REQUIRED. The Docker setup uses environment variables for configuration. You must create a .env file:

# Create .env from the example template (REQUIRED)
cp .env.example .env

# Edit .env and set your required values
nano .env  # or use your preferred editor

Important: All environment variables must be set. The make docker-init command will automatically create .env from .env.example if it doesn’t exist, but you should review and customize the values.

The .env file is required and must contain:

• PostgreSQL credentials: POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB (required)
• Port mapping: POSTGRES_PORT (required)
• PostgreSQL version: PG_VERSION (required, e.g., 14, 15, 16)
• Test database settings: TEST_POSTGRES_* variables (required for testing)

Example .env file:

# PostgreSQL Configuration (REQUIRED)
POSTGRES_USER=myuser
POSTGRES_PASSWORD=mysecurepassword
POSTGRES_DB=myapp
POSTGRES_PORT=5432
PG_VERSION=14

# Test Database Configuration (REQUIRED for testing)
TEST_POSTGRES_USER=testuser
TEST_POSTGRES_PASSWORD=testpass
TEST_POSTGRES_DB=testdb
TEST_POSTGRES_PORT=5433

Note: The .env file is automatically ignored by git (in .gitignore) to keep your credentials secure. You must create a .env file from .env.example before running any docker commands.

Installation (Manual)

Prerequisites

• PostgreSQL 11 or later
• PostgreSQL development headers (postgresql-server-dev-* on Debian/Ubuntu, postgresql-devel on RHEL/CentOS)
• make and a C compiler (gcc)

Build and Install

# Build the extension
make

# Install the extension (requires superuser privileges)
sudo make install

# Create the extension in your database
psql -d your_database -c "CREATE EXTENSION pg_ethiopian_calendar;"

Functions

Primary Functions

to_ethiopian_date(timestamp) → text / pg_ethiopian_to_date(timestamp) → text

Converts a Gregorian timestamp to an Ethiopian calendar date as text. The time component is discarded; only the date is converted.

Parameters: - timestamp: A Gregorian calendar timestamp

Returns: - text: Ethiopian calendar date as string in format “YYYY-MM-DD”

Examples: ```sql – Using original name SELECT to_ethiopian_date(‘2024-01-01’::timestamp); – Returns: ‘2016-04-23’

– Using pg_ prefixed alias (PostgreSQL extension naming convention) SELECT pg_ethiopian_to_date(‘2024-01-01’::timestamp); – Returns: ‘2016-04-23’ ```

from_ethiopian_date(text) → timestamp / pg_ethiopian_from_date(text) → timestamp

Converts an Ethiopian calendar date string to a Gregorian timestamp. The input should be in format “YYYY-MM-DD” (Ethiopian calendar).

Parameters: - ethiopian_date: Ethiopian calendar date as text (format: YYYY-MM-DD)

Returns: - timestamp: Gregorian calendar timestamp at midnight

Examples: ```sql – Using original name SELECT from_ethiopian_date(‘2016-04-23’); – Returns: ‘2024-01-01 00:00:00’::timestamp

– Using pg_ prefixed alias SELECT pg_ethiopian_from_date(‘2016-04-23’); – Returns: ‘2024-01-01 00:00:00’::timestamp ```

to_ethiopian_datetime(timestamp) → timestamp with time zone / pg_ethiopian_to_datetime(timestamp) → timestamp with time zone

Converts a Gregorian timestamp to an Ethiopian calendar TIMESTAMP WITH TIME ZONE. The date is converted to Ethiopian calendar; the time-of-day remains the same.

Parameters: - timestamp: A Gregorian calendar timestamp

Returns: - timestamp with time zone: A timestamp with the date in Ethiopian calendar and the original time preserved

Examples: ```sql – Using original name SELECT to_ethiopian_datetime(‘2024-01-01 14:30:00’::timestamp);

– Using pg_ prefixed alias SELECT pg_ethiopian_to_datetime(‘2024-01-01 14:30:00’::timestamp); ```

to_ethiopian_timestamp(timestamp) → timestamp / pg_ethiopian_to_timestamp(timestamp) → timestamp

Converts a Gregorian timestamp to an Ethiopian calendar TIMESTAMP (without time zone). The date is converted to Ethiopian calendar; the time-of-day remains the same. This function is ideal for use in generated columns where you want a TIMESTAMP type (not TEXT).

Parameters: - timestamp: A Gregorian calendar timestamp

Returns: - timestamp: A timestamp (without time zone) with the date in Ethiopian calendar and the original time preserved

Examples: ```sql – Using original name SELECT to_ethiopian_timestamp(‘2024-01-01 14:30:00’::timestamp);

– Using pg_ prefixed alias SELECT pg_ethiopian_to_timestamp(‘2024-01-01 14:30:00’::timestamp);

– In generated columns 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 ); ```

current_ethiopian_date() → text

Returns the current date in Ethiopian calendar as text. This function is STABLE (not IMMUTABLE) because it depends on the current time. Useful for DEFAULT values.

Returns: - text: Current Ethiopian calendar date as string in format “YYYY-MM-DD”

Examples: ```sql – Get current Ethiopian date SELECT current_ethiopian_date();

– Use as default value CREATE TABLE events ( id SERIAL PRIMARY KEY, event_date_ethiopian TEXT DEFAULT current_ethiopian_date() ); ```

Function Naming

The extension provides both naming styles: - Original names: to_ethiopian_date(), from_ethiopian_date(), to_ethiopian_datetime(), to_ethiopian_timestamp(), current_ethiopian_date() - pg_ prefixed aliases: pg_ethiopian_to_date(), pg_ethiopian_from_date(), pg_ethiopian_to_datetime(), pg_ethiopian_to_timestamp()

Both styles are equivalent and call the same underlying C functions. Use whichever style you prefer. The pg_ prefixed versions follow PostgreSQL extension naming conventions (similar to pg_stat_statements, pg_trgm).

Usage Examples

Basic Conversion

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

-- Convert current timestamp
SELECT to_ethiopian_date(NOW()::timestamp);

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

-- Convert with time preserved
SELECT to_ethiopian_datetime('2024-12-25 23:59:59'::timestamp);

-- Round-trip conversion
SELECT from_ethiopian_date(to_ethiopian_date('2024-01-01'::timestamp));
-- Returns: '2024-01-01 00:00:00'::timestamp

Generated Columns

You can use these functions in generated columns to automatically maintain Ethiopian calendar dates. You have two options:

Option 1: Using TIMESTAMP type (Recommended for timestamps) ```sql CREATE TABLE sample ( id SERIAL PRIMARY KEY, created_at TIMESTAMP DEFAULT NOW(), created_at_ethiopian TIMESTAMP GENERATED ALWAYS AS (to_ethiopian_timestamp(created_at)) STORED, updated_at TIMESTAMP DEFAULT NOW(), updated_at_ethiopian TIMESTAMP GENERATED ALWAYS AS (to_ethiopian_timestamp(updated_at)) STORED );

INSERT INTO sample (created_at) VALUES (NOW()); SELECT * FROM sample; ```

Option 2: Using TEXT type (For date-only values) ```sql CREATE TABLE sample ( id SERIAL PRIMARY KEY, created_at TIMESTAMP DEFAULT NOW(), created_at_et TEXT GENERATED ALWAYS AS (to_ethiopian_date(created_at)) STORED );

INSERT INTO sample (created_at) VALUES (NOW()); SELECT * FROM sample; ```

Option 3: Using DEFAULT with current_ethiopian_date() sql CREATE TABLE events ( id SERIAL PRIMARY KEY, event_date_ethiopian TEXT DEFAULT current_ethiopian_date() );

Querying with Ethiopian Dates

-- Find records created on a specific Ethiopian date
SELECT *
FROM sample
WHERE created_at_et = to_ethiopian_date('2024-01-15'::timestamp);

-- Convert multiple dates
SELECT 
    created_at,
    to_ethiopian_date(created_at) AS ethiopian_date
FROM sample;

Implementation Details

Conversion Algorithm

The extension uses Julian Day Number (JDN) as an intermediate representation for calendar conversions:

1. Gregorian → JDN: Convert the input Gregorian date to Julian Day Number using the standard formula from “Calendrical Calculations”
2. JDN → Ethiopian: Convert the JDN to Ethiopian calendar date components (year, month, day)
3. Ethiopian → JDN → Gregorian: Convert back to Gregorian DATE for PostgreSQL storage (since PostgreSQL DATE is always Gregorian internally)

The conversion formulas are based on: - Ethiopian Epoch: August 29, 8 CE (Gregorian) = JDN 1724221 - 4-year cycles: The Ethiopian calendar uses 4-year eras (1461 days total: 3 years of 365 days + 1 leap year of 366 days) - Leap years: Years where year % 4 == 3 are leap years

Technical Notes

• DATE Type Limitation: PostgreSQL’s DATE type is always stored as Gregorian internally. The functions perform the conversion and return a DATE value that represents the same calendar day (same JDN) in the Ethiopian calendar system.
• Time Preservation: to_ethiopian_datetime() preserves the original time-of-day while converting only the date portion.
• NULL Handling: Both functions return NULL if the input timestamp is NULL.
• Immutability: Both functions are marked as IMMUTABLE and STRICT for optimal query planning and NULL handling.

Quick Testing Guide

Quick Start Testing

# 1. Start PostgreSQL container
make docker-start

# 2. Connect to database
make docker-shell

# 3. In psql, create extension and test
CREATE EXTENSION pg_ethiopian_calendar;
SELECT to_ethiopian_date('2024-01-01'::timestamp);
SELECT pg_ethiopian_to_date('2024-01-01'::timestamp);
SELECT from_ethiopian_date('2016-04-23');

Quick Command-Line Test

# Test functions without interactive psql
docker compose exec -T postgres psql -U postgres -c \
  "CREATE EXTENSION IF NOT EXISTS pg_ethiopian_calendar; \
   SELECT to_ethiopian_date('2024-01-01'::timestamp) AS ethiopian_date;"

For detailed testing, use make docker-test.

Testing

The extension includes comprehensive pgTAP unit tests and a Docker-based test environment.

Running Tests with Docker (Recommended)

The easiest way to run tests is using Makefile:

# Run all tests
make docker-test

# Or use docker compose directly
docker compose --profile test up --build --abort-on-container-exit test

This will: 1. Build a PostgreSQL container (default: version 14, supports 11+) with pgTAP installed 2. Build and install the extension 3. Run all pgTAP tests automatically 4. Exit with the test results

Running Tests from test/ Directory

You can also run tests from the test directory:

cd test
docker compose up --build --abort-on-container-exit

Test Coverage

The test suite (test/tests/ethiopian_calendar_tests.sql) includes:

• Extension loading and function existence checks
• Type verification for return values
• Known reference conversions
• NULL input handling
• Time component preservation/discarding
• Consistency checks
• Leap year boundary tests (both Gregorian and Ethiopian)
• Year boundary tests
• Function immutability and strictness verification

Manual Testing

To run tests manually:

# Install pgTAP in your PostgreSQL database
CREATE EXTENSION pgtap;

# Create the extension
CREATE EXTENSION pg_ethiopian_calendar;

# Run tests
psql -d your_database -f test/tests/ethiopian_calendar_tests.sql

Project Structure

pg-ethiopian-calendar/
├── Makefile                    # Build configuration using PGXS
├── pg_ethiopian_calendar.control  # Extension metadata
├── META.json                   # PGXN metadata
├── README.md                   # This file
├── LICENSE                     # PostgreSQL License
├── .gitignore                  # Git ignore patterns
├── .dockerignore               # Docker ignore patterns
├── .env.example                # Environment variables template
├── Dockerfile                  # Production Docker image
├── Dockerfile.dev              # Development Docker image
├── docker-compose.yml          # Docker Compose configuration
├── src/
│   └── ethiopian_calendar.c   # C implementation
├── sql/
│   └── pg_ethiopian_calendar--1.0.sql  # SQL function definitions
└── test/
    ├── Dockerfile              # Docker image for testing
    ├── docker-compose.yml      # Docker Compose configuration for tests
    ├── pgtest.sh               # Test runner script
    └── tests/
        └── ethiopian_calendar_tests.sql  # pgTAP test suite

References

• Dershowitz, Nachum & Reingold, Edward M. (2008). Calendrical Calculations (3rd ed.). Cambridge University Press. ISBN: 978-0-521-88540-9
• PostgreSQL Extension Building: https://www.postgresql.org/docs/current/extend-pgxs.html

License

This extension is provided as-is for educational and practical use.

Extension Standards

This extension follows PostgreSQL extension standards:

• ✅ PGXS-based build system - Uses PostgreSQL Extension System
• ✅ Versioned SQL migrations - Follows extension--version.sql naming
• ✅ Standard control file - Proper metadata and configuration
• ✅ Proper function attributes - IMMUTABLE STRICT for optimal performance
• ✅ Error handling - Uses PostgreSQL ereport for errors
• ✅ Documentation - Functions documented with COMMENT
• ✅ Naming conventions - Lowercase with underscores, descriptive names

See EXTENSION_STANDARDS.md and NAMING_CONVENTIONS.md for detailed information.

Contributing

Contributions, bug reports, and feature requests are welcome!

Key points: - Maintain compatibility with PostgreSQL 11+ - Follow the conversion formulas from “Calendrical Calculations” - Include tests for new features - Update documentation as needed

Submit issues and pull requests at: https://github.com/HuluWZ/pg-ethiopian-calendar

PostgreSQL Version Support

The extension supports PostgreSQL 11 and later.

Using Different PostgreSQL Versions with Docker

You can specify the PostgreSQL version using the PG_VERSION environment variable:

# Use PostgreSQL 11
PG_VERSION=11 make docker-start

# Use PostgreSQL 12
PG_VERSION=12 make docker-start

# Use PostgreSQL 13
PG_VERSION=13 make docker-start

# Use PostgreSQL 14 (default)
make docker-start

# Use PostgreSQL 15
PG_VERSION=15 make docker-start

# Use PostgreSQL 16
PG_VERSION=16 make docker-start

Or with docker compose directly:

PG_VERSION=11 docker compose build postgres
PG_VERSION=11 docker compose up -d postgres

License

This extension is released under the PostgreSQL License. See LICENSE for details.