How to Use Date Functions in SQLite

Key Insights

• SQLite doesn’t have a dedicated date type—dates are stored as TEXT (ISO 8601), REAL (Julian day), or INTEGER (Unix timestamp), making proper function usage critical for accurate queries • The five core functions (date(), time(), datetime(), julianday(), strftime()) combined with modifiers like ‘+1 day’ and ‘start of month’ handle virtually all date manipulation needs • Always store dates in ISO 8601 format (YYYY-MM-DD HH:MM:SS) and create indexes on date columns to maintain query performance and avoid comparison pitfalls

Understanding SQLite’s Date Storage

SQLite takes a minimalist approach to date handling. Unlike PostgreSQL or MySQL, there’s no native DATE or DATETIME type. Instead, SQLite stores dates using one of three type affinities: TEXT (as ISO 8601 strings), REAL (as Julian day numbers), or INTEGER (as Unix timestamps—seconds since 1970-01-01).

This flexibility is powerful but requires discipline. The built-in date functions expect ISO 8601 format strings, so storing dates consistently prevents conversion headaches and comparison errors. When you query WHERE created_at > '2024-01-01', SQLite performs lexicographic string comparison, which works correctly only with properly formatted ISO dates.

Core Date and Time Functions

SQLite provides five fundamental date functions that form the foundation of all date operations.

The date() function returns dates in YYYY-MM-DD format:

SELECT date('now');
-- Returns: 2024-01-15

SELECT date('2024-01-15 14:30:00');
-- Returns: 2024-01-15

SELECT date('now', 'start of month');
-- Returns: 2024-01-01

The time() function extracts time in HH:MM:SS format:

SELECT time('now');
-- Returns: 14:30:22

SELECT time('2024-01-15 14:30:00');
-- Returns: 14:30:00

The datetime() function combines both:

SELECT datetime('now');
-- Returns: 2024-01-15 14:30:22

SELECT datetime('now', 'localtime');
-- Returns: 2024-01-15 09:30:22 (if UTC-5)

The julianday() function converts dates to Julian day numbers, useful for date arithmetic:

SELECT julianday('now');
-- Returns: 2460326.60607

SELECT julianday('2024-01-15') - julianday('2024-01-01');
-- Returns: 14.0 (days between dates)

The strftime() function is the Swiss Army knife for custom formatting:

SELECT strftime('%Y', 'now');
-- Returns: 2024

SELECT strftime('%m', 'now');
-- Returns: 01

SELECT strftime('%Y-%m', 'now');
-- Returns: 2024-01

SELECT strftime('%W', 'now');
-- Returns: 03 (week number)

Date Modifiers and Calculations

Modifiers transform dates by adding, subtracting, or snapping to specific points in time. You can chain multiple modifiers together.

Adding and subtracting intervals:

SELECT date('now', '+1 day');
-- Tomorrow

SELECT date('now', '-7 days');
-- Last week

SELECT date('now', '+2 months', '-1 day');
-- Last day of month, two months from now

SELECT datetime('now', '+3 hours', '-15 minutes');
-- 2 hours 45 minutes from now

Finding month boundaries:

SELECT date('now', 'start of month');
-- Returns: 2024-01-01

SELECT date('now', 'start of month', '+1 month', '-1 day');
-- Returns: 2024-01-31 (last day of current month)

SELECT date('2024-02-15', 'start of month', '+1 month', '-1 day');
-- Returns: 2024-02-29 (handles leap years)

Working with weekdays:

SELECT date('now', 'weekday 0');
-- Next Sunday (0 = Sunday, 6 = Saturday)

SELECT date('now', 'weekday 1');
-- Next Monday

SELECT date('now', 'start of month', 'weekday 1');
-- First Monday of current month

The localtime and utc modifiers handle timezone conversions:

SELECT datetime('now');
-- Returns: 2024-01-15 14:30:22 (UTC)

