Date and Time Data Types

Learning Focus

Use this lesson to master Date and Time Data Types in DuckDB for accurate time-series and reporting queries.

Available Temporal Types

Type	Description	Example
DATE	Calendar date (no time)	2024-01-15
TIME	Time of day (no date)	14:30:00
TIMESTAMP	Date + time (no timezone)	2024-01-15 14:30:00
TIMESTAMPTZ	Date + time with timezone	2024-01-15 14:30:00+00
INTERVAL	Duration between two points	INTERVAL '3 days'

CREATE TABLE events (
    id          INTEGER,
    event_name  VARCHAR,
    event_date  DATE,
    start_time  TIME,
    created_at  TIMESTAMP,
    duration    INTERVAL
);

INSERT INTO events VALUES
    (1, 'Conference', '2024-06-15', '09:00:00', '2024-06-15 09:00:00', INTERVAL '3 hours'),
    (2, 'Workshop',   '2024-07-20', '14:00:00', '2024-07-20 14:00:00', INTERVAL '90 minutes');

Date Functions

-- Current date and time
SELECT current_date;                     -- 2024-01-15
SELECT current_time;                     -- 14:30:00
SELECT current_timestamp;                -- 2024-01-15 14:30:00
SELECT now();                            -- same as current_timestamp

-- Extracting parts
SELECT extract('year'  FROM DATE '2024-06-15');   -- 2024
SELECT extract('month' FROM DATE '2024-06-15');   -- 6
SELECT extract('day'   FROM DATE '2024-06-15');   -- 15

-- DuckDB dot notation (shorthand)
SELECT DATE '2024-06-15'.year;   -- 2024
SELECT DATE '2024-06-15'.month;  -- 6

-- Formatting
SELECT strftime(DATE '2024-06-15', '%Y-%m-%d');    -- 2024-06-15
SELECT strftime(now(), '%A, %B %d, %Y');           -- Thursday, June 15, 2024

Date Arithmetic

-- Add/subtract intervals
SELECT DATE '2024-01-01' + INTERVAL '30 days';     -- 2024-01-31
SELECT DATE '2024-06-15' - INTERVAL '1 month';     -- 2024-05-15
SELECT TIMESTAMP '2024-01-01 10:00' + INTERVAL '2 hours 30 minutes';

-- Difference between two dates
SELECT DATE '2024-06-15' - DATE '2024-01-01';      -- 166 (days)

-- Age calculation
SELECT age(DATE '1990-05-20');                      -- interval to today

Working with Timestamps

-- Parse string to timestamp
SELECT TIMESTAMP '2024-06-15 14:30:00';
SELECT strptime('15/06/2024 14:30', '%d/%m/%Y %H:%M');

-- Truncate to period
SELECT date_trunc('month', TIMESTAMP '2024-06-15 14:30:00');  -- 2024-06-01 00:00:00
SELECT date_trunc('hour',  TIMESTAMP '2024-06-15 14:30:00');  -- 2024-06-15 14:00:00
SELECT date_trunc('year',  TIMESTAMP '2024-06-15 14:30:00');  -- 2024-01-01 00:00:00

-- Convert UNIX epoch
SELECT to_timestamp(1718460000);  -- timestamp from epoch seconds
SELECT epoch(now());              -- epoch seconds from timestamp

Practical Use Cases

-- Sales by month
SELECT
    date_trunc('month', order_date) AS month,
    COUNT(*)                        AS orders,
    SUM(amount)                     AS total
FROM orders
GROUP BY month
ORDER BY month;

-- Active records in the last 30 days
SELECT * FROM events
WHERE event_date >= current_date - INTERVAL '30 days';

-- Calculate days until event
SELECT event_name, event_date - current_date AS days_until
FROM events
WHERE event_date > current_date
ORDER BY days_until;

Common Pitfalls

Pitfall	Consequence	Prevention
Storing dates as VARCHAR	Cannot use date math or truncation	Always use DATE or TIMESTAMP columns
Ignoring timezones	Inconsistent results across regions	Use TIMESTAMPTZ if timezone matters
Using MySQL DATE_FORMAT	Syntax error	Use DuckDB's strftime()

Quick Reference

SELECT current_date;
SELECT now();
SELECT extract('year' FROM now());
SELECT date_trunc('month', now());
SELECT DATE '2024-01-01' + INTERVAL '1 year';
SELECT strftime(now(), '%Y-%m-%d');

What's Next

• Next: Nested Types — LIST, STRUCT, MAP - DuckDB's powerful nested type system.
• Module Overview - Return to this module index.