Dates & Time

Dates are fundamental to analytics. ASQL provides a clean, intuitive, and portable date syntax that compiles to the right SQL for any dialect.

Date Literals

Use the @ prefix for date literals:

from users
  where signup_date >= @2024-01-01

from orders
  where order_date between @2024-01-01 and @2024-12-31

This compiles to DATE '2024-01-01' in SQL.

For timestamps, include the time:

@2024-01-15T10:30:00

Time Truncation Functions

Truncate dates to a specific unit:

Function	Result	Example Output
year(date)	Truncate to year start	2025-01-01
month(date)	Truncate to month start	2025-01-01
week(date)	Truncate to week start	2025-01-06
day(date)	Truncate to day	2025-01-15
hour(date)	Truncate to hour	2025-01-15 14:00:00
quarter(date)	Truncate to quarter start	2025-01-01

ASQL Playground

from orders
  group by month(created_at) (
    sum(amount) as revenue
  )

Natural Language Alternatives

All of these are equivalent:

year(created_at)       -- function style
year created_at        -- space style
year_created_at        -- underscore style
year of created_at     -- "of" style

Date Part Extraction

Extract specific parts (returns integers, not dates):

Expression	Returns	Example
day of week date	1-7	3 (Wednesday)
day of month date	1-31	15
day of year date	1-366	45
week of year date	1-52	12
month of year date	1-12	6
quarter of year date	1-4	2

Numbering follows the database

Extraction compiles to the database's own function, so numbering can differ by dialect: week of year is the ISO week (1-53) on PostgreSQL and DuckDB, but BigQuery's EXTRACT(WEEK) counts Sunday-start weeks from 0, and Snowflake's follows its WEEK_OF_YEAR_POLICY setting. day of week likewise returns the dialect's native numbering. Use week(date) truncation when you need weeks that line up across dialects. There is no year of ... extraction form; use extract(year from date).

Comparing a truncating unit function with a number, such as year(created_at) = 2024, is a compile error because it compares a date with an integer. Write extract(year from created_at) = 2024 or filter on a date range.

-- Weekend orders
from orders
  where day of week order_date in (6, 7)

-- Sales by day of week
from sales
  group by day of week sale_date (
    sum(amount) as revenue
  )

Truncation vs Extraction

• Truncation (month(date)): Returns a date, for time series
• Extraction (month of year date): Returns an integer, for "all Januaries"

month(created_at)           -- → 2025-01-01 (date)
month of year created_at    -- → 1 (integer)

Date Arithmetic

Add or subtract from dates using clean inline syntax:

order_date + 7 days
order_date - 1 month
order_date + 2 weeks
created_at + 24 hours

Singular and plural both work:

order_date + 1 day    -- singular
order_date + 7 days   -- plural

This compiles to dialect-appropriate SQL:

-- PostgreSQL, Snowflake, Redshift
order_date + INTERVAL '7 DAY'

-- BigQuery, MySQL, DuckDB, Databricks, Trino
order_date + INTERVAL '7' DAY

Date Difference

Calculate the difference between dates:

days(end_date - start_date)      -- Integer days
months(end_date - start_date)    -- Integer months
years(end_date - start_date)     -- Integer years
hours(end_date - start_date)     -- Integer hours
weeks(end_date - start_date)     -- Integer weeks

Alternative function syntax:

days_between(start_date, end_date)
months_between(start_date, end_date)

Example:

from orders
  select
    days(shipped_date - order_date) as fulfillment_days,
    months(now() - customer_since) as customer_tenure_months

Relative Dates

Past Dates (ago)

from users
  where last_login >= 7 days ago

from orders
  where created_at >= 30 days ago
  where created_at >= 1 month ago

Future Dates (from now)

from orders
  where estimated_delivery <= 3 days from now

from reminders
  where remind_at <= 1 hour from now

These compile to dialect-appropriate SQL using CURRENT_TIMESTAMP and intervals.

Time Since/Until Functions

Calculate time elapsed since a date, or time remaining until a future date.

