converting date to Jalal date in clickhouse

Question

I use clickhouse version 22.3.15.33

In my table the dates are in a format like this: 2023-01-15. I want to calculate the sum of a variable in my table for each Jalal month.

So first I need to convert this date to Jalal date and then get the month and then use group by based on the month. But using this code:

SELECT format(t.created_date, 'yyyy-mm-dd', 'fa')
from table t

gives me this error:

SQL Error [1002]: ClickHouse exception, code: 1002, host: 172.21.16.1, port: 8123; Code: 43. DB::Exception: Illegal type Date of argument 1 of function format: While processing format(created_date, 'yyyy-MM-dd', 'fa'). (ILLEGAL_TYPE_OF_ARGUMENT) (version 22.3.15.33 (official build))

Answer 1

ClickHouse doesn't have any built-in function to convert Georgian date to Jalali date, but you can make a function to do it following these steps:

1. Install Python library

sudo pip install jdatetime

2. Create a script to convert Georgian date to Jalali date

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

#!/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()

Don't forget this file should be executable or you can do chmod 777 georgian_to_jalali.py

3. Create a ClickHouse function

sudo nano /etc/clickhouse-server/georgian_to_jalali_function.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>

Finally

You can run this query

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

or

SELECT georgian_to_jalali('2023-01-15') as jalali_date;

Your result looks like this picture

For more information about ClickHouse functions, you can read