MySQL Functions, JSON, and Date Cheatsheet

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

# Return current timestamp values.

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

# Calculate a future date or time.

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

# Calculate a relative date in the past.

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

# Render a datetime using format specifiers.

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

# Calculate age or duration.

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

# Read a path inside a JSON document.

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

# Write or replace a value inside a JSON document.

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

# Construct a JSON object from scalar values.

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

# Join scalar values into one string.

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

# Return the first non-NULL expression.

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

# Transform values into labels inside a query.