SELECT datetime('now', 'localtime');
-- Returns: 2024-01-15 09:30:22 (if you're UTC-5)

SELECT datetime('now', 'localtime', '+5 hours');
-- Convert back to UTC

Practical Date Queries

Real-world applications require filtering, grouping, and calculating with dates. Here are patterns you’ll use constantly.

Finding records from the last 30 days:

SELECT * FROM orders
WHERE created_at >= date('now', '-30 days');

Records from the current month:

SELECT * FROM orders
WHERE created_at >= date('now', 'start of month')
  AND created_at < date('now', 'start of month', '+1 month');

Monthly aggregation for reporting:

SELECT 
    strftime('%Y-%m', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
WHERE created_at >= date('now', '-12 months')
GROUP BY strftime('%Y-%m', created_at)
ORDER BY month;

Finding records between specific dates:

SELECT * FROM events
WHERE event_date BETWEEN date('2024-01-01') AND date('2024-01-31');

-- More explicit and often clearer:
SELECT * FROM events
WHERE event_date >= '2024-01-01' 
  AND event_date < '2024-02-01';

Calculating age or duration:

SELECT 
    name,
    birth_date,
    CAST((julianday('now') - julianday(birth_date)) / 365.25 AS INTEGER) AS age
FROM users;

SELECT 
    task_name,
    started_at,
    completed_at,
    ROUND(julianday(completed_at) - julianday(started_at), 2) AS duration_days
FROM tasks
WHERE completed_at IS NOT NULL;

Finding upcoming events:

SELECT * FROM events
WHERE event_date >= date('now')
  AND event_date <= date('now', '+7 days')
ORDER BY event_date;

Best Practices and Patterns

Store dates in ISO 8601 format consistently. Define columns with CHECK constraints to enforce this:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    CHECK(created_at IS datetime(created_at))
);

Create indexes on date columns for query performance:

CREATE INDEX idx_orders_created_at ON orders(created_at);

-- For range queries, consider partial indexes
CREATE INDEX idx_recent_orders 
ON orders(created_at) 
WHERE created_at >= date('now', '-90 days');

Use computed columns for frequently accessed date components:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_datetime TEXT NOT NULL,
    event_date TEXT GENERATED ALWAYS AS (date(event_datetime)) STORED,
    event_month TEXT GENERATED ALWAYS AS (strftime('%Y-%m', event_datetime)) STORED
);

CREATE INDEX idx_events_month ON events(event_month);

Handle timezones explicitly. Store everything in UTC and convert for display:

-- Store in UTC
INSERT INTO events (event_datetime) 
VALUES (datetime('now'));

-- Display in local time
SELECT 
    event_name,
    datetime(event_datetime, 'localtime') AS local_time
FROM events;

Troubleshooting Common Issues

String comparison mistakes are the most common pitfall. This fails:

-- WRONG: Compares as strings, not dates
SELECT * FROM orders
WHERE created_at > '2024-1-5';  -- Missing zero-padding

Always use proper ISO format or wrap in date functions:

-- CORRECT
SELECT * FROM orders
WHERE created_at > '2024-01-05';

-- SAFER: Explicit conversion
SELECT * FROM orders
WHERE date(created_at) > date('2024-01-05');

Handle NULL dates safely:

-- Calculate days since last login, handling NULLs
SELECT 
    username,
    COALESCE(
        CAST(julianday('now') - julianday(last_login) AS INTEGER),
        -1
    ) AS days_since_login
FROM users;

Beware of timezone confusion when comparing dates:

-- If created_at stores local time but you compare with UTC 'now'
SELECT * FROM orders
WHERE created_at >= datetime('now', '-1 day');  -- Might be off by hours

-- Be explicit about timezone
SELECT * FROM orders
WHERE created_at >= datetime('now', 'localtime', '-1 day');

Watch out for month arithmetic edge cases:

SELECT date('2024-01-31', '+1 month');
-- Returns: 2024-03-02 (not 2024-02-29!)

-- Use start of month for safer calculations
SELECT date('2024-01-31', 'start of month', '+1 month');
-- Returns: 2024-02-01

SQLite’s date functions are simple but powerful. Master these patterns, store dates consistently in ISO 8601 format, and you’ll handle time-based queries with confidence. The lack of a dedicated date type is a feature, not a limitation—it gives you flexibility while the built-in functions provide all the functionality you need.