SQL Date and Time Functions Explained — Filtering, Formatting and Real-World Patterns

-- ============================================================
-- Demonstrates retrieving the current date and time in MySQL.
-- Run this to understand what each function actually returns.
-- ============================================================

-- Full timestamp: date + time together
SELECT NOW() AS current_datetime;          -- e.g. 2024-03-15 09:42:11

-- Date only — no time portion
SELECT CURDATE() AS today_date;            -- e.g. 2024-03-15

-- Time only — no date portion
SELECT CURTIME() AS current_time;         -- e.g. 09:42:11

-- Practical use: stamping a new user registration
-- The created_at column is set to RIGHT NOW automatically
INSERT INTO user_accounts (username, email, created_at)
VALUES ('sarah_jones', 'sarah@example.com', NOW());

-- Practical use: find all users who registered today
-- CURDATE() strips the time so we compare apples to apples
SELECT username, email, created_at
FROM   user_accounts
WHERE  DATE(created_at) = CURDATE();  -- DATE() strips time from the stored timestamp

-- ============================================================
-- Real-world subscription expiry and overdue invoice queries.
-- Uses MySQL syntax. PostgreSQL notes included in comments.
-- ============================================================

-- Sample data context:
-- subscriptions table: id, customer_name, trial_start, trial_end
-- invoices table: id, customer_id, amount_due, due_date, paid_at

-- -------------------------------------------------------
-- 1. Find trials expiring in the next 7 days (send a reminder)
-- -------------------------------------------------------
SELECT
    customer_name,
    trial_end,
    DATEDIFF(trial_end, CURDATE()) AS days_remaining  -- positive = future, 0 = today, negative = already expired
FROM subscriptions
WHERE DATEDIFF(trial_end, CURDATE()) BETWEEN 0 AND 7
ORDER BY days_remaining ASC;  -- most urgent first

-- -------------------------------------------------------
-- 2. Find overdue invoices and how many days overdue they are
-- -------------------------------------------------------
SELECT
    i.id                          AS invoice_id,
    c.company_name,
    i.amount_due,
    i.due_date,
    DATEDIFF(CURDATE(), i.due_date) AS days_overdue  -- note argument order: (later, earlier) = positive number
FROM invoices i
JOIN customers c ON c.id = i.customer_id
WHERE i.paid_at IS NULL                             -- not yet paid
  AND i.due_date < CURDATE()                        -- past the due date
ORDER BY days_overdue DESC;                         -- worst offenders first

-- -------------------------------------------------------
-- 3. Calculate customer account age in years and months
-- TIMESTAMPDIFF lets you choose the unit of measurement
-- -------------------------------------------------------
SELECT
    username,
    created_at,
    TIMESTAMPDIFF(YEAR,  created_at, NOW()) AS account_age_years,
    TIMESTAMPDIFF(MONTH, created_at, NOW()) AS account_age_months  -- total months, not just the leftover
FROM user_accounts
ORDER BY created_at ASC;

-- PostgreSQL equivalent for days_overdue (uses interval subtraction):
-- SELECT id, CURRENT_DATE - due_date AS days_overdue FROM invoices WHERE paid_at IS NULL;

-- ============================================================
-- Monthly signup report and weekly revenue aggregation.
-- Shows both MySQL and PostgreSQL approaches side by side.
-- ============================================================

-- -------------------------------------------------------
-- MySQL: Signups grouped by month (last 12 months)
-- DATE_FORMAT with '%Y-%m-01' normalises all days to the 1st
-- so every row in a calendar month gets the same bucket key
-- -------------------------------------------------------
SELECT
    DATE_FORMAT(created_at, '%Y-%m-01')   AS month_start,    -- '2024-02-01', '2024-03-01', etc.
    DATE_FORMAT(created_at, '%M %Y')      AS month_label,    -- 'February 2024' — human-friendly for reports
    COUNT(*)                              AS new_signups,
    SUM(CASE WHEN plan_type = 'paid' THEN 1 ELSE 0 END) AS paid_signups  -- break out paid vs free
