TypeScript SDKQuery Builder

Time Functions

Work with dates, timestamps, and time intervals

hypequery provides powerful time-based functions for working with dates, timestamps, and time intervals. These are essential for analytics, reporting, and time-series data.

All helpers shown on this page are public exports from @hypequery/clickhouse.

Overview

Use time functions to:

  • Convert and format timestamps
  • Extract date components (year, month, day, etc.)
  • Group data by time intervals
  • Build time-series analytics

Query builder syntax

These examples use a typed standalone db client so the query builder stays the focus.

Time Conversion Functions

toDateTime

Convert values to ClickHouse DateTime objects:

const events = await db.table('events')
  .select([
    'id',
    toDateTime('created_at', 'event_ts'),
  ])
  .execute();

DateTime vs DateTime64

Use toDateTime() for standard timestamps (second precision). For microsecond precision, use DateTime64 columns directly in your schema.

formatDateTime

Format timestamps with custom format strings:

const events = await db.table('events')
  .select([
    'id',
    formatDateTime('created_at', 'Y-MM-dd HH:00', {
      alias: 'hour_bucket',
    }),
  ])
  .groupBy(['hour_bucket'])
  .execute();

Format Codes

CodeDescriptionExample
Y4-digit year2024
y2-digit year24
MMonth number01-12
MMMonth number with zero01, 12
dDay of month1-31
ddDay of month with zero01, 31
HHour (24-hour)0-23
HHHour with zero00, 23
mMinute0-59
mmMinute with zero00, 59
sSecond0-59
ssSecond with zero00, 59

Format Examples

// ISO date format
formatDateTime('created_at', 'Y-MM-dd', { alias: 'date' })

// Hour of day
formatDateTime('created_at', 'Y-MM-dd HH:00', { alias: 'hour' })

// Custom readable format
formatDateTime('created_at', 'Y-MM-dd HH:mm:ss', { alias: 'formatted' })

// Month
formatDateTime('created_at', 'Y-MM', { alias: 'month' })

Date Component Extraction

datePart

Extract specific components from a timestamp:

const events = await db.table('events')
  .select([
    datePart('year', 'created_at', 'year'),
    datePart('month', 'created_at', 'month'),
    datePart('day', 'created_at', 'day'),
    datePart('hour', 'created_at', 'hour'),
    datePart('minute', 'created_at', 'minute'),
  ])
  .execute();

DatePart Options

PartDescriptionRange
yearYear0000-9999
quarterQuarter of year1-4
monthMonth number1-12
weekWeek number1-53
dayDay of month1-31
hourHour of day0-23
minuteMinute of hour0-59
secondSecond of minute0-59

DatePart Examples

// Year-over-year comparison
const yearlyStats = await db.table('orders')
  .select([
    datePart('year', 'created_at', 'year'),
  ])
  .sum('total', 'revenue')
  .groupBy(['year'])
  .orderBy('year', 'ASC')
  .execute();

// Day of week analysis
const dailyStats = await db.table('events')
  .select([
    datePart('day', 'created_at', 'day_of_month'),
  ])
  .count('id', 'event_count')
  .groupBy(['day_of_month'])
  .orderBy('day_of_month', 'ASC')
  .execute();

Time Interval Functions

toStartOfInterval

Truncate timestamps to specific intervals:

const events = await db.table('events')
  .select([
    'id',
    toStartOfInterval('created_at', '1 hour', 'hour_bucket'),
    toStartOfInterval('created_at', '15 minute', 'fifteen_min_bucket'),
  ])
  .groupBy(['hour_bucket', 'fifteen_min_bucket'])
  .execute();

Interval Formats

IntervalDescription
1 minuteTruncate to minute
5 minuteTruncate to 5-minute window
15 minuteTruncate to 15-minute window
1 hourTruncate to hour
6 hourTruncate to 6-hour window
1 dayTruncate to day
1 weekTruncate to week
1 monthTruncate to month
1 quarterTruncate to quarter
1 yearTruncate to year

Built-in start-of helpers

If you already know the exact bucket you want, you can import the built-in helpers directly instead of using a string interval:

import {
  toStartOfMinute,
  toStartOfHour,
  toStartOfDay,
  toStartOfWeek,
  toStartOfMonth,
  toStartOfQuarter,
  toStartOfYear,
} from '@hypequery/clickhouse';

const events = await db.table('events')
  .select([
    toStartOfHour('created_at', 'hour_start'),
    toStartOfDay('created_at', 'day_start'),
    toStartOfMonth('created_at', 'month_start'),
  ])
  .execute();

Use these when you want a direct helper for a standard ClickHouse bucket without spelling out an interval string.

Group By Time Intervals

Built-in Intervals

groupByTimeInterval() supports ClickHouse's built-in toStartOf* bucketing methods. The builder still expects a string interval argument, even when the selected method does not use it directly:

const events = await db.table('events')
  .count('id', 'event_count')
  .groupByTimeInterval('created_at', '1 minute', 'toStartOfMinute')
  .execute();

Built-in Interval Options

FunctionDescription
toStartOfMinuteTruncate to minute
toStartOfHourTruncate to hour
toStartOfDayTruncate to day
toStartOfWeekTruncate to week (Monday)
toStartOfMonthTruncate to month
toStartOfQuarterTruncate to quarter
toStartOfYearTruncate to year

Custom Intervals

const events = await db.table('events')
  .count('id', 'event_count')
  .groupByTimeInterval('created_at', '5 minute')
  .execute();

Common Patterns

Daily Active Users

const dailyActive = await db.table('events')
  .select([
    toStartOfInterval('created_at', '1 day', 'date'),
  ])
  .countDistinct('user_id', 'daily_active_users')
  .groupBy(['date'])
  .orderBy('date', 'ASC')
  .execute();

