NOW() is fixed when the statement starts, while SYSDATE() reads the clock at each call. DATE_ADD(), DATE_SUB() and + INTERVAL n unit do arithmetic, DATEDIFF() counts days and TIMESTAMPDIFF(unit, a, b) any unit. DATE_FORMAT() and STR_TO_DATE() share specifiers: %Y year, %m and %b month, %d and %D day ("11th"), %H and %l hour, %i minutes, %p AM/PM, %a weekday.
SELECT DATE_FORMAT(ordered_at, '%Y-%m') AS month, COUNT(*) AS orders,
GROUP_CONCAT(DISTINCT YEARWEEK(ordered_at, 3)) AS iso_weeks,
MIN(DATE(ordered_at) - INTERVAL WEEKDAY(ordered_at) DAY) AS first_monday,
DATE_FORMAT(MIN(ordered_at), '%a %D %b, %l:%i %p') AS first_order
FROM orders WHERE ordered_at < '2026-06-01' GROUP BY month ORDER BY month;+---------+--------+---------------+--------------+-----------------------+ | month | orders | iso_weeks | first_monday | first_order | +---------+--------+---------------+--------------+-----------------------+ | 2026-03 | 2 | 202610,202611 | 2026-03-02 | Mon 2nd Mar, 10:15 AM | | 2026-04 | 1 | 202615 | 2026-04-06 | Sat 11th Apr, 7:30 AM | | 2026-05 | 2 | 202619,202621 | 2026-05-04 | Tue 5th May, 12:00 PM | +---------+--------+---------------+--------------+-----------------------+
YEARWEEK(d, 3) numbers Monday-based ISO weeks; the default mode 0 starts on Sunday. Subtracting WEEKDAY() days gives the week's Monday, a friendlier label. Month arithmetic clamps: '2026-01-31' + INTERVAL 1 MONTH is 2026-02-28. CONVERT_TZ() with a named zone such as 'Asia/Kuala_Lumpur' returns NULL until the zone tables are loaded (mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql), while offsets such as '+08:00' always work. In WHERE, keep the column bare (ordered_at >= '2026-03-01', not MONTH(ordered_at) = 3) so an index applies (Sargable Predicates).