ClickHouse Custom Function: Convert Georgian Dates to Jalali Dates

Dates are a fundamental part of data analysis, and often, there's a need to convert between different date systems. While the Gregorian (or Georgian) calendar is internationally the most widely used civil calendar, the Jalali or Persian calendar is used predominantly in certain regions like Iran. and has its own significance. Especially when dealing with data from regions using the Jalali calendar, converting between these date systems becomes essential.

In this guide, we'll walk through setting up a simple integration in ClickHouse that allows you to convert Georgian dates to Jalali dates. ClickHouse, a fast and efficient column-oriented database management system, allows the integration of custom functions. We'll leverage this capability to use a Python script for our date conversion.

Follow the steps below to set up this conversion functionality:

1. Install the Necessary Python Library:

sudo pip install jdatetime

2. Scripting the Conversion:
Initiate by creating a Python script named georgian_to_jalali.py.

sudo nano /var/lib/clickhouse/user_scripts/georgian_to_jalali.py

Populate the script with:

#!/usr/bin/python3
import jdatetime
import sys

def georgian_to_jalali(date_str):
    georgian_date = jdatetime.datetime.strptime(date_str, '%Y-%m-%d')
    jalali_date = jdatetime.datetime.fromgregorian(datetime=georgian_date).strftime('%Y-%m-%d')
    return jalali_date

def main():
    for line in sys.stdin:
        date_str = str(line.rstrip())
        print(georgian_to_jalali(date_str), end='')
        sys.stdout.flush()

if __name__ == "__main__":
    main()

Ensure the script is executable:

sudo chmod 777 /var/lib/clickhouse/user_scripts/georgian_to_jalali.py

3. Integrate with ClickHouse:
Define the ClickHouse function by creating an XML file:

sudo nano /etc/clickhouse-server/georgian_to_jalali_function.xml

Input the following XML:

<functions>
    <function>
        <type>executable</type>
        <name>georgian_to_jalali</name>
        <return_type>String</return_type>
        <argument>
            <type>String</type>
            <name>value</name>
        </argument>
        <format>TabSeparated</format>
        <command>georgian_to_jalali.py</command>
    </function>
</functions>

Usage:
With everything set, utilize the function in ClickHouse queries. Sample queries include:

SELECT today() as georgian_date, georgian_to_jalali(today()) as jalali_date;

After executing, the results align with the referenced image provided.