How to Get the Current Date and Time in MySQL

All three read the same clock, fixed at statement start, so NOW() returns an identical value everywhere within one statement. Use SYSDATE() if you specifically want the time at the moment each call executes. The values follow the session time zone, which SELECT @@session.time_zone will show you.

SELECT NOW(), CURDATE(), CURRENT_TIMESTAMP;

Yesterday, last 30 days

INTERVAL takes a plain number and a unit keyword (DAY, MONTH, YEAR, HOUR) with no quotes around the number. Working from CURDATE() rather than NOW() keeps you on date boundaries, which is usually what a daily report wants. For range filters, prefer created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) over wrapping created_at in a function, so the index on that column stays usable.

SELECT
  DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS yesterday,
  DATE_SUB(CURDATE(), INTERVAL 30 DAY) AS thirty_days_ago;