NOW, CURDATE, CURTIME in MySQL - Deep Dive

Overview - NOW, CURDATE, CURTIME

What is it?

NOW, CURDATE, and CURTIME are functions in MySQL that give you the current date and time information. NOW returns the full date and time, CURDATE returns just the current date, and CURTIME returns just the current time. These functions help you work with the exact moment when a query runs.

Why it matters

Knowing the current date and time is important for many tasks like recording when events happen, filtering data by date, or scheduling actions. Without these functions, you would have to manually enter dates and times, which is slow and error-prone. They make your database smart about time.

Where it fits

Before learning these functions, you should understand basic SQL queries and data types like DATE and TIME. After mastering them, you can learn about time zones, date arithmetic, and scheduling tasks in databases.

Mental Model

Core Idea

NOW, CURDATE, and CURTIME give you the exact current date and time from the database server whenever you ask.

Think of it like...

It's like looking at a clock on the wall: NOW tells you the full date and time, CURDATE tells you just the day on the calendar, and CURTIME tells you just the clock time.

┌───────────────┐
│   CURRENT     │
│   MOMENT      │
│  (Database)   │
└──────┬────────┘
       │
       │
 ┌─────▼─────┐  ┌───────────┐  ┌───────────┐
 │   NOW     │  │  CURDATE  │  │  CURTIME  │
 │ yyyy-mm-dd│  │ yyyy-mm-dd│  │ hh:mm:ss  │
 │ hh:mm:ss  │  │           │  │           │
 └───────────┘  └───────────┘  └───────────┘

Build-Up - 7 Steps

1

Foundation

Understanding NOW function basics

Concept: NOW() returns the current date and time as a combined value.

In MySQL, you can use NOW() to get the exact current date and time from the server. For example, running SELECT NOW(); might return '2024-06-15 14:30:45'. This value includes year, month, day, hour, minute, and second.

Result

A datetime string like '2024-06-15 14:30:45' showing the current moment.

Understanding NOW() is key because it gives you a precise timestamp, which is essential for recording when things happen.

2

Foundation

Basics of CURDATE and CURTIME

Concept: CURDATE() returns only the current date; CURTIME() returns only the current time.

CURDATE() gives you the date part only, like '2024-06-15'. CURTIME() gives you the time part only, like '14:30:45'. You can use SELECT CURDATE(); or SELECT CURTIME(); to get these values separately.

Result

CURDATE returns '2024-06-15'; CURTIME returns '14:30:45'.

Knowing how to get just the date or just the time helps when you only need part of the timestamp.

3

Intermediate

Using NOW, CURDATE, CURTIME in queries

🤔Before reading on: Do you think NOW() can be used to filter records by date and time, or only to display current time? Commit to your answer.

Concept: These functions can be used in WHERE clauses to filter data based on current date or time.

You can write queries like SELECT * FROM orders WHERE order_date = CURDATE(); to get today's orders. Or SELECT * FROM events WHERE event_time < CURTIME(); to get events earlier than now. This helps you work with live data relative to the current moment.

Result

Query returns rows matching the current date or time condition.

Using these functions in filters lets your database queries adapt automatically to the current moment without manual date input.

4

Intermediate

Difference between NOW and CURRENT_TIMESTAMP

🤔Before reading on: Do you think NOW() and CURRENT_TIMESTAMP() return different values or the same? Commit to your answer.

Concept: NOW() and CURRENT_TIMESTAMP() are synonyms in MySQL, both returning current date and time.

In MySQL, NOW() and CURRENT_TIMESTAMP() do the same thing: they return the current date and time. You can use either in your queries. For example, SELECT CURRENT_TIMESTAMP(); returns the same as SELECT NOW();.

Result

Both functions return identical datetime values.

Knowing these synonyms helps you read different codebases and write flexible queries.

5

Intermediate

Format and data types returned by these functions

Concept: NOW() returns DATETIME, CURDATE() returns DATE, CURTIME() returns TIME data types.

NOW() returns a DATETIME value like '2024-06-15 14:30:45'. CURDATE() returns a DATE value like '2024-06-15'. CURTIME() returns a TIME value like '14:30:45'. These types help MySQL understand how to store and compare these values.

Result

Each function returns a value with a specific data type suitable for date/time operations.

Understanding data types helps you avoid errors when comparing or storing these values.

6

Advanced

Using NOW, CURDATE, CURTIME with time zones

🤔Before reading on: Do you think NOW() always returns the same time regardless of server settings? Commit to your answer.

Concept: These functions return values based on the MySQL server's time zone setting, which can affect results.

MySQL server has a time zone setting that affects what NOW(), CURDATE(), and CURTIME() return. If the server is set to UTC, NOW() returns UTC time. If set to local time zone, it returns local time. You can check with SELECT @@global.time_zone; and adjust if needed.

Result

The returned date/time reflects the server's configured time zone.

Knowing the time zone impact prevents confusion when your application and database server are in different zones.

7

Expert

Internal evaluation timing of NOW, CURDATE, CURTIME

🤔Before reading on: Do you think NOW() returns the exact same time if called multiple times in one query, or different times? Commit to your answer.

Concept: NOW() and related functions are evaluated once per query, so multiple calls in the same query return the same timestamp.

When you run a query, MySQL evaluates NOW() once at the start of the query execution. So if you use NOW() multiple times in one query, it returns the same value each time. This ensures consistency. However, if you run separate queries, NOW() updates to the current time.

Result

Multiple NOW() calls in one query return identical timestamps.

Understanding this prevents bugs where you expect NOW() to change within a single query but it doesn't.

Under the Hood

When a query with NOW(), CURDATE(), or CURTIME() runs, MySQL fetches the current system time from the server's clock once at the start of the query execution. It then converts this time into the appropriate format (DATETIME, DATE, or TIME) and uses this value throughout the query. This avoids inconsistencies during query processing.

Why designed this way?

Evaluating these functions once per query ensures stable and predictable results, especially when multiple references to current time appear in the same query. It avoids confusing situations where different parts of a query see different times. Alternatives like evaluating per row would cause inconsistent data.

┌─────────────────────────────┐
│  Query Execution Starts      │
└─────────────┬───────────────┘
              │
              ▼
┌─────────────────────────────┐
│  Fetch current system time   │
│  (server clock)              │
└─────────────┬───────────────┘
              │
              ▼
┌─────────────────────────────┐
│  Convert to NOW, CURDATE,    │
│  or CURTIME format           │
└─────────────┬───────────────┘
              │
              ▼
┌─────────────────────────────┐
│  Use this value throughout   │
│  the query execution         │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does NOW() return a new current time each time it is called within the same query? Commit to yes or no.

Common Belief: NOW() returns the exact current time every time it is called, even multiple times in one query.

Reality: NOW() is evaluated once at the start of the query and returns the same timestamp for all calls within that query.

Why it matters: Expecting different times within one query can cause bugs in time calculations or inconsistent data.

Quick: Does CURDATE() return the date based on the client’s local time or the server’s time? Commit to your answer.

Common Belief: CURDATE() returns the date based on the client’s local time zone.

Reality: CURDATE() returns the date based on the MySQL server’s time zone setting, not the client’s.

Why it matters: Misunderstanding this can cause wrong date filtering or data recording when client and server are in different time zones.

Quick: Is CURTIME() always just the current time without any date? Commit to yes or no.

Common Belief: CURTIME() returns the current time including the date part.

Reality: CURTIME() returns only the time part (hours, minutes, seconds) without any date.

Why it matters: Using CURTIME() where a full datetime is expected can cause errors or unexpected results.

Quick: Can NOW() be used to get the current timestamp in milliseconds? Commit to yes or no.

Common Belief: NOW() returns the current time including milliseconds.

Reality: NOW() returns time only up to seconds precision; it does not include milliseconds.

Why it matters: Relying on NOW() for millisecond precision can cause problems in applications needing very fine time measurements.

Expert Zone

1

NOW() returns a constant timestamp per query, but functions like SYSDATE() return the actual current time at each call, which can differ within a query.

2

CURDATE() and CURTIME() depend on the server’s time zone, so changing the server time zone affects all queries using these functions immediately.

3

Using NOW() in default column values is not allowed in MySQL, but CURRENT_TIMESTAMP can be used, which is functionally equivalent.

When NOT to use

Avoid using NOW(), CURDATE(), or CURTIME() when you need the client’s local time or when you require sub-second precision. Instead, use application-side time or functions like UNIX_TIMESTAMP() for epoch time or SYSDATE() for real-time evaluation.

Production Patterns

In production, NOW() is commonly used for timestamping records on insert or update, filtering data by current date, and scheduling jobs. Developers often combine NOW() with INTERVAL to calculate future or past dates, e.g., NOW() + INTERVAL 1 DAY.

Connections

Time Zones

Builds-on

Understanding how NOW(), CURDATE(), and CURTIME() depend on server time zones helps manage data correctly across global applications.

Event Scheduling

Builds-on

Using current date and time functions is essential for triggering events or jobs at the right moment in database-driven systems.

Real-Time Clocks in Embedded Systems

Same pattern

Just like NOW() fetches the current time from the server clock, embedded systems use real-time clocks to get current time for operations, showing a shared principle across fields.

Common Pitfalls

#1 Using NOW() multiple times in one query expecting different times.

Wrong approach:

SELECT NOW() AS time1, NOW() AS time2;

Correct approach:

SELECT NOW() AS time1, NOW() AS time2; -- but understand both times are identical

Root cause:

Misunderstanding that NOW() is evaluated once per query, not per call.

#2 Filtering data by CURDATE() assuming it uses client time zone.

Wrong approach:

SELECT * FROM orders WHERE order_date = CURDATE(); -- expecting client date

Correct approach:

SET time_zone = 'client/timezone'; SELECT * FROM orders WHERE order_date = CURDATE(); -- or adjust server time zone

Root cause:

Not realizing CURDATE() uses server time zone, causing mismatches with client expectations.

#3 Using CURTIME() when full datetime is needed.

Wrong approach:

SELECT * FROM events WHERE event_datetime < CURTIME();

Correct approach:

SELECT * FROM events WHERE event_datetime < NOW();

Root cause:

Confusing CURTIME() (time only) with NOW() (date and time).

Key Takeaways

NOW(), CURDATE(), and CURTIME() provide the current date and time from the MySQL server in different formats.

These functions are evaluated once per query, ensuring consistent timestamps within that query.

They depend on the server's time zone setting, which affects the returned values and must be managed carefully.

Using the right function for your needs—full datetime, date only, or time only—prevents errors in data handling.

Understanding these functions deeply helps build reliable, time-aware database applications.