FROM user_accounts
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)  -- rolling 12-month window
GROUP BY DATE_FORMAT(created_at, '%Y-%m-01')                -- group by the normalised bucket
ORDER BY month_start ASC;                                   -- chronological order

-- -------------------------------------------------------
-- PostgreSQL equivalent using DATE_TRUNC (cleaner syntax)
-- DATE_TRUNC rounds DOWN to the start of the given period
-- -------------------------------------------------------
-- SELECT
--     DATE_TRUNC('month', created_at) AS month_start,
--     COUNT(*) AS new_signups
-- FROM user_accounts
-- WHERE created_at >= NOW() - INTERVAL '12 months'
-- GROUP BY DATE_TRUNC('month', created_at)
-- ORDER BY month_start ASC;

-- -------------------------------------------------------
-- EXTRACT: Pull individual components for pivot-style reports
-- Useful when you want day-of-week or hour-of-day analysis
-- -------------------------------------------------------
SELECT
    EXTRACT(HOUR FROM order_placed_at)  AS hour_of_day,     -- 0–23
    DAYNAME(order_placed_at)            AS day_name,         -- 'Monday', 'Tuesday', etc. (MySQL)
    COUNT(*)                            AS total_orders,
    ROUND(AVG(order_total), 2)          AS avg_order_value
FROM orders
WHERE order_placed_at >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY EXTRACT(HOUR FROM order_placed_at), DAYNAME(order_placed_at)
ORDER BY total_orders DESC
LIMIT 10;  -- top 10 busiest hour/day combinations

-- ============================================================
-- Safe, index-friendly date range filtering patterns.
-- These patterns work across MySQL, PostgreSQL, and SQL Server.
-- ============================================================

-- -------------------------------------------------------
-- PATTERN 1: Specific calendar month — use half-open interval
-- BETWEEN '2024-03-01' AND '2024-03-31' MISSES the last 23h 59m 59s
-- This pattern captures every row in March, no matter the time
-- -------------------------------------------------------
SELECT
    order_id,
    customer_id,
    order_total,
    order_placed_at
FROM orders
WHERE order_placed_at >= '2024-03-01 00:00:00'   -- first moment of March
  AND order_placed_at <  '2024-04-01 00:00:00'   -- stops BEFORE April (half-open)
ORDER BY order_placed_at DESC;

-- -------------------------------------------------------
-- PATTERN 2: Rolling window — last 30 days from right now
-- DATE_SUB dynamically calculates the window start
-- Index-friendly because the column is untouched on the left side
-- -------------------------------------------------------
SELECT
    customer_id,
    COUNT(*)          AS orders_last_30_days,
    SUM(order_total)  AS revenue_last_30_days
FROM orders
WHERE order_placed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)  -- 30 days ago to now
GROUP BY customer_id
HAVING COUNT(*) >= 3  -- customers with at least 3 orders — loyalty segment
ORDER BY revenue_last_30_days DESC;

-- -------------------------------------------------------
-- PATTERN 3: Today only — the right and WRONG way
-- WRONG (breaks index): WHERE DATE(created_at) = CURDATE()
-- RIGHT (index-friendly): use explicit range
-- -------------------------------------------------------

-- RIGHT WAY — the database can use an index on created_at
SELECT COUNT(*) AS signups_today
FROM user_accounts
WHERE created_at >= CURDATE()                              -- start of today (midnight)
  AND created_at <  DATE_ADD(CURDATE(), INTERVAL 1 DAY);  -- start of tomorrow

-- -------------------------------------------------------
-- PATTERN 4: Year-to-date (Jan 1 of current year to now)
-- -------------------------------------------------------
SELECT
    MONTHNAME(order_placed_at)   AS month_name,
    COUNT(*)                     AS total_orders,
    SUM(order_total)             AS total_revenue
FROM orders
WHERE order_placed_at >= DATE_FORMAT(CURDATE(), '%Y-01-01')  -- Jan 1 of current year
  AND order_placed_at <  NOW()                               -- up to this moment
GROUP BY MONTH(order_placed_at), MONTHNAME(order_placed_at)
ORDER BY MONTH(order_placed_at) ASC;