How to Format Dates in ClickHouse

How do I format DateTime columns in ClickHouse?

ClickHouse formats Date or DateTime values with the formatDateTime() function, letting you output custom strings using strftime-style tokens and optional timezone.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why format dates in ClickHouse?

Readable dates simplify reports and API responses. formatDateTime() converts Date or DateTime columns into any string pattern, avoiding extra parsing in application code.

What is the syntax of formatDateTime()?

Use formatDateTime(datetime_expression, 'pattern' [, 'timezone']). The pattern follows strftime tokens such as %Y (year) or %d (day). Add an optional IANA timezone like 'UTC' or 'America/New_York'.

How do I format order_date as "YYYY-MM-DD"?

SELECT formatDateTime(order_date, '%Y-%m-%d') AS order_day, total_amount FROM Orders ORDER BY order_day;

The query returns 2024-06-01-style strings, perfect for grouping or CSV exports.

How do I add time and timezone?

SELECT formatDateTime(order_date, '%Y-%m-%d %H:%M:%S', 'America/Los_Angeles') AS local_time FROM Orders WHERE id = 42;

The third argument converts the stored UTC timestamp to Pacific Time before formatting.

Which conversion specifiers can I use?

Common tokens include %Y year, %m month, %d day, %H hour, %M minute, %S second, %F ISO date, and %R 24-hour time. Combine them for flexible outputs.

Best practices for date formatting

Store timestamps in UTC, apply timezone only when displaying. Keep patterns consistent across dashboards. Cache formatted results for heavy reports, or create a MATERIALIZED VIEW with pre-formatted columns.

Why How to Format Dates in ClickHouse is important

How to Format Dates in ClickHouse Example Usage


-- Show daily revenue with formatted date
SELECT
    formatDateTime(order_date, '%Y-%m-%d') AS order_day,
    sum(total_amount)                   AS daily_revenue
FROM Orders
GROUP BY order_day
ORDER BY order_day;

How to Format Dates in ClickHouse Syntax


formatDateTime(datetime_expression, 'pattern' [, 'timezone'])

-- Format customer signup date as ISO string
SELECT formatDateTime(created_at, '%F') AS signup_day
FROM Customers;

-- Format order timestamp with time and PST timezone
SELECT formatDateTime(order_date, '%F %R', 'America/Los_Angeles') AS order_time_pst
FROM Orders;

Common Mistakes

Using MySQL-style tokens like %Y-%m-%d %h:%i:%s. ClickHouse follows strftime, so minutes are %M, not %i. Fix by consulting ClickHouse token list.
Omitting the timezone argument on stored UTC data and seeing wrong local times. Always pass the correct IANA timezone or convert in application code.