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;

NOW(), SYSDATE() and CURDATE() are three different things

NOW() returns the time the statement started and stays fixed for the whole statement, so every row of a long UPDATE gets the same timestamp. SYSDATE() reads the clock each time it is called, so rows can differ by seconds, and it is not safe for statement-based replication. CURDATE() drops the time entirely. When you want reproducible timestamps, NOW() is almost always the right one.

SELECT NOW(), SYSDATE(), CURDATE(), CURTIME();

The result depends on the session time zone

NOW() renders in the session time zone, which defaults to the server's. Two clients on the same server can therefore see different values for the same instant, and a scheduled job can disagree with your editor. UTC_TIMESTAMP() sidesteps it, and setting the session zone explicitly makes reports reproducible.

SELECT @@global.time_zone, @@session.time_zone;
SET time_zone = '+00:00';
SELECT NOW(), UTC_TIMESTAMP();

Comparing a DATETIME to CURDATE()

CURDATE() is midnight, so created_at = CURDATE() matches only rows stored at exactly 00:00:00 and looks like it is losing data. Compare against a half-open range instead, which also keeps the index in play.

SELECT * FROM orders
WHERE created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL 1 DAY;

Do more with MySQL

• Export MySQL to Google Sheets on a schedule
• MySQL GUI in the browser
• MySQL SQL editor in the browser