--- title: Date and Time functions description: Date and time SQL functions for Analytics Engine. image: https://developers.cloudflare.com/core-services-preview.png --- > Documentation Index > Fetch the complete documentation index at: https://developers.cloudflare.com/analytics/llms.txt > Use this file to discover all available pages before exploring further. [Skip to content](#%5Ftop) # Date and Time functions ## formatDateTime Usage: ``` formatDateTime(, [, ]) ``` `formatDateTime` prints a datetime as a string according to a provided format string. Refer to [ClickHouse's documentation ↗](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions/#formatdatetime)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(, '%Y-%m-%d')formatDateTime(toDateTime('2022-12-01 16:17:00', 'America/New_York'), '%Y-%m-%d') -- prints YYYY-MM-DD in UTCformatDateTime(, '%Y-%m-%d', 'Etc/UTC')formatDateTime(toDateTime('2022-12-01 16:17:00', 'America/New_York'), '%Y-%m-%d', 'Etc/UTC') ``` ## now Usage: ``` now() ``` Returns the current time as a DateTime. ## today New Usage: ``` today() ``` Returns the current date as a `Date`. ## toDateTime Usage: ``` toDateTime([, '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') ``` ## toYear New Usage: ``` toYear() ``` `toYear` returns the year of a datetime. Examples: ``` -- returns the number 2025toYear(toDateTime('2025-10-27 00:00:00')) ``` ## toMonth New Usage: ``` toMonth() ``` `toMonth` returns the year of a datetime. Examples: ``` -- returns the number 10toMonth(toDateTime('2025-10-27 00:00:00')) ``` ## toDayOfWeek New Usage: ``` toDayOfWeek() ``` `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 2025toDayOfWeek(toDateTime('2025-10-27 00:00:00')) -- returns the number 2 for Tuesday 28th October 2025toDayOfWeek(toDateTime('2025-10-28 00:00:00')) -- returns the number 7 for Sunday 2nd November 2025toDayOfWeek(toDateTime('2025-11-02 00:00:00')) ``` ## toDayOfMonth New Usage: ``` toDayOfMonth() ``` `toDayOfMonth` returns the day of the month from a datetime. Examples: ``` -- returns the number 27toDayOfMonth(toDateTime('2025-10-27 00:00:00')) ``` ## toHour New Usage: ``` toHour() ``` `toHour` returns the hour of the day from a datetime. Examples: ``` -- returns the number 9toHour(toDateTime('2025-10-27 09:11:13')) ``` ## toMinute New Usage: ``` toMinute() ``` `toMinute` returns the minute of the hour from a datetime. Examples: ``` -- returns the number 11toMinute(toDateTime('2025-10-27 09:11:13')) ``` ## toSecond New Usage: ``` toSecond() ``` `toSecond` returns the second of the minute from a datetime. Examples: ``` -- returns the number 13toSecond(toDateTime('2025-10-27 09:11:13')) ``` ## toUnixTimestamp Usage: ``` toUnixTimestamp() ``` `toUnixTimestamp` converts a datetime into an integer unix timestamp. Examples: ``` -- get the current unix timestamptoUnixTimestamp(now()) ``` ## toStartOfInterval Usage: ``` toStartOfInterval(, INTERVAL '' [, ]) ``` `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 ``` ## toStartOfYear New Usage: ``` toStartOfYear() ``` `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:00toStartOfYear(toDateTime('2025-10-27 00:00:00')) ``` ## toStartOfMonth New Usage: ``` toStartOfMonth() ``` `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:00toStartOfMonth(toDateTime('2025-10-27 00:00:00')) ``` ## toStartOfWeek New Usage: ``` toStartOfWeek() ``` `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:00toStartOfWeek(toDateTime('2025-10-27 00:00:00')) -- round a time on a Wednesday down to Monday 2025-10-27 00:00:00toStartOfWeek(toDateTime('2025-10-29 00:00:00')) ``` ## toStartOfDay New Usage: ``` toStartOfDay() ``` `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:00toStartOfDay(toDateTime('2025-10-27 00:00:00')) ``` ## toStartOfHour New Usage: ``` toStartOfHour() ``` `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:00toStartOfHour(toDateTime('2025-10-27 16:55:25')) ``` ## toStartOfFifteenMinutes New Usage: ``` toStartOfFifteenMinutes() ``` `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:00toStartOfFifteenMinutes(toDateTime('2025-10-27 16:55:25')) ``` ## toStartOfTenMinutes New Usage: ``` toStartOfTenMinutes() ``` `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:00toStartOfTenMinutes(toDateTime('2025-10-27 16:55:25')) ``` ## toStartOfFiveMinutes New Usage: ``` toStartOfFiveMinutes() ``` `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:00toStartOfFiveMinutes(toDateTime('2025-10-27 16:55:25')) ``` ## toStartOfMinute New Usage: ``` toStartOfMinute() ``` `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:00toStartOfMinute(toDateTime('2025-10-27 16:55:25')) ``` ## toYYYYMM New Usage: ``` toYYYYMM() ``` `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 202510toYYYYMM(toDateTime('2025-10-27 16:55:25')) ``` ```json {"@context":"https://schema.org","@type":"TechArticle","@id":"https://developers.cloudflare.com/analytics/analytics-engine/sql-reference/date-time-functions/#page","headline":"SQL Reference · Cloudflare Analytics docs","description":"Date and time SQL functions for Analytics Engine.","url":"https://developers.cloudflare.com/analytics/analytics-engine/sql-reference/date-time-functions/","inLanguage":"en","image":"https://developers.cloudflare.com/core-services-preview.png","dateModified":"2026-04-23","publisher":{"@type":"Organization","name":"Cloudflare","url":"https://www.cloudflare.com/"},"isPartOf":{"@type":"WebSite","@id":"https://developers.cloudflare.com/#website","name":"Cloudflare Docs","url":"https://developers.cloudflare.com/"}} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"item":{"@id":"/directory/","name":"Directory"}},{"@type":"ListItem","position":2,"item":{"@id":"/analytics/","name":"Analytics"}},{"@type":"ListItem","position":3,"item":{"@id":"/analytics/analytics-engine/","name":"Workers Analytics Engine"}},{"@type":"ListItem","position":4,"item":{"@id":"/analytics/analytics-engine/sql-reference/","name":"SQL Reference"}},{"@type":"ListItem","position":5,"item":{"@id":"/analytics/analytics-engine/sql-reference/date-time-functions/","name":"Date and Time functions"}}]} ```