Date and time operations are critical for analytics. Here's how to handle them correctly across different SQL databases.

Getting Current Date/Time

-- Current date (no time)
SELECT CURRENT_DATE;

-- Current timestamp with time
SELECT CURRENT_TIMESTAMP;
SELECT NOW();  -- PostgreSQL, MySQL

-- Current time only
SELECT CURRENT_TIME;

Extracting Date Parts

-- Extract year, month, day
SELECT
    EXTRACT(YEAR FROM order_date) as order_year,
    EXTRACT(MONTH FROM order_date) as order_month,
    EXTRACT(DAY FROM order_date) as order_day,
    EXTRACT(DOW FROM order_date) as day_of_week  -- 0=Sunday
FROM orders;

-- Alternative syntax
SELECT
    DATE_PART('year', order_date) as order_year,
    DATE_PART('quarter', order_date) as order_quarter
FROM orders;

Date Arithmetic

Add/Subtract Time

-- PostgreSQL
SELECT order_date + INTERVAL '30 days' as due_date
FROM orders;

-- MySQL
SELECT DATE_ADD(order_date, INTERVAL 30 DAY) as due_date
FROM orders;

-- SQL Server
SELECT DATEADD(day, 30, order_date) as due_date
FROM orders;

Calculate Differences

-- Days between dates
SELECT order_date, ship_date,
    ship_date - order_date as days_to_ship  -- PostgreSQL
FROM orders;

-- MySQL
SELECT DATEDIFF(ship_date, order_date) as days_to_ship
FROM orders;

-- Age/interval
SELECT user_id,
    AGE(CURRENT_DATE, birth_date) as age  -- PostgreSQL
FROM users;

Truncating Dates

-- Group by month
SELECT
    DATE_TRUNC('month', order_date) as order_month,
    COUNT(*) as order_count,
    SUM(total) as monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

-- Available: year, quarter, month, week, day, hour

Formatting Dates

-- PostgreSQL
SELECT TO_CHAR(order_date, 'YYYY-MM-DD') as formatted_date,
       TO_CHAR(order_date, 'Mon DD, YYYY') as readable_date
FROM orders;

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') as formatted_date
FROM orders;

-- SQL Server
SELECT FORMAT(order_date, 'yyyy-MM-dd') as formatted_date
FROM orders;

Practical Examples

Find Records from Last 30 Days

SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

Calculate Retention Rate

SELECT
    DATE_TRUNC('month', first_order) as cohort_month,
    COUNT(DISTINCT user_id) as cohort_size,
    COUNT(DISTINCT CASE
        WHEN second_order <= first_order + INTERVAL '30 days'
        THEN user_id
    END) as retained_users
FROM user_orders
GROUP BY cohort_month;

Handle Timezones

-- Convert to specific timezone
SELECT order_timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' as ny_time
FROM orders;

-- Store in UTC, display in local time
SELECT
    created_at as utc_time,
    created_at AT TIME ZONE 'America/Los_Angeles' as pst_time
FROM events;