DEV Community

Cover image for ClickHouse Custom Function: Convert Georgian Dates to Jalali Dates
Shahab Ranjbary
Shahab Ranjbary

Posted on • Updated on

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

Ensure the script is executable:

sudo chmod 777 /var/lib/clickhouse/user_scripts/georgian_to_jalali.py
Enter fullscreen mode Exit fullscreen mode

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

sudo nano /etc/clickhouse-server/georgian_to_jalali_function.xml
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

Image description

Top comments (0)