Date and Time functions

Usage:

formatDateTime(<datetime expression>, <format string>[, <timezone string>])

formatDateTime prints a datetime as a string according to a provided format string. Refer to ClickHouse's documentation for a list of supported formatting options.

Examples:

-- prints the current YYYY-MM-DD in UTC
formatDateTime(now(), '%Y-%m-%d')

-- prints YYYY-MM-DD in the datetime's timezone
formatDateTime(<a datetime with a timezone>, '%Y-%m-%d')
formatDateTime(toDateTime('2022-12-01 16:17:00', 'America/New_York'), '%Y-%m-%d')

-- prints YYYY-MM-DD in UTC
formatDateTime(<a datetime with a timezone>, '%Y-%m-%d', 'Etc/UTC')
formatDateTime(toDateTime('2022-12-01 16:17:00', 'America/New_York'), '%Y-%m-%d', 'Etc/UTC')

Usage:

now()

Returns the current time as a DateTime.

Usage:

today()

Returns the current date as a Date.

Usage:

toDateTime(<expression>[, 'timezone string'])

toDateTime converts an expression to a datetime. This function does not support ISO 8601-style timezones; if your time is not in UTC then you must provide the timezone using the second optional argument.

Examples:

-- double1 contains a unix timestamp in seconds
toDateTime(double1)

-- blob1 contains an datetime in the format 'YYYY-MM-DD hh:mm:ss'
toDateTime(blob1)

-- literal values:
toDateTime(355924804) -- unix timestamp
toDateTime('355924804') -- string containing unix timestamp
toDateTime('1981-04-12 12:00:04') -- string with datetime in 'YYYY-MM-DD hh:mm:ss' format

-- interpret a date relative to New York time
toDateTime('2022-12-01 16:17:00', 'America/New_York')

Usage:

toYear(<datetime>)

toYear returns the year of a datetime.

Examples:

-- returns the number 2025
toYear(toDateTime('2025-10-27 00:00:00'))

Usage:

toMonth(<datetime>)

toMonth returns the year of a datetime.

Examples:

-- returns the number 10
toMonth(toDateTime('2025-10-27 00:00:00'))

Usage:

toDayOfWeek(<datetime>)

toDayOfWeek takes a datetime and returns its numerical day of the week.

Returns 1 to indicate Monday, 2 to indicate Tuesday, and so on.

Examples:

-- returns the number 1 for Monday 27th October 2025
toDayOfWeek(toDateTime('2025-10-27 00:00:00'))

-- returns the number 2 for Tuesday 28th October 2025
toDayOfWeek(toDateTime('2025-10-28 00:00:00'))

-- returns the number 7 for Sunday 2nd November 2025
toDayOfWeek(toDateTime('2025-11-02 00:00:00'))

Usage:

toDayOfMonth(<datetime>)

toDayOfMonth returns the day of the month from a datetime.

Examples:

-- returns the number 27
toDayOfMonth(toDateTime('2025-10-27 00:00:00'))

Usage:

toHour(<datetime>)

toHour returns the hour of the day from a datetime.

Examples:

-- returns the number 9
toHour(toDateTime('2025-10-27 09:11:13'))

Usage:

toMinute(<datetime>)

toMinute returns the minute of the hour from a datetime.

Examples:

-- returns the number 11
toMinute(toDateTime('2025-10-27 09:11:13'))

Usage:

toSecond(<datetime>)

toSecond returns the second of the minute from a datetime.

Examples:

-- returns the number 13
toSecond(toDateTime('2025-10-27 09:11:13'))

Usage:

toUnixTimestamp(<datetime>)

toUnixTimestamp converts a datetime into an integer unix timestamp.

Examples:

-- get the current unix timestamp
toUnixTimestamp(now())

Usage:

toStartOfInterval(<datetime>, INTERVAL '<n>' <unit>[, <timezone string>])

toStartOfInterval rounds down a datetime to the nearest offset of a provided interval. This can be useful for grouping data into equal-sized time ranges.

Examples:

-- round the current time down to the nearest 15 minutes
toStartOfInterval(now(), INTERVAL '15' MINUTE)

-- round a timestamp down to the day
toStartOfInterval(timestamp, INTERVAL '1' DAY)

-- count the number of datapoints filed in each hourly window
SELECT
  toStartOfInterval(timestamp, INTERVAL '1' HOUR) AS hour,
  sum(_sample_interval) AS count
FROM your_dataset
GROUP BY hour
ORDER BY hour ASC

Usage:

toStartOfYear(<datetime>)

toStartOfYear rounds down a datetime to the nearest start of year. This can be useful for grouping data into equal-sized time ranges.

Examples:

-- round a timestamp down to 2025-01-01 00:00:00
toStartOfYear(toDateTime('2025-10-27 00:00:00'))

Usage:

toStartOfMonth(<datetime>)

toStartOfMonth rounds down a datetime to the nearest start of month. This can be useful for grouping data into equal-sized time ranges.

Examples:

-- round a timestamp down to 2025-10-01 00:00:00
toStartOfMonth(toDateTime('2025-10-27 00:00:00'))

Usage:

toStartOfWeek(<datetime>)

toStartOfWeek rounds down a datetime to the start of the week. This can be useful for grouping data into equal-sized time ranges.

Treats Monday as the first day of the week.

Examples:

-- round a time on a Monday down to Monday 2025-10-27 00:00:00
toStartOfWeek(toDateTime('2025-10-27 00:00:00'))

-- round a time on a Wednesday down to Monday 2025-10-27 00:00:00
toStartOfWeek(toDateTime('2025-10-29 00:00:00'))

Usage:

toStartOfDay(<datetime>)

toStartOfDay rounds down a datetime to the nearest start of day. This can be useful for grouping data into equal-sized time ranges.

Examples:

-- round a timestamp down to 2025-10-27 00:00:00
toStartOfDay(toDateTime('2025-10-27 00:00:00'))

Usage:

toStartOfHour(<datetime>)

toStartOfHour rounds down a datetime to the nearest start of hour. This can be useful for grouping data into equal-sized time ranges.

Examples:

-- round a timestamp down to 2025-10-27 16:00:00
toStartOfHour(toDateTime('2025-10-27 16:55:25'))

Usage:

toStartOfFifteenMinutes(<datetime>)

toStartOfFifteenMinutes rounds down a datetime to the nearest fifteen minutes. This can be useful for grouping data into equal-sized time ranges.

Examples:

-- round a timestamp down to 2025-10-27 16:45:00
toStartOfFifteenMinutes(toDateTime('2025-10-27 16:55:25'))

Usage:

toStartOfTenMinutes(<datetime>)

toStartOfTenMinutes rounds down a datetime to the nearest ten minutes. This can be useful for grouping data into equal-sized time ranges.

Examples:

-- round a timestamp down to 2025-10-27 16:50:00
toStartOfTenMinutes(toDateTime('2025-10-27 16:55:25'))

Usage:

toStartOfFiveMinutes(<datetime>)

toStartOfFiveMinutes rounds down a datetime to the nearest five minutes. This can be useful for grouping data into equal-sized time ranges.

Examples:

-- round a timestamp down to 2025-10-27 16:55:00
toStartOfFiveMinutes(toDateTime('2025-10-27 16:55:25'))

Usage:

toStartOfMinute(<datetime>)

toStartOfMinute rounds down a datetime to the nearest minute. This can be useful for grouping data into equal-sized time ranges.

Examples:

-- round a timestamp down to 2025-10-27 16:55:00
toStartOfMinute(toDateTime('2025-10-27 16:55:25'))

Usage:

toYYYYMM(<datetime>)

toYYYYMM returns a number representing year and month of a datetime. For instance a datetime on 2025-05-03 would return the number 202505.

Examples:

-- returns the number 202510
toYYYYMM(toDateTime('2025-10-27 16:55:25'))