Skip to content

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 
-- 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
order_date + INTERVAL '7 days'

-- MySQL
DATE_ADD(order_date, INTERVAL 7 DAY)

-- SQL Server
DATEADD(day, 7, order_date)

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

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