Druid - How to escape character in TIME_FORMAT function

Question

I am testing a sql on Druid console Query. In my result, I'd like to change the __time to be formatted as YYYY/MM/DDThh (e.g. 2022/08/24T12) but I cannot find the way to escape T in the format string, and get Error: Unknown exception

My sql:

SELECT
  TIME_FORMAT(__time, 'YYYY/MM/ddThh') AS "hour",
  SUM(cost)
FROM my_table
WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '3' DAY
GROUP BY TIME_FORMAT(__time, 'YYYY/MM/ddThh')

I've tried several other common escape word: /T "T" but all got the same error, and I can't find a documentation for how to do so.

What will be the correct way to do this? Thanks!

Answer 1

With that query:

SELECT __time, 
      TIME_FORMAT(__time, 'YMDD''T''KK') as formatted
FROM "csv-short-v4"

I obtained the record you asked for.

You can find in the documentation more useful information about the SQL implementation for druid:

• SQL Scalar Functions
• Date Format Class accepted by Druid