Current Date and Time Functions

Learning Focus

Use this lesson to understand MySQL's current date and time functions — the tools you use every time you need to know "what time is it right now?" inside a SQL query.

Concept Overview

What Are Current Date/Time Functions?

MySQL provides built-in functions that return the current date, time, or both at the moment a query executes. These are used constantly in real applications for:

• Recording when something happened (audit logs, order timestamps)
• Filtering data by time windows ("show me orders from today")
• Setting default values on columns like created_at and updated_at

Key Distinction: Statement Time vs. Wall Clock Time

Function	Returns	Behavior
NOW()	Date + time	Same value for the entire statement
SYSDATE()	Date + time	Returns the exact time of the function call
CURRENT_TIMESTAMP	Date + time	Synonym for NOW()

Why does this matter? If your INSERT takes 5 seconds and touches 10,000 rows, NOW() gives all rows the same timestamp (consistent), while SYSDATE() gives each row a slightly different timestamp (inconsistent). Use NOW() by default.

Server Time vs. UTC

Your MySQL server has a configured timezone. If your application serves users across timezones, mixing server local time and UTC causes bugs:

-- Check your server's current timezone
SELECT @@global.time_zone, @@session.time_zone;

Best practice: Store timestamps in UTC, convert to local time in the application layer.

---

Basic Syntax & Rules

Function Reference

Function	Returns	Example Output	When to Use
NOW()	DATETIME (date + time)	2026-02-10 14:30:00	Most common — events, audit logs
CURDATE()	DATE only	2026-02-10	Daily reports, date comparisons
CURTIME()	TIME only	14:30:00	Time-of-day checks (rare)
UTC_TIMESTAMP()	DATETIME in UTC	2026-02-10 07:30:00	Cross-timezone applications
UTC_DATE()	DATE in UTC	2026-02-10	UTC-only date comparisons
UNIX_TIMESTAMP()	INT (seconds since epoch)	1770688200	API timestamps, caching keys
CURRENT_TIMESTAMP	Same as NOW()	2026-02-10 14:30:00	Column defaults (ANSI SQL syntax)

Using as Column Defaults

The most common use of timestamp functions is setting automatic values on created_at and updated_at columns:

CREATE TABLE orders (
    order_id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    total      DECIMAL(12,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
                                 ON UPDATE CURRENT_TIMESTAMP
);

• DEFAULT CURRENT_TIMESTAMP — auto-fills when a row is inserted
• ON UPDATE CURRENT_TIMESTAMP — auto-updates when any column in the row changes

---

Step-by-Step Examples

Example 1: Seeing All Time Functions at Once

SELECT
    NOW()            AS server_now,
    CURDATE()        AS server_date,
    CURTIME()        AS server_time,
    UTC_TIMESTAMP()  AS utc_now,
    UTC_DATE()       AS utc_date,
    UNIX_TIMESTAMP() AS unix_epoch;

Expected output:

server_now	server_date	server_time	utc_now	utc_date	unix_epoch
2026-02-10 14:30:00	2026-02-10	14:30:00	2026-02-10 07:30:00	2026-02-10	1770688200

Notice: server_now and utc_now differ by the server's timezone offset.

---

Example 2: Audit Logging with NOW()

Step 1: Create an audit log table

CREATE TABLE audit_logs (
    log_id     BIGINT AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    user_id    BIGINT,
    details    TEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert an audit entry

INSERT INTO audit_logs (event_type, user_id, details)
VALUES ('LOGIN_SUCCESS', 42, 'Login from IP 192.168.1.100');

Notice: we didn't specify created_at — it's auto-filled by DEFAULT CURRENT_TIMESTAMP.

Step 3: Query recent events

SELECT event_type, user_id, created_at
FROM audit_logs
WHERE created_at >= NOW() - INTERVAL 1 HOUR
ORDER BY created_at DESC;

event_type	user_id	created_at
LOGIN_SUCCESS	42	2026-02-10 14:30:00

---

Example 3: Filtering by Today's Date

A very common reporting pattern — "show me today's orders":

-- Using CURDATE() for date-only comparison
SELECT order_id, total, created_at
FROM orders
WHERE DATE(created_at) = CURDATE();

Better approach (index-friendly):

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

The second version uses a range scan instead of applying DATE() to every row, which is much faster on large tables.

---

Example 4: UTC-Based Timestamps for Multi-Timezone Apps

-- Store in UTC
INSERT INTO events (event_name, occurred_at)
VALUES ('user_signup', UTC_TIMESTAMP());

-- Query in UTC, convert to local in the application layer
SELECT event_name,
       occurred_at AS utc_time,
       CONVERT_TZ(occurred_at, '+00:00', '+07:00') AS jakarta_time
FROM events
WHERE occurred_at >= UTC_TIMESTAMP() - INTERVAL 24 HOUR;

event_name	utc_time	jakarta_time
user_signup	2026-02-10 07:30:00	2026-02-10 14:30:00

---

Practical Use Cases

1. Audit Trails

Every system needs to know when things happened. Use CURRENT_TIMESTAMP as a default on created_at columns across all tables.

2. Session Expiry

Track session creation with NOW(), then filter expired sessions with WHERE expires_at < NOW().

3. Daily Reports and Partitioning

Use CURDATE() to filter data for daily batches, cron jobs, or partition pruning.

4. Cross-Region Applications

Store all timestamps as UTC_TIMESTAMP() and convert to local time in the presentation layer. This prevents timezone bugs.

5. Cache Invalidation

Use UNIX_TIMESTAMP() to generate cache keys or TTL values for application-level caching.

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Mixing NOW() and UTC_TIMESTAMP() in the same schema	Some columns are local time, others UTC — reports show wrong times	Pick one strategy and standardize across all tables
Using SYSDATE() in replication environments	Source and replica get different timestamps	Use NOW() which is deterministic and replication-safe
No created_at / updated_at columns	No audit trail — impossible to debug "when did this change?"	Add DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP to every table
Using DATE(created_at) = CURDATE() on large tables	Applies function to every row — cannot use index	Use range: created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL 1 DAY
Not syncing server time with NTP	Time drift between servers causes inconsistent timestamps	Configure NTP on all database servers

---

Best Practices

• Standardize on UTC — store UTC_TIMESTAMP(), convert in the app layer
• Use CURRENT_TIMESTAMP defaults — every table should have created_at and updated_at
• Prefer NOW() over SYSDATE() — NOW() is deterministic per statement
• Never use DATE() in WHERE clauses on indexed columns — use range comparisons instead
• Sync your clocks — use NTP to ensure consistent timestamps across servers
• Document your timezone strategy — write down whether your app uses UTC or local time

---

Hands-On Practice

Exercise 1: Set Up Timestamp Columns (Easy)

Create a products table with:

• product_id (PK, auto-increment)
• name (required)
• price (required)
• created_at (auto-filled on insert)
• updated_at (auto-updated on any change)

Insert a product, then update its price. Verify that created_at stays the same but updated_at changes.

Exercise 2: Filter by Time Window (Medium)

Write a query to find all orders placed in the last 7 days, sorted by most recent first. Write two versions:

1. Using DATE() function (simple but slow)
2. Using range comparison (index-friendly)

Explain why version 2 is better.

Exercise 3: UTC Conversion (Advanced)

Your application stores timestamps in UTC. Write a query that:

1. Finds all events from the last 24 hours
2. Returns the time in both UTC and Asia/Jakarta (UTC+7)
3. Groups the events by hour in Jakarta time

---

Connection to Other Concepts

Related Concept	How It Connects
Formatting & Parsing Dates	NOW() returns raw datetime; formatting converts it for display
Date Arithmetic	NOW() - INTERVAL 7 DAY — arithmetic starts from current time
Indexes	Using functions like DATE() on indexed columns breaks index usage
Event Scheduler	Events use NOW() to determine when to fire
Replication	NOW() is replication-safe; SYSDATE() is not

What to Learn Next

• Formatting and Parsing Dates — control how dates appear in output

---

Visual Learning Diagram

flowchart TD
    A["Need current time in a query?"] --> B{"What do you need?"}
    B -->|"Date + Time"| C{"Timezone?"}
    B -->|"Date only"| D["CURDATE()"]
    B -->|"Time only"| E["CURTIME()"]
    B -->|"Unix seconds"| F["UNIX_TIMESTAMP()"]
    C -->|"Server local"| G["NOW()"]
    C -->|"UTC"| H["UTC_TIMESTAMP()"]
    G --> I["Use for local-only apps"]
    H --> J["Use for multi-timezone apps"]

    classDef recommended fill:#28a745,stroke:#fff,color:#fff
    class H,J recommended

Quick Reference

-- Current date and time
SELECT NOW(), CURDATE(), CURTIME(), UTC_TIMESTAMP();

-- Unix timestamp
SELECT UNIX_TIMESTAMP();
SELECT FROM_UNIXTIME(1770688200);

-- Column defaults
ALTER TABLE t ADD created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE t ADD updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
                             ON UPDATE CURRENT_TIMESTAMP;

-- Timezone check
SELECT @@global.time_zone, @@session.time_zone;

What's Next

• Formatting and Parsing Dates