-- TIMESTAMP: UTC storage, timezone-converted display
CREATE TABLE events_ts (
  id         INT       NOT NULL AUTO_INCREMENT,
  name       VARCHAR(100),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

-- DATETIME: literal storage, no conversion
CREATE TABLE appointments (
  id         INT      NOT NULL AUTO_INCREMENT,
  patient_id INT      NOT NULL,
  appt_time  DATETIME NOT NULL,  -- stored as-is, no UTC conversion
  PRIMARY KEY (id)
);

-- Check session timezone
SELECT @@session.time_zone;

-- Change session timezone (affects TIMESTAMP display, not DATETIME)
SET time_zone = '+05:30';
SELECT created_at FROM events_ts;  -- shows time in IST now

-- NOW() is fixed per statement
SELECT NOW(), SLEEP(2), NOW();
-- Both NOW() calls return the same time

-- SYSDATE() evaluates at call time
SELECT SYSDATE(), SLEEP(2), SYSDATE();
-- Second SYSDATE() is 2 seconds later

-- Practical: use NOW() for created_at defaults (deterministic)
INSERT INTO orders (customer_id, created_at) VALUES (42, NOW());

SELECT NOW();           -- '2024-07-15 14:30:00' (date and time)
SELECT CURDATE();       -- '2024-07-15'          (date only)
SELECT CURRENT_DATE();  -- '2024-07-15'          (alias for CURDATE)
SELECT CURTIME();       -- '14:30:00'            (time only)
SELECT CURRENT_TIME();  -- '14:30:00'            (alias for CURTIME)
SELECT UTC_DATE();      -- '2024-07-15'          (UTC date)
SELECT UTC_TIMESTAMP(); -- '2024-07-15 18:30:00' (UTC datetime)

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');          -- '2024-07-15'
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');          -- '15/07/2024'
SELECT DATE_FORMAT(NOW(), '%M %d, %Y');         -- 'July 15, 2024'
SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y');     -- 'Monday, July 15, 2024'
SELECT DATE_FORMAT(NOW(), '%h:%i %p');          -- '02:30 PM'
SELECT DATE_FORMAT(NOW(), '%Y-%m');             -- '2024-07' (year-month key)
SELECT DATE_FORMAT(NOW(), '%Y-Q%Q');            -- '2024-Q3' (fiscal quarter label)

-- Group sales by month for reporting
SELECT
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  COUNT(*)                          AS order_count,
  SUM(total)                        AS revenue
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

SELECT STR_TO_DATE('15/07/2024', '%d/%m/%Y');
-- '2024-07-15'

SELECT STR_TO_DATE('July 15 2024', '%M %d %Y');
-- '2024-07-15'

SELECT STR_TO_DATE('07-15-2024 2:30 PM', '%m-%d-%Y %h:%i %p');
-- '2024-07-15 14:30:00'

-- Fix imported data with wrong date format
UPDATE orders
SET order_date = STR_TO_DATE(order_date_raw, '%d-%m-%Y')
WHERE order_date IS NULL AND order_date_raw IS NOT NULL;

SELECT DATE_ADD('2024-07-15', INTERVAL 30 DAY);       -- '2024-08-14'
SELECT DATE_ADD('2024-07-15', INTERVAL 3 MONTH);      -- '2024-10-15'
SELECT DATE_ADD('2024-07-15', INTERVAL 1 YEAR);       -- '2025-07-15'
SELECT DATE_ADD(NOW(),        INTERVAL 2 HOUR);        -- 2 hours from now
SELECT DATE_ADD(NOW(),        INTERVAL 30 MINUTE);
SELECT DATE_ADD(NOW(),        INTERVAL 45 SECOND);
SELECT DATE_ADD(NOW(),        INTERVAL '1 30' HOUR_MINUTE);  -- 1h 30m
SELECT DATE_ADD(NOW(),        INTERVAL '2 12:30:00' DAY_SECOND);

-- Find subscriptions expiring in the next 7 days
SELECT user_id, expires_at
FROM subscriptions
WHERE expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY);

-- Age in years
SELECT TIMESTAMPDIFF(YEAR, '1990-03-15', CURDATE()) AS age_years;

-- Duration in months
SELECT TIMESTAMPDIFF(MONTH, '2024-01-01', '2024-07-15') AS months;  -- 6

-- Days until New Year
SELECT TIMESTAMPDIFF(DAY, NOW(), '2025-01-01 00:00:00') AS days_to_ny;

-- Subscription duration
SELECT
  user_id,
  TIMESTAMPDIFF(DAY, start_date, COALESCE(end_date, CURDATE())) AS active_days
FROM subscriptions;

-- Age verification with category
SELECT
  name,
  birth_date,
  TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age,
  CASE
    WHEN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18 THEN 'Adult'
    ELSE 'Minor'
  END AS category
FROM customers;

-- Rough business day count between two dates (excludes weekends)
-- Note: does not account for holidays
SELECT
  start_dt,
  end_dt,
  FLOOR(DATEDIFF(end_dt, start_dt) / 7) * 5
    + MID('0123444401233334012222340111123400001234000123440',
          WEEKDAY(start_dt) * 7 + WEEKDAY(end_dt) + 1, 1) AS business_days
FROM date_ranges;

-- Fiscal year quarter (Q1 = April-June, common in UK/India)
SELECT
  order_date,
  CASE
    WHEN MONTH(order_date) IN (4,5,6)   THEN 'Q1'
    WHEN MONTH(order_date) IN (7,8,9)   THEN 'Q2'
    WHEN MONTH(order_date) IN (10,11,12) THEN 'Q3'
    ELSE 'Q4'
  END AS fiscal_quarter
FROM orders;

SELECT * FROM orders
WHERE order_date >= DATE(NOW() - INTERVAL WEEKDAY(NOW()) DAY)
  AND order_date <  DATE(NOW() - INTERVAL WEEKDAY(NOW()) DAY) + INTERVAL 7 DAY;

SELECT SUM(total) AS last_month_revenue
FROM orders
WHERE order_date >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m-01')
  AND order_date <  DATE_FORMAT(NOW(), '%Y-%m-01');

SELECT COUNT(DISTINCT user_id) AS active_users_30d
FROM user_activity
WHERE activity_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

WITH monthly AS (
  SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(total)                        AS revenue
  FROM orders
  GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month)   AS prev_revenue,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    / LAG(revenue) OVER (ORDER BY month) * 100, 2
  ) AS pct_change
FROM monthly;

SELECT UNIX_TIMESTAMP();                       -- e.g. 1721051400
SELECT UNIX_TIMESTAMP('2024-07-15 14:30:00');  -- specific datetime to Unix
SELECT FROM_UNIXTIME(1721051400);              -- '2024-07-15 14:30:00'
SELECT FROM_UNIXTIME(1721051400, '%Y-%m-%d');  -- '2024-07-15' (formatted)

-- Convert legacy INT timestamps to readable dates
SELECT name, FROM_UNIXTIME(created_ts) AS created_at FROM legacy_events;