Date & Time Functions

-- Current date/time
SELECT date('now');           -- 2024-01-15
SELECT datetime('now');       -- 2024-01-15 14:30:00
SELECT time('now');           -- 14:30:00

-- Date arithmetic
SELECT date('now', '+7 days');
SELECT date('now', '-1 month');
SELECT date('now', 'start of month');
SELECT date('now', 'weekday 0');  -- Next Sunday

-- Extract parts
SELECT strftime('%Y', created_at) AS year;
SELECT strftime('%m', created_at) AS month;
SELECT strftime('%d', created_at) AS day;
SELECT strftime('%W', created_at) AS week_num;

-- Current date/time
SELECT CURRENT_DATE;
SELECT CURRENT_TIMESTAMP;
SELECT NOW();

-- Date arithmetic
SELECT NOW() + INTERVAL '7 days';
SELECT NOW() - INTERVAL '1 month';
SELECT DATE_TRUNC('month', NOW());

-- Extract parts
SELECT EXTRACT(YEAR FROM created_at);
SELECT EXTRACT(MONTH FROM created_at);
SELECT EXTRACT(DOW FROM created_at);  -- Day of week

-- Orders from last 7 days (SQLite)
SELECT * FROM orders
WHERE created_at >= date('now', '-7 days');

-- Orders from last 7 days (PostgreSQL)
SELECT * FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days';

-- Group by month (SQLite)
SELECT strftime('%Y-%m', created_at) AS month, COUNT(*)
FROM orders
GROUP BY strftime('%Y-%m', created_at);

-- Group by month (PostgreSQL)
SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*)
FROM orders
GROUP BY DATE_TRUNC('month', created_at);