Three Equivalent Syntaxes

All of these are equivalent:

-- Function call (recommended)
days_since(created_at)
months_until(due_date)

-- Space notation (natural language)
days since created_at
months until due_date

-- Underscore alias (compact)
days_since_created_at
months_until_due_date

Available Functions

Since (time elapsed)	Until (time remaining)
days_since(col)	days_until(col)
weeks_since(col)	weeks_until(col)
months_since(col)	months_until(col)
years_since(col)	years_until(col)
hours_since(col)	hours_until(col)
minutes_since(col)	minutes_until(col)
seconds_since(col)	seconds_until(col)

Examples

from users
  select
    name,
    days_since(last_login),           -- Function call
    months since signup_date,          -- Space notation
    years_since_birth_date as age      -- Underscore alias

from tasks
  where days_until(due_date) < 7
  -- or: where days until due_date < 7
  -- or: where days_until_due_date < 7

Week Start Configuration

Default: ISO 8601 standard (Monday = day 1)

week(created_at)           -- Default: ISO (Monday start)
week_monday(created_at)    -- Explicit Monday start
week_sunday(created_at)    -- US-style Sunday start

day of week created_at     -- 1 = Monday, 7 = Sunday (ISO)

Time Bucketing (Grouping)

Time bucketing is simply grouping by a time function:

from orders
  group by month(created_at) (
    sum(amount) as revenue
  )

from sessions
  group by week(start_time) (
    count(distinct user_id) as active_users
  )

Timezone Handling

Use :: syntax for timezone conversion:

-- Short timezone codes
created_at::PST
created_at::UTC
created_at::EST

-- Full IANA timezone names (quoted)
created_at::"America/Los_Angeles"
created_at::"Europe/London"

-- Chained with other operations
month(created_at::PST)
created_at::UTC + 7 days

A short code names its region's zone, daylight saving included: PT/PST/PDT → America/Los_Angeles, MT/MST/MDT → America/Denver, CT/CST/CDT → America/Chicago, ET/EST/EDT → America/New_York, UTC/GMT → UTC. Any other zone needs its quoted IANA name.

This compiles to each warehouse's conversion:

-- PostgreSQL, DuckDB
created_at AT TIME ZONE 'America/Los_Angeles'

-- BigQuery
TIMESTAMP(DATETIME(created_at, 'America/Los_Angeles'))

-- Snowflake, Redshift, Databricks
CONVERT_TIMEZONE('America/Los_Angeles', created_at)

-- MySQL (DATETIME values carry no zone, so they are read as UTC)
CONVERT_TZ(created_at, 'UTC', 'America/Los_Angeles')

Quick Reference

Operation	Syntax	Example
Date literal	@YYYY-MM-DD	@2025-01-15
Truncation	unit(col)	month(created_at)
Extraction	unit of period col	day of week created_at
Add/subtract	date + N unit	order_date + 7 days
Difference	unit(date1 - date2)	days(end - start)
Relative past	N unit ago	7 days ago
Relative future	N unit from now	3 days from now
Time since	unit_since(col) or unit since col	days_since(created_at)
Time until	unit_until(col) or unit until col	days_until(due_date)
Timezone	col::TZ	created_at::PST

Real-World Examples

Daily Revenue for Last 30 Days

ASQL Playground

from orders
  where created_at >= 30 days ago
  group by day(created_at) (
    sum(amount) as revenue
  )
  order by day(created_at)

Monthly User Signups

ASQL Playground

from users
  group by month(created_at) (
    # as signups
  )
  order by month

Orders with Fulfillment Time

from orders
  where shipped_date is not null
  select
    id,
    order_date,
    shipped_date,
    days(shipped_date - order_date) as fulfillment_days

Active Users Last Week

ASQL Playground

from users
  where last_login >= 7 days ago
  select name, email, days_since_last_login

Next Steps

• Aggregations — Grouping by date periods
• Guaranteed Groups — Gap-filling for time series
• Window Functions — Prior/next for date comparisons