-- Get current date and timestamp
SELECT
    CURRENT_DATE AS today,
    NOW() AS current_timestamp,
    CURRENT_TIMESTAMP AS also_now;

-- PostgreSQL-specific: extract with timezone
SELECT
    NOW() AS utc_now,
    NOW() AT TIME ZONE 'America/New_York' AS ny_time,
    NOW() AT TIME ZONE 'Asia/Tokyo' AS tokyo_time;

-- Extract year, month, and day from order dates
SELECT
    order_id,
    order_date,
    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
FROM orders;

-- MySQL alternative using DATE_FORMAT
SELECT
    order_id,
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    DAY(order_date) AS order_day,
    DAYOFWEEK(order_date) AS day_of_week
FROM orders;

-- PostgreSQL: format dates as readable strings
SELECT
    order_id,
    order_date,
    TO_CHAR(order_date, 'Month DD, YYYY') AS long_date,
    TO_CHAR(order_date, 'MM/DD/YYYY') AS us_format,
    TO_CHAR(order_date, 'DD-MM-YYYY HH24:MI') AS full_datetime
FROM orders;

-- MySQL: format dates using DATE_FORMAT
SELECT
    order_id,
    DATE_FORMAT(order_date, '%M %d, %Y') AS long_date,
    DATE_FORMAT(order_date, '%m/%d/%Y') AS us_format,
    DATE_FORMAT(order_date, '%d-%m-%Y %H:%i') AS full_datetime
FROM orders;

-- Add and subtract intervals from dates
SELECT
    CURRENT_DATE AS today,
    CURRENT_DATE + INTERVAL '7' DAY AS next_week,
    CURRENT_DATE - INTERVAL '30' DAY AS last_month,
    CURRENT_DATE + INTERVAL '3' MONTH AS three_months,
    CURRENT_DATE + INTERVAL '1' YEAR AS next_year;

-- MySQL equivalent using DATE_ADD and DATE_SUB
SELECT
    CURDATE() AS today,
    DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week,
    DATE_SUB(CURDATE(), INTERVAL 30 DAY) AS last_month,
    DATE_ADD(CURDATE(), INTERVAL 3 MONTH) AS three_months;

-- Calculate days between two dates
SELECT
    order_id,
    order_date,
    CURRENT_DATE - order_date AS days_since_order,
    EXTRACT(DAY FROM AGE(CURRENT_DATE, order_date)) AS days_old
FROM orders;

-- PostgreSQL: use AGE() for human-readable differences
SELECT
    employee_name,
    hire_date,
    AGE(CURRENT_DATE, hire_date) AS tenure,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) AS years_employed
FROM employees;

-- MySQL: use DATEDIFF for day counts
SELECT
    employee_name,
    hire_date,
    DATEDIFF(CURDATE(), hire_date) AS days_employed,
    DATEDIFF(CURDATE(), hire_date) / 365 AS approx_years
FROM employees;

-- Group orders by month using DATE_TRUNC
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS total_orders,
    SUM(amount) AS monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Group by quarter
SELECT
    DATE_TRUNC('quarter', order_date) AS quarter,
    COUNT(*) AS order_count,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY DATE_TRUNC('quarter', order_date)
ORDER BY quarter;

-- Classify orders by recency
SELECT
    order_id,
    order_date,
    CASE
        WHEN order_date >= CURRENT_DATE - INTERVAL '7' DAY THEN 'This Week'
        WHEN order_date >= CURRENT_DATE - INTERVAL '30' DAY THEN 'This Month'
        WHEN order_date >= CURRENT_DATE - INTERVAL '90' DAY THEN 'This Quarter'
        ELSE 'Older'
    END AS recency_bucket
FROM orders;

-- Find first and last orders per customer
SELECT
    customer_id,
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order,
    MAX(order_date) - MIN(order_date) AS customer_lifespan
FROM orders
GROUP BY customer_id;

-- Generate a monthly revenue report with growth calculation
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) * 100, 1
    ) AS growth_pct
FROM monthly
ORDER BY month;

-- Count business days between two dates (excluding weekends)
SELECT
    order_id,
    order_date,
    ship_date,
    EXTRACT(DAY FROM AGE(ship_date, order_date))
        - (EXTRACT(DOW FROM ship_date) - EXTRACT(DOW FROM order_date)) / 7
        AS approx_business_days
FROM orders
WHERE ship_date IS NOT NULL;