SQL - CURRENT_DATE / GETDATE() / NOW()

Key Insights

Every major database system has its own syntax for retrieving the current date and time—knowing the differences prevents frustrating cross-platform bugs and failed migrations.
Choosing between date-only (CURRENT_DATE) and datetime (NOW(), GETDATE()) functions affects storage, indexing, and query performance in ways that compound at scale.
Always store timestamps in UTC and convert to local time at the application layer; this single decision eliminates an entire category of timezone-related bugs in distributed systems.

Why Current Date Functions Matter

Retrieving the current date and time is one of the most fundamental operations in SQL. You’ll use it for audit logging, record timestamps, expiration checks, report filtering, and calculating durations. Despite its simplicity, this operation varies significantly across database systems—a query that works perfectly in PostgreSQL will fail immediately in SQL Server.

Understanding these differences isn’t academic. When you’re migrating databases, writing cross-platform applications, or debugging timestamp issues at 2 AM, knowing exactly which function to use and what it returns becomes critical knowledge.

Function Syntax by Database System

Each major database system has its own approach to returning the current date and time. Here’s how they compare:

PostgreSQL

-- Date only (returns DATE type)
SELECT CURRENT_DATE;

-- Date and time (returns TIMESTAMP)
SELECT NOW();
SELECT CURRENT_TIMESTAMP;

-- With timezone (returns TIMESTAMPTZ)
SELECT NOW() AT TIME ZONE 'UTC';

MySQL

-- Date only (returns DATE type)
SELECT CURRENT_DATE();
SELECT CURDATE();

-- Date and time (returns DATETIME)
SELECT NOW();
SELECT CURRENT_TIMESTAMP();

-- Alternative with microseconds
SELECT SYSDATE();

SQL Server

-- Date and time (returns DATETIME)
SELECT GETDATE();

-- Higher precision (returns DATETIME2)
SELECT SYSDATETIME();

-- Date only (cast required)
SELECT CAST(GETDATE() AS DATE);

-- UTC variants
SELECT GETUTCDATE();
SELECT SYSUTCDATETIME();

Oracle

-- Date and time (returns DATE, which includes time in Oracle)
SELECT SYSDATE FROM DUAL;

-- Session timezone aware
SELECT CURRENT_DATE FROM DUAL;

-- With timezone (returns TIMESTAMP WITH TIME ZONE)
SELECT SYSTIMESTAMP FROM DUAL;

SQLite

-- Date only (returns TEXT)
SELECT DATE('now');

-- Date and time (returns TEXT)
SELECT DATETIME('now');

-- Unix timestamp
SELECT STRFTIME('%s', 'now');

Notice that SQLite returns text strings rather than native date types. This is a common source of bugs when migrating from SQLite to a production database.

Date vs. DateTime: Understanding Return Types

The distinction between date-only and datetime functions matters more than most developers realize. Consider the storage and precision implications:

-- PostgreSQL example showing the difference
SELECT 
    CURRENT_DATE AS date_only,
    NOW() AS full_timestamp,
    pg_typeof(CURRENT_DATE) AS date_type,
    pg_typeof(NOW()) AS timestamp_type;

Output:

date_only   | full_timestamp              | date_type | timestamp_type
------------+-----------------------------+-----------+---------------------------
2024-01-15  | 2024-01-15 14:32:17.483291  | date      | timestamp with time zone

Use date-only functions when:

You’re storing birthdates, hire dates, or event dates where time is irrelevant
You need to compare dates without worrying about time components
Storage efficiency matters (DATE typically uses 4 bytes vs. 8 for TIMESTAMP)

Use datetime functions when:

You need audit trails with precise timing
You’re tracking events that happen multiple times per day
You’re calculating durations in hours, minutes, or seconds

A common mistake is using datetime when date would suffice, then struggling with comparisons:

-- This fails to match records from today if created_at is a TIMESTAMP
SELECT * FROM orders WHERE created_at = CURRENT_DATE;

-- This works correctly
SELECT * FROM orders WHERE created_at::DATE = CURRENT_DATE;

-- But this is more efficient (can use indexes)
SELECT * FROM orders 
WHERE created_at >= CURRENT_DATE 
  AND created_at < CURRENT_DATE + INTERVAL '1 day';

Common Use Cases

Inserting Timestamps on Record Creation

The most common use case is automatically timestamping records:

-- PostgreSQL: Using DEFAULT constraint
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    action VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    created_date DATE DEFAULT CURRENT_DATE
);

-- SQL Server equivalent
CREATE TABLE audit_log (
    id INT IDENTITY(1,1) PRIMARY KEY,
    action VARCHAR(100) NOT NULL,
    created_at DATETIME DEFAULT GETDATE(),
    created_date DATE DEFAULT CAST(GETDATE() AS DATE)
);

-- Insert without specifying timestamp
INSERT INTO audit_log (action) VALUES ('user_login');

Filtering Records by Today’s Date

Filtering by the current date is essential for dashboards and reports:

-- PostgreSQL: Orders placed today
SELECT * FROM orders 
WHERE order_date = CURRENT_DATE;

-- SQL Server: Orders placed today
SELECT * FROM orders 
WHERE CAST(order_date AS DATE) = CAST(GETDATE() AS DATE);

-- MySQL: Orders from the last 7 days
SELECT * FROM orders 
WHERE order_date >= CURRENT_DATE - INTERVAL 7 DAY;

Calculating Date Differences

Date arithmetic is crucial for business logic:

-- PostgreSQL: Calculate age in years
SELECT 
    name,
    birth_date,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) AS age
FROM users;

-- SQL Server: Days until subscription expires
SELECT 
    user_id,
    expiration_date,
    DATEDIFF(DAY, GETDATE(), expiration_date) AS days_remaining
FROM subscriptions
WHERE expiration_date > GETDATE();

-- MySQL: Find overdue invoices
SELECT * FROM invoices 
WHERE due_date < CURRENT_DATE 
  AND paid_date IS NULL;

Time Zones and UTC Considerations

Time zones are where datetime handling gets genuinely difficult. The golden rule: store everything in UTC, convert on display.

-- PostgreSQL: Compare local vs. UTC
SELECT 
    NOW() AS local_time,
    NOW() AT TIME ZONE 'UTC' AS utc_time,
    CURRENT_TIMESTAMP AS with_timezone;

-- MySQL: UTC functions
SELECT 
    NOW() AS local_time,
    UTC_TIMESTAMP() AS utc_time;

-- SQL Server: UTC variants
SELECT 
    GETDATE() AS local_time,
    GETUTCDATE() AS utc_time,
    SYSDATETIMEOFFSET() AS with_offset;

For distributed systems, configure your database to use UTC:

-- PostgreSQL: Set session timezone
SET timezone = 'UTC';

-- MySQL: Set session timezone
SET time_zone = '+00:00';

When storing user-facing timestamps, consider storing the timezone offset alongside the UTC timestamp:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(200),
    event_time_utc TIMESTAMP NOT NULL,
    timezone_offset INTERVAL,
    created_at TIMESTAMP DEFAULT (NOW() AT TIME ZONE 'UTC')
);

Performance Considerations

Current date functions have subtle performance implications that matter at scale.

Function evaluation timing: Most databases evaluate NOW() once at the start of a statement, not per row. This is usually what you want:

-- All rows get the same timestamp (correct behavior)
UPDATE orders SET processed_at = NOW() WHERE status = 'pending';

However, SYSDATE() in MySQL evaluates per row, which can cause issues in long-running statements.

Indexing implications: Wrapping columns in functions prevents index usage:

-- Bad: Full table scan
SELECT * FROM orders WHERE DATE(created_at) = CURRENT_DATE;

-- Good: Can use index on created_at
SELECT * FROM orders 
WHERE created_at >= CURRENT_DATE 
  AND created_at < CURRENT_DATE + INTERVAL '1 day';

For frequently filtered date columns, consider adding a computed column:

-- SQL Server: Computed column for date-only filtering
ALTER TABLE orders 
ADD order_date AS CAST(created_at AS DATE) PERSISTED;

CREATE INDEX idx_order_date ON orders(order_date);

Quick Reference Table

Database	Date Only	DateTime	UTC DateTime	Return Type
PostgreSQL	`CURRENT_DATE`	`NOW()`	`NOW() AT TIME ZONE 'UTC'`	DATE / TIMESTAMPTZ
MySQL	`CURRENT_DATE()`, `CURDATE()`	`NOW()`	`UTC_TIMESTAMP()`	DATE / DATETIME
SQL Server	`CAST(GETDATE() AS DATE)`	`GETDATE()`	`GETUTCDATE()`	DATE / DATETIME
Oracle	`TRUNC(SYSDATE)`	`SYSDATE`	`SYS_EXTRACT_UTC(SYSTIMESTAMP)`	DATE / TIMESTAMP
SQLite	`DATE('now')`	`DATETIME('now')`	`DATETIME('now', 'utc')`	TEXT

Keep this reference handy when working across database platforms. The syntax differences are small but the debugging time they cause is not.