MySQL Functions, JSON, and Date Cheatsheet

SELECT NOW(), CURRENT_DATE(), CURRENT_TIME();

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS one_week_from_now;

SELECT DATE_SUB(NOW(), INTERVAL 30 DAY) AS thirty_days_ago;

SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS created_at_fmt FROM orders LIMIT 5;

SELECT TIMESTAMPDIFF(MINUTE, created_at, NOW()) AS age_minutes FROM sessions;

SELECT JSON_EXTRACT(settings, '$.notifications.email') AS email_enabled FROM user_preferences;

UPDATE user_preferences
SET settings = JSON_SET(settings, '$.theme', 'dark')
WHERE user_id = 42;

SELECT JSON_OBJECT('id', id, 'email', email) AS user_json FROM users LIMIT 5;

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM contacts;

SELECT COALESCE(display_name, full_name, email) AS label FROM users;

SELECT id,
  CASE status
    WHEN 'paid' THEN 'Paid'
    WHEN 'pending' THEN 'Pending'
    ELSE 'Other'
  END AS status_label
FROM orders;