Shape / columns · All operations

Round a date down to a unit

A variant of Parse a date or time

Truncate or bucket a date or time to the start of a unit or a fixed interval.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Round a date down to the start of a unit or a fixed interval

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT order_id, date_trunc('month', order_date)::DATE AS month_start, date_trunc('week', order_date)::DATE AS week_start,
       time_bucket(INTERVAL 15 MINUTE, TIMESTAMP '2026-02-10 14:37:12') AS quarter_hour
FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT [month_start::VARCHAR, week_start::VARCHAR, quarter_hour::VARCHAR] FROM result WHERE order_id = 10) = ['2026-02-01', '2026-02-09', '2026-02-10 14:30:00'] AND (SELECT count(DISTINCT month_start) FROM result) = 2 AND (SELECT count(DISTINCT week_start) FROM result) = 6) THEN 'as expected' ELSE error('dates were not rounded down to the month, the Monday of the week and the quarter hour') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “dates were not rounded down to the month, the Monday of the week and the quarter hour”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Duckle (2)
  • Time Binread 2026-09-25

    round timestamps down to fixed-interval buckets

  • Truncateread 2026-09-25

    truncate a timestamp to a unit

Ibis (4)
  • DateTruncateread 2026-09-27

    Truncate a date to a unit such as month or year

  • TimeTruncateread 2026-09-27

    Truncate a time to a unit such as hour or minute

  • TimestampBucketread 2026-09-27

    Floor a timestamp to buckets of a fixed interval, with an optional offset

  • TimestampTruncateread 2026-09-27

    Truncate a timestamp to a unit such as day or hour

KNIME Analytics Platform (2)
  • Date Rounderread 2026-09-27

    Round dates to a granularity such as start of month, quarter or year

  • Time Rounderread 2026-09-27

    Round times to a chosen granularity

Background

In PostgreSQL, date_trunc truncates a timestamp to a chosen precision, setting every less significant field to zero, or to one for day and month.[1] In PostgreSQL, date_trunc('hour', timestamp '2001-02-16 20:38:40') returns 2001-02-16 20:00:00.[1] In DuckDB, time_bucket truncates a timestamp to a grid of width bucket_width.[2]

When to use it

PostgreSQL's date_bin gives the same result as date_trunc for full units such as 1 hour, but can also truncate to an arbitrary interval.[1] PostgreSQL's date_bin does not accept a stride containing units of a month or larger.[1] In DuckDB, time_bucket anchors its grid at 2000-01-03, a Monday, for widths below a month, and at 2000-01-01 for a month or coarser, unless an origin or offset is given.[2] Give an explicit origin whenever buckets must line up with a business calendar, so their edges fall where readers expect. (Meridian's view)

In PostgreSQL, truncating a timestamp with time zone is done with respect to a time zone, so truncation to day gives midnight in that zone, by default the session's TimeZone setting.[1]

In DuckDB, date_trunc always returns a TIMESTAMP, even when the input is a DATE.[3] Cast the result back to a date when truncating dates, so joins against date columns compare like with like. (Meridian's view)

The method

Temporal binning breaks the range of possible instants into contiguous bins, such as years, months and days, and maps each value to the bin it falls into.[4] PostgreSQL's date_trunc is conceptually similar to the trunc function for numbers.[1]

In DuckDB, time_bucket(INTERVAL '10 minutes', TIMESTAMP '1992-04-20 15:26:00-07', INTERVAL '5 minutes') shifts the grid by five minutes and returns 1992-04-20 15:25:00.[2]

Sources

  1. [1]PostgreSQL: Documentation: 18: 9.9. Date/Time Functions and Operatorsdocumentation
  2. [2]Timestamp Functions – DuckDBdocumentation
  3. [3]Date Functions – DuckDBdocumentation
  4. [4]Timestamp Types – DuckDBdocumentation