Introduction

Getting the current date and time in MySQL is simple at syntax level, but production correctness depends on timezone handling, column types, and query context. Choosing between NOW(), CURRENT_DATE(), and UTC-based functions affects data consistency across services. This guide explains practical patterns you can run directly.

Core Current-Time Functions

MySQL provides several built-in functions with different granularity.

1SELECT
2  NOW() AS current_datetime,
3  CURRENT_TIMESTAMP() AS current_timestamp_alias,
4  CURRENT_DATE() AS current_date,
5  CURRENT_TIME() AS current_time;

NOW() and CURRENT_TIMESTAMP() are equivalent in common usage. Use CURRENT_DATE() when you need only date logic, such as daily partitions.

For fractional seconds:

SELECT NOW(6) AS microsecond_precision;

This is useful for high-frequency event ordering.

Session Timezone vs UTC

NOW() uses the session timezone. If application servers run in multiple regions, this can create inconsistent records unless you standardize.

Check settings:

SELECT @@global.time_zone AS global_tz, @@session.time_zone AS session_tz;

Set session timezone explicitly:

SET time_zone = '+00:00';
SELECT NOW() AS utc_now;

Or convert at query time:

SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00') AS converted_utc;

In distributed systems, storing UTC in TIMESTAMP or normalized DATETIME fields is usually safest.

Inserting Current Time into Tables

Typical insert patterns:

INSERT INTO audit_log(event_type, created_at)
VALUES ('user_login', NOW());

You can also define defaults in schema so inserts remain consistent across clients:

1CREATE TABLE jobs (
2  id BIGINT PRIMARY KEY AUTO_INCREMENT,
3  name VARCHAR(120) NOT NULL,
4  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
5  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
6);

This pattern is reliable for change tracking without requiring application-side timestamp population.

Filtering by Current Date Safely

A frequent mistake is wrapping indexed columns in date functions, which can disable index usage. Prefer range predicates.

Less efficient pattern:

SELECT *
FROM orders
WHERE DATE(created_at) = CURRENT_DATE();

Index-friendly pattern:

1SELECT *
2FROM orders
3WHERE created_at >= CURRENT_DATE()
4  AND created_at < CURRENT_DATE() + INTERVAL 1 DAY;

The range approach scales better on large tables.

Choosing TIMESTAMP vs DATETIME

Use TIMESTAMP when you want MySQL to manage timezone conversion relative to session settings. Use DATETIME when the stored value should remain literal and unaffected by timezone conversion.

Guideline:

• event logs and operational records: usually TIMESTAMP
• business-local schedule values: often DATETIME

Be explicit in schema docs so teams apply one policy consistently.

Application Integration Example

A Python client inserting current time from SQL side avoids clock skew between app nodes.

1import mysql.connector
2
3conn = mysql.connector.connect(
4    host="localhost",
5    user="app",
6    password="secret",
7    database="demo"
8)
9cur = conn.cursor()
10cur.execute("INSERT INTO audit_log(event_type, created_at) VALUES (%s, NOW())", ("sync_run",))
11conn.commit()
12cur.close()
13conn.close()

Using server-side NOW() keeps ordering and audit behavior consistent.

Formatting Current Time for Output

Keep storage and display concerns separate. Store high-fidelity timestamp values, then format at query time only for user-facing output.

SELECT
  DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS display_local,
  DATE_FORMAT(CONVERT_TZ(NOW(), @@session.time_zone, '+00:00'), '%Y-%m-%d %H:%i:%s') AS display_utc;

This pattern preserves accurate raw values while giving predictable formatted strings for reports.

Common Pitfalls

• Assuming NOW() is always UTC without checking session timezone.
• Mixing DATETIME and TIMESTAMP without a documented convention.
• Wrapping indexed datetime columns in functions during filtering.
• Relying on application node clocks for audit-critical timestamps.
• Forgetting microsecond precision when events occur very close together.

Summary

• Use NOW() or CURRENT_TIMESTAMP() for current datetime values.
• Standardize timezone policy, commonly UTC, across sessions and services.
• Prefer schema defaults for created_at and updated_at consistency.
• Use range predicates for date filtering to preserve index performance.
• Choose TIMESTAMP or DATETIME based on conversion semantics, not habit.