Hourly Event Counts

const hourlyEvents = await db.table('events')
  .select([
    toStartOfInterval('created_at', '1 hour', 'hour'),
  ])
  .count('id', 'event_count')
  .groupBy(['hour'])
  .orderBy('hour', 'ASC')
  .execute();

Monthly Revenue

const monthlyRevenue = await db.table('orders')
  .select([
    toStartOfInterval('created_at', '1 month', 'month'),
  ])
  .sum('total', 'revenue')
  .groupBy(['month'])
  .orderBy('month', 'ASC')
  .execute();

Weekly Cohort Analysis

const cohorts = await db.table('users')
  .select([
    toStartOfInterval('created_at', '1 week', 'cohort_week'),
  ])
  .count('id', 'new_users')
  .groupBy(['cohort_week'])
  .orderBy('cohort_week', 'ASC')
  .execute();

Events per 15-Minute Bucket

const buckets = await db.table('events')
  .select([
    toStartOfInterval('created_at', '15 minute', 'time_bucket'),
  ])
  .count('id', 'event_count')
  .groupBy(['time_bucket'])
  .orderBy('time_bucket', 'ASC')
  .execute();

Time-Based Filtering

Date Range

const recentEvents = await db.table('events')
  .where('created_at', 'gte', '2024-01-01')
  .where('created_at', 'lt', '2024-02-01')
  .select(['id', 'type'])
  .execute();

Last N Days

const last7Days = await db.table('events')
  .where('created_at', 'gte', selectExpr('today() - 7'))
  .select(['id', 'type', 'created_at'])
  .execute();

Time of Day Filtering

const businessHours = await db.table('events')
  .where(datePart('hour', 'created_at'), 'gte', 9)
  .where(datePart('hour', 'created_at'), 'lt', 17)
  .select(['id', 'created_at'])
  .execute();

Time Zone Support

formatDateTime with Timezone

const events = await db.table('events')
  .select([
    formatDateTime('created_at', 'Y-MM-dd HH:mm:ss', {
      timezone: 'America/New_York',
      alias: 'est_time',
    }),
  ])
  .execute();

Convert Timezone

const events = await db.table('events')
  .select([
    'id',
    toDateTime('created_at', 'utc_time'),
    formatDateTime('created_at', 'Y-MM-dd HH:mm:ss', {
      timezone: 'Europe/London',
      alias: 'local_time',
    }),
  ])
  .execute();

Time-Series Analytics

Funnel by Time Period

const funnel = await db.table('events')
  .select([
    toStartOfInterval('created_at', '1 day', 'date'),
    'event_type',
  ])
  .countDistinct('user_id', 'unique_users')
  .groupBy(['date', 'event_type'])
  .orderBy(['date', 'event_type'], 'ASC')
  .execute();

Moving Average

const movingAvg = await db.table('events')
  .select([
    toStartOfInterval('created_at', '1 day', 'date'),
    selectExpr('avg(count) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)', 'moving_avg'),
  ])
  .count('id', 'count')
  .groupBy(['date'])
  .orderBy('date', 'ASC')
  .execute();

Year-over-Year Comparison

const yoy = await db.table('orders')
  .select([
    datePart('year', 'created_at', 'year'),
    datePart('month', 'created_at', 'month'),
  ])
  .sum('total', 'revenue')
  .groupBy(['year', 'month'])
  .orderBy(['year', 'month'], 'ASC')
  .execute();

Performance Tips

Time Query Performance

  • Prefer toStartOfInterval(...) or groupByTimeInterval(...) for explicit buckets
  • Consider materialized views for common time-based aggregations
  • Filter by time ranges before grouping when possible
  • Use appropriate partitioning by time for large tables

Filter Before Grouping

// ✅ Better - filter first
const stats = await db.table('events')
  .where('created_at', 'gte', '2024-01-01')
  .select([
    toStartOfInterval('created_at', '1 day', 'date'),
  ])
  .count('id', 'event_count')
  .groupBy(['date'])
  .execute();

// ❌ Slower - group everything
const stats = await db.table('events')
  .select([
    toStartOfInterval('created_at', '1 day', 'date'),
  ])
  .count('id', 'event_count')
  .groupBy(['date'])
  .having('date >= toDateTime(\'2024-01-01\')')
  .execute();

Examples

Website Traffic by Hour

const hourlyTraffic = await db.table('page_views')
  .select([
    toStartOfInterval('created_at', '1 hour', 'hour'),
  ])
  .count('id', 'page_views')
  .countDistinct('user_id', 'unique_visitors')
  .groupBy(['hour'])
  .orderBy('hour', 'ASC')
  .execute();
const signups = await db.table('users')
  .select([
    toStartOfInterval('created_at', '1 week', 'week'),
  ])
  .count('id', 'new_signups')
  .groupBy(['week'])
  .orderBy('week', 'ASC')
  .execute();

Custom Reporting Period

const reportPeriod = await db.table('orders')
  .select([
    formatDateTime('created_at', 'Y-MM', { alias: 'period' }),
  ])
  .sum('total', 'revenue')
  .count('id', 'order_count')
  .groupBy(['period'])
  .orderBy('period', 'ASC')
  .execute();

On this page

Get early access to hypequery cloud.

Cloud hosts the same model you run in your backend, with a managed API and MCP server, versions, and rollback. We're letting teams in a few at a time. Join the waitlist and we'll reach out when your project can go live.

How do you want to build your analytics layer? (Optional)
What features do you most need? (Optional)

Pick as many as you like.

Only your email is required. All other questions are optional.