Lesson 11: Date and Time Functions

-- Today's date
SELECT DATE('now') AS today;

-- Today's date
SELECT CURDATE() AS today;

-- Today's date
SELECT CURRENT_DATE AS today;

-- Last 30 days orders
SELECT order_number, ordered_at, total_amount
FROM orders
WHERE ordered_at >= DATE('now', '-30 days')
ORDER BY ordered_at DESC
LIMIT 5;

-- Last 30 days orders
SELECT order_number, ordered_at, total_amount
FROM orders
WHERE ordered_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
ORDER BY ordered_at DESC
LIMIT 5;

-- Last 30 days orders
SELECT order_number, ordered_at, total_amount
FROM orders
WHERE ordered_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY ordered_at DESC
LIMIT 5;

-- Day of week analysis (0=Sunday, 6=Saturday)
SELECT
    CASE CAST(strftime('%w', ordered_at) AS INTEGER)
        WHEN 0 THEN '일요일'
        WHEN 1 THEN '월요일'
        WHEN 2 THEN '화요일'
        WHEN 3 THEN '수요일'
        WHEN 4 THEN '목요일'
        WHEN 5 THEN '금요일'
        WHEN 6 THEN '토요일'
    END AS day_of_week,
    COUNT(*) AS order_count
FROM orders
GROUP BY strftime('%w', ordered_at)
ORDER BY CAST(strftime('%w', ordered_at) AS INTEGER);

-- Day of week analysis (1=Sunday, 7=Saturday)
SELECT
    CASE DAYOFWEEK(ordered_at)
        WHEN 1 THEN '일요일'
        WHEN 2 THEN '월요일'
        WHEN 3 THEN '화요일'
        WHEN 4 THEN '수요일'
        WHEN 5 THEN '목요일'
        WHEN 6 THEN '금요일'
        WHEN 7 THEN '토요일'
    END AS day_of_week,
    COUNT(*) AS order_count
FROM orders
GROUP BY DAYOFWEEK(ordered_at)
ORDER BY DAYOFWEEK(ordered_at);

-- Day of week analysis (0=Sunday, 6=Saturday)
SELECT
    CASE EXTRACT(DOW FROM ordered_at::date)
        WHEN 0 THEN '일요일'
        WHEN 1 THEN '월요일'
        WHEN 2 THEN '화요일'
        WHEN 3 THEN '수요일'
        WHEN 4 THEN '목요일'
        WHEN 5 THEN '금요일'
        WHEN 6 THEN '토요일'
    END AS day_of_week,
    COUNT(*) AS order_count
FROM orders
GROUP BY EXTRACT(DOW FROM ordered_at::date)
ORDER BY EXTRACT(DOW FROM ordered_at::date);

-- Order count by hour
SELECT
    CAST(strftime('%H', ordered_at) AS INTEGER) AS hour,
    COUNT(*) AS order_count
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY strftime('%H', ordered_at)
ORDER BY hour;

-- Order count by hour
SELECT
    HOUR(ordered_at) AS hour,
    COUNT(*) AS order_count
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2025-01-01'
GROUP BY HOUR(ordered_at)
ORDER BY hour;

-- Order count by hour
SELECT
    EXTRACT(HOUR FROM ordered_at)::int AS hour,
    COUNT(*) AS order_count
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2025-01-01'
GROUP BY EXTRACT(HOUR FROM ordered_at)
ORDER BY hour;

-- 7 days after order date = expected delivery
SELECT
    order_number,
    ordered_at,
    DATE(ordered_at, '+7 days') AS expected_delivery
FROM orders
WHERE ordered_at LIKE '2024-12%'
ORDER BY ordered_at DESC
LIMIT 5;

-- 7 days after order date = expected delivery
SELECT
    order_number,
    ordered_at,
    DATE_ADD(ordered_at, INTERVAL 7 DAY) AS expected_delivery
FROM orders
WHERE ordered_at >= '2024-12-01'
  AND ordered_at <  '2025-01-01'
ORDER BY ordered_at DESC
LIMIT 5;

-- 7 days after order date = expected delivery
SELECT
    order_number,
    ordered_at,
    ordered_at::date + INTERVAL '7 days' AS expected_delivery
FROM orders
WHERE ordered_at >= '2024-12-01'
  AND ordered_at <  '2025-01-01'
ORDER BY ordered_at DESC
LIMIT 5;

-- First day of month, first day of year
SELECT
    DATE('now', 'start of month')  AS first_of_month,
    DATE('now', 'start of year')   AS first_of_year;

-- First and last order date per month
SELECT
    SUBSTR(ordered_at, 1, 7) AS month,
    MIN(DATE(ordered_at))    AS first_order,
    MAX(DATE(ordered_at))    AS last_order
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY SUBSTR(ordered_at, 1, 7)
ORDER BY month;

-- First day of month, first day of year
SELECT
    DATE_FORMAT(CURDATE(), '%Y-%m-01')     AS first_of_month,
    DATE_FORMAT(CURDATE(), '%Y-01-01')     AS first_of_year;

-- Or use LAST_DAY() to get month end
SELECT LAST_DAY(CURDATE()) AS last_of_month;

-- First day of month, first day of year
SELECT
    DATE_TRUNC('month', CURRENT_DATE) AS first_of_month,
    DATE_TRUNC('year',  CURRENT_DATE) AS first_of_year;

SELECT
    ordered_at,
    strftime('%Y년 %m월 %d일', ordered_at)       AS korean_format,
    strftime('%Y%m%d', ordered_at)               AS compact_format,
    strftime('%d/%m/%Y', ordered_at)              AS eu_format
FROM orders
LIMIT 3;

SELECT
    ordered_at,
    DATE_FORMAT(ordered_at, '%Y년 %m월 %d일')  AS korean_format,
    DATE_FORMAT(ordered_at, '%Y%m%d')          AS compact_format,
    DATE_FORMAT(ordered_at, '%d/%m/%Y')         AS eu_format
FROM orders
LIMIT 3;

SELECT
    ordered_at,
    TO_CHAR(ordered_at, 'YYYY"년" MM"월" DD"일"')  AS korean_format,
    TO_CHAR(ordered_at, 'YYYYMMDD')                AS compact_format,
    TO_CHAR(ordered_at, 'DD/MM/YYYY')               AS eu_format
FROM orders
LIMIT 3;

-- How many days between order and delivery?
SELECT
    o.order_number,
    o.ordered_at,
    s.delivered_at,
    ROUND(julianday(s.delivered_at) - julianday(o.ordered_at), 1) AS delivery_days
FROM orders AS o
INNER JOIN shipping AS s ON s.order_id = o.id
WHERE s.delivered_at IS NOT NULL
ORDER BY delivery_days DESC
LIMIT 8;

-- How many days between order and delivery?
SELECT
    o.order_number,
    o.ordered_at,
    s.delivered_at,
    DATEDIFF(s.delivered_at, o.ordered_at) AS delivery_days
FROM orders AS o
INNER JOIN shipping AS s ON s.order_id = o.id
WHERE s.delivered_at IS NOT NULL
ORDER BY delivery_days DESC
LIMIT 8;

-- How many days between order and delivery?
SELECT
    o.order_number,
    o.ordered_at,
    s.delivered_at,
    s.delivered_at::date - o.ordered_at::date AS delivery_days
FROM orders AS o
INNER JOIN shipping AS s ON s.order_id = o.id
WHERE s.delivered_at IS NOT NULL
ORDER BY delivery_days DESC
LIMIT 8;

-- Average delivery days per carrier
SELECT
    s.carrier,
    COUNT(*)  AS deliveries,
    ROUND(AVG(julianday(s.delivered_at) - julianday(o.ordered_at)), 1) AS avg_days
FROM shipping AS s
INNER JOIN orders AS o ON s.order_id = o.id
WHERE s.delivered_at IS NOT NULL
GROUP BY s.carrier
ORDER BY avg_days;

-- Average delivery days per carrier
SELECT
    s.carrier,
    COUNT(*)  AS deliveries,
    ROUND(AVG(DATEDIFF(s.delivered_at, o.ordered_at)), 1) AS avg_days
FROM shipping AS s
INNER JOIN orders AS o ON s.order_id = o.id
WHERE s.delivered_at IS NOT NULL
GROUP BY s.carrier
ORDER BY avg_days;

-- Average delivery days per carrier
SELECT
    s.carrier,
    COUNT(*)  AS deliveries,
    ROUND(AVG(s.delivered_at::date - o.ordered_at::date), 1) AS avg_days
FROM shipping AS s
INNER JOIN orders AS o ON s.order_id = o.id
WHERE s.delivered_at IS NOT NULL
GROUP BY s.carrier
ORDER BY avg_days;

-- Customer age from birth_date
SELECT
    name,
    birth_date,
    CAST(
        (julianday('now') - julianday(birth_date)) / 365.25
    AS INTEGER) AS age
FROM customers
WHERE birth_date IS NOT NULL
ORDER BY age DESC
LIMIT 8;

-- Customer age from birth_date
SELECT
    name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM customers
WHERE birth_date IS NOT NULL
ORDER BY age DESC
LIMIT 8;

-- Customer age from birth_date
SELECT
    name,
    birth_date,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date))::int AS age
FROM customers
WHERE birth_date IS NOT NULL
ORDER BY age DESC
LIMIT 8;

-- Order count by week for 2024
SELECT
    strftime('%W', ordered_at) AS week_number,
    COUNT(*)                   AS order_count
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY strftime('%W', ordered_at)
ORDER BY week_number
LIMIT 10;

-- Order count by week for 2024
SELECT
    WEEK(ordered_at, 1) AS week_number,
    COUNT(*)            AS order_count
FROM orders
WHERE YEAR(ordered_at) = 2024
GROUP BY WEEK(ordered_at, 1)
ORDER BY week_number
LIMIT 10;

-- Order count by week for 2024
SELECT
    EXTRACT(WEEK FROM ordered_at::date)::int AS week_number,
    COUNT(*)                                  AS order_count
FROM orders
WHERE EXTRACT(YEAR FROM ordered_at::date) = 2024
GROUP BY EXTRACT(WEEK FROM ordered_at::date)
ORDER BY week_number
LIMIT 10;

-- Quarterly revenue
SELECT
    SUBSTR(ordered_at, 1, 4) AS year,
    CASE
        WHEN CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) BETWEEN 1 AND 3  THEN 'Q1'
        WHEN CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) BETWEEN 4 AND 6  THEN 'Q2'
        WHEN CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) BETWEEN 7 AND 9  THEN 'Q3'
        ELSE 'Q4'
    END AS quarter,
    SUM(total_amount) AS revenue
FROM orders
WHERE status NOT IN ('cancelled', 'returned')
  AND ordered_at LIKE '2024%'
GROUP BY year, quarter
ORDER BY year, quarter;

-- Quarterly revenue
SELECT
    YEAR(ordered_at) AS year,
    CONCAT('Q', QUARTER(ordered_at)) AS quarter,
    SUM(total_amount) AS revenue
FROM orders
WHERE status NOT IN ('cancelled', 'returned')
  AND YEAR(ordered_at) = 2024
GROUP BY YEAR(ordered_at), QUARTER(ordered_at)
ORDER BY year, quarter;

-- Quarterly revenue
SELECT
    EXTRACT(YEAR FROM ordered_at::date)::int AS year,
    'Q' || EXTRACT(QUARTER FROM ordered_at::date)::int AS quarter,
    SUM(total_amount) AS revenue
FROM orders
WHERE status NOT IN ('cancelled', 'returned')
  AND EXTRACT(YEAR FROM ordered_at::date) = 2024
GROUP BY
    EXTRACT(YEAR FROM ordered_at::date),
    EXTRACT(QUARTER FROM ordered_at::date)
ORDER BY year, quarter;