Date and Time functions
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 secondstoDateTime(double1)
-- blob1 contains an datetime in the format 'YYYY-MM-DD hh:mm:ss'toDateTime(blob1)
-- literal values:toDateTime(355924804) -- unix timestamptoDateTime('355924804') -- string containing unix timestamptoDateTime('1981-04-12 12:00:04') -- string with datetime in 'YYYY-MM-DD hh:mm:ss' format
-- interpret a date relative to New York timetoDateTime('2022-12-01 16:17:00', 'America/New_York')
Usage:
now()
Returns the current time as a DateTime.
Usage:
toUnixTimestamp(<datetime>)
toUnixTimestamp
converts a datetime into an integer unix timestamp.
Examples:
-- get the current unix timestamptoUnixTimestamp(now())
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 UTCformatDateTime(now(), '%Y-%m-%d')
-- prints YYYY-MM-DD in the datetime's timezoneformatDateTime(<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 UTCformatDateTime(<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:
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 minutestoStartOfInterval(now(), INTERVAL '15' MINUTE)
-- round a timestamp down to the daytoStartOfInterval(timestamp, INTERVAL '1' DAY)
-- count the number of datapoints filed in each hourly windowSELECT toStartOfInterval(timestamp, INTERVAL '1' HOUR) AS hour, sum(_sample_interval) AS countFROM your_datasetGROUP BY hourORDER BY hour ASC
Was this helpful?
- Resources
- API
- New to Cloudflare?
- Directory
- Sponsorships
- Open Source
- Support
- Help Center
- System Status
- Compliance
- GDPR
- Company
- cloudflare.com
- Our team
- Careers
- © 2025 Cloudflare, Inc.
- Privacy Policy
- Terms of Use
- Report Security Issues
- Trademark
-