ClickHouse Date Functions in TypeScript — toStartOfDay, toStartOfQuarter, and More

ClickHouse date functions are central to real analytics work, but the TypeScript failure mode is usually not the SQL itself. It is forgetting what comes back over HTTP. This post covers the main bucketing functions, what they return, and how to use them in hypequery without hiding the string-based return types.

Return Types: Strings, Not JS Date Objects

Before the function list — the single most important thing to understand when using ClickHouse date functions in TypeScript: they return strings to the client, not JavaScript Date objects.

• DateTime results come back as "YYYY-MM-DD HH:MM:SS" — e.g. "2026-04-25 14:00:00"
• Date results come back as "YYYY-MM-DD" — e.g. "2026-04-25"

hypequery's schema generator reflects this: DateTime columns are typed as string, Date columns as string. There is no automatic coercion. If you need a JS Date object, parse it explicitly:

The Date Bucketing Functions

| Function | Input | Output | Rounds To | |----------|-------|--------|-----------| | toStartOfMinute(dt) | DateTime | DateTime | Start of the minute | | toStartOfFiveMinutes(dt) | DateTime | DateTime | Start of the 5-minute block | | toStartOfTenMinutes(dt) | DateTime | DateTime | Start of the 10-minute block | | toStartOfFifteenMinutes(dt) | DateTime | DateTime | Start of the 15-minute block | | toStartOfHour(dt) | DateTime | DateTime | Start of the hour | | toStartOfDay(dt) | DateTime/Date | Date | Start of the day | | toStartOfWeek(dt[, mode]) | DateTime/Date | Date | Start of the week (Sunday=0 by default) | | toStartOfMonth(dt) | DateTime/Date | Date | First day of the month | | toStartOfQuarter(dt) | DateTime/Date | Date | First day of the quarter (Jan 1, Apr 1, Jul 1, Oct 1) | | toStartOfYear(dt) | DateTime/Date | Date | January 1st of the year | | toStartOfInterval(dt, INTERVAL n unit) | DateTime/Date | DateTime/Date | Start of any interval |

Note that toStartOfDay and below return Date not DateTime — the string format changes from "YYYY-MM-DD HH:MM:SS" to "YYYY-MM-DD".

toStartOfQuarter

toStartOfQuarter is the function that brings the most questions, since "quarter" boundaries aren't immediately obvious. ClickHouse uses calendar quarters:

• Q1: January 1 – March 31 → bucketed to 2026-01-01
• Q2: April 1 – June 30 → bucketed to 2026-04-01
• Q3: July 1 – September 30 → bucketed to 2026-07-01
• Q4: October 1 – December 31 → bucketed to 2026-10-01

Result rows: { quarter: "2026-01-01", total_revenue: "482910" } — the quarter is represented by its first day as a Date string.

SQL Examples with GROUP BY Alias

ClickHouse allows GROUP BY to reference aliases defined in SELECT. All of these work:

Using Date Functions in hypequery

Pass the function call as a string expression in .select():

A Reusable Granularity Helper

Dashboards typically let users pick a time granularity. Here's a helper that maps a granularity option to the right ClickHouse function, with TypeScript types for the result:

Usage:

toStartOfWeek and the Mode Parameter

toStartOfWeek takes an optional second parameter for the week start day:

If your application uses ISO weeks (Monday start), pass 1 as the second argument. The string you get back is the date of the week's first day.

toStartOfInterval: The Flexible Option

When none of the fixed-granularity functions fit — say you need 6-hour blocks or 2-week periods — toStartOfInterval handles it:

See ClickHouse toStartOfInterval with GROUP BY in TypeScript for a deeper treatment of this function including the alias rules and gap filling.

---

Related: ClickHouse Time-Series Patterns · toStartOfInterval with GROUP BY · ClickHouse TypeScript Guide