Scalar Functions — DateTime

KQL has a rich set of datetime functions for bucketing, truncating, formatting, and converting timestamps. Most functions work across all five dialects. The remaining gaps are approximations where a dialect lacks a native interval/timespan type — notably totimespan() in SQLite and MySQL, and datetime_add() with non-literal amounts in SQLite.

Legend: ✅ Supported · ⚠️ Approximated · ❌ Not Supported · 🔄 Rewritten

Function	SQLite	MySQL	ClickHouse	PostgreSQL	DuckDB	Notes
`now()`	✅	✅	✅	✅	✅	SQLite: `datetime('now')`
`ago(duration)`	✅	✅	✅	✅	✅	All units supported; sub-second normalized to seconds
`datetime(val)`	✅	✅	✅	✅	✅	CAST to timestamp; MySQL: `DATETIME`
`todatetime()`	✅	✅	✅	✅	✅	CAST to timestamp; MySQL: `DATETIME`
`make_datetime()`	✅	✅	✅	✅	✅	SQLite: built via `printf`+`datetime`; Postgres/DuckDB: `make_timestamp`
`datetime_diff()`	⚠️	✅	✅	✅	✅	SQLite: `julianday`/`strftime` math — `year`/`month` are exact, but other units (notably `week`) are approximated unreliably and an unrecognized unit silently returns a day count, with no warning; DuckDB: `datediff`
`datetime_add()`	⚠️	✅	✅	✅	✅	SQLite: literal amounts only; DuckDB: `+ INTERVAL`
`datetime_part()`	⚠️	✅	✅	✅	✅	SQLite: `strftime` — only the common parts map; `quarter`, `week_of_year`, `dayofyear`, and sub-second parts are unsupported; Postgres/MySQL/DuckDB: `EXTRACT`; ClickHouse: fixed part set
`format_datetime()`	✅	✅	✅	✅	✅	Format tokens translated per dialect; DuckDB: `strftime`
`startofday()`	✅	✅	✅	✅	✅	Postgres/DuckDB: `date_trunc`
`startofmonth()`	✅	✅	✅	✅	✅
`startofweek()`	✅	✅	✅	✅	✅
`startofyear()`	✅	✅	✅	✅	✅
`endofday()`	✅	✅	✅	✅	✅	DuckDB matches Postgres (`date_trunc` + interval − 1µs)
`endofmonth()`	✅	✅	✅	✅	✅	SQLite: datetime modifier chain; Postgres/DuckDB: `date_trunc` + interval
`endofweek()`	✅	✅	✅	✅	✅	Sunday-based; Postgres/DuckDB shift `date_trunc('week')` back one day
`endofyear()`	✅	✅	✅	✅	✅	SQLite: datetime modifier chain; Postgres/DuckDB: `date_trunc` + interval; ClickHouse: composed from `toStartOfYear`
`dayofmonth()`	✅	✅	✅	✅	✅
`dayofweek()`	✅	✅	✅	✅	✅
`dayofyear()`	✅	✅	✅	✅	✅
`hourofday()`	✅	✅	✅	✅	✅
`minuteofhour()`	✅	✅	✅	✅	✅	Postgres/DuckDB: `EXTRACT(MINUTE …)`
`secondofminute()`	✅	✅	✅	✅	✅	Postgres/DuckDB: `EXTRACT(SECOND …)`
`monthofyear()`	✅	✅	✅	✅	✅	`getmonth()` is an alias
`getyear()`	✅	✅	✅	✅	✅
`weekofyear()`	✅	✅	✅	✅	✅	Postgres/DuckDB: `EXTRACT(WEEK …)`
`week_of_year()`	✅	✅	✅	✅	✅	ISO 8601 week number
`make_timespan()`	✅	✅	✅	✅	✅	Computed as total seconds via arithmetic; dialect-agnostic
`bin(col, interval)`	✅	✅	✅	✅	✅	ClickHouse: `toStartOfInterval`; DuckDB: `time_bucket`; SQLite/MySQL/Postgres: epoch-aligned floor
`bin_at()`	✅	✅	✅	⚠️	✅	Postgres: numeric form only (datetime form unsupported); ClickHouse/DuckDB pass the anchor as origin
`totimespan()`	⚠️	⚠️	✅	✅	✅	SQLite/MySQL: approximated as raw seconds with warning; Postgres/DuckDB: `CAST … AS INTERVAL`
`format_timespan()`	✅	✅	✅	✅	✅	Output fixed `hh:mm:ss`; KQL format string not reproduced (warns). Postgres/DuckDB: `CAST(INTERVAL AS VARCHAR)`; MySQL: `SEC_TO_TIME`
`unixtime_seconds_todatetime()`	✅	✅	✅	✅	✅
`unixtime_milliseconds_todatetime()`	✅	✅	✅	✅	✅	Divides by 1000; DuckDB: `epoch_ms`
`unixtime_microseconds_todatetime()`	✅	✅	✅	✅	✅	Divides by 1,000,000
`unixtime_nanoseconds_todatetime()`	✅	✅	✅	✅	✅	Divides by 1,000,000,000