SQL Date and Time Functions

-- Current timestamps
SELECT
    NOW()                           AS current_timestamp,
    CURRENT_DATE                    AS today,
    CURRENT_TIME                    AS time_now,
    NOW() AT TIME ZONE 'UTC'        AS utc_now;

-- Date arithmetic
SELECT
    NOW() - INTERVAL '7 days'       AS one_week_ago,
    NOW() + INTERVAL '1 month'      AS next_month,
    CURRENT_DATE - 30               AS thirty_days_ago,
    AGE(NOW(), '2024-01-01')        AS time_since;

-- Extract parts
SELECT
    EXTRACT(YEAR FROM NOW())        AS year,
    EXTRACT(MONTH FROM NOW())       AS month,
    EXTRACT(DOW FROM NOW())         AS day_of_week,
    EXTRACT(EPOCH FROM NOW())       AS unix_timestamp;

-- Truncate (for grouping)
SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS orders
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Format dates
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS') AS formatted;
SELECT TO_CHAR(NOW(), 'Mon DD, YYYY') AS pretty_date;

-- Filter by date range
SELECT * FROM events
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
  AND created_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';

-- Generate date series for gap-filling
SELECT d::date AS date, COALESCE(o.total, 0) AS orders
FROM generate_series(
    CURRENT_DATE - 30,
    CURRENT_DATE,
    '1 day'::interval
) d
LEFT JOIN (
    SELECT DATE(created_at) AS day, COUNT(*) AS total
    FROM orders GROUP BY DATE(created_at)
) o ON d::date = o.day
ORDER BY date;