formatDateTime(datetime_field, '%Y-%m-%d %Hh') AS DateTime returns error

[ghuname]

Describe the bug
I have a table with DateTime field. If I am using DB API and:

select SELECT 
        formatDateTime(ipdrCreationTime, '%Y-%m-%d %Hh') AS DateTime

I get an error unsupported format character 'Y' (0x59) at index 380

Using the same select with pure Client works well.

I also noticed problems with select that use double or back quotes as field alias:

select field_name as "Flap%",
, field_name2 as `Flap%`
...

Double or back quote work with pure Client, but with DB API I get:
unsupported format character '"' (0x22) at index 262
unsupported format character '`' (0x60) at index 358

Versions:
clickhouse-driver-0.1.5
Python 3.7.4

[xzkostyan]

Hi.

Following example works for me:

In [1]: from clickhouse_driver import connect                                                                                                                                   

In [2]: conn = connect('clickhouse://localhost')                                                                                                                                

In [3]: cur = conn.cursor()                                                                                                                                                     

In [4]: cur.execute("SELECT formatDateTime(now(), '%Y-%m-%d %Hh') AS DateTime")                                                                                                 

In [5]: cur.fetchall()                                                                                                                                                          
Out[5]: [('2020-09-26 23h',)]

Can you provide python snippet that reproduces your problem?

[ghuname]

Yes you are right. It wasn't a problem with clickhouse driver, it was a problem with jinjasql that had a problem with '%' character. After you have reported that select above works, I have done further investigation.
The first select worked:

from clickhouse_driver import connect
from jinjasql import JinjaSql
import pandas as pd

conn = connect(f'clickhouse://localhost:10001')
jsql = JinjaSql(param_style='pyformat')

def execute(sql_templ, params = None):
    params = {} if params is None else params.copy()
    query, bind_params = jsql.prepare_query(sql_templ, params)
    print(query)
    print(f'{bind_params!r}')
    return pd.read_sql_query(query, conn, params=bind_params)

df = execute("""
select ipdrCreationTime DateTime
from pnm.diag_log 
where ipdrCreationTime >= subtractHours(now(), 24)
limit 1
""")

display(df)

But the second one didn't:

execute("""
select formatDateTime(ipdrCreationTime, '%Y-%m-%d %Hh')
from pnm.diag_log 
where ipdrCreationTime >= subtractHours(now(), 24)
limit 1
""")

display(df)

Error was:

DatabaseError: Execution failed on sql '
select formatDateTime(ipdrCreationTime, '%Y-%m-%d %Hh')
from pnm.diag_log 
where ipdrCreationTime >= subtractHours(now(), 24)
limit 1': unsupported format character 'Y' (0x59) at index 43

I found out that character '%' in select above should be escaped with another '%' character (as described on sripathikrishnan/jinjasql#28 (comment))

If second select is changed to:

select formatDateTime(ipdrCreationTime, '**%%Y-%%m-%%d %%Hh**')
from pnm.diag_log 
where ipdrCreationTime >= subtractHours(now(), 24)
limit 1

everything works. I hope this will help to someone.
@xzkostyan Sorry for a false alarm.