PostgreSQL CURRENT_DATE, CURRENT_TIME Functions

The CURRENT_DATE and CURRENT_TIME functions in PostgreSQL return the current date and current time respectively. These SQL standard functions are useful for date-based queries, default values, and time-sensitive operations.

Syntax

CURRENT_DATE
-- Returns: date (no time zone)

CURRENT_TIME
-- Returns: time with time zone

CURRENT_TIMESTAMP
-- Returns: timestamp with time zone (equivalent to NOW())

Official Documentation

Example usage

-- Get current date
SELECT CURRENT_DATE AS today;
-- Result: 2025-12-26

-- Get current time
SELECT CURRENT_TIME AS now_time;
-- Result: 14:30:45.123456+00

-- Get current timestamp
SELECT CURRENT_TIMESTAMP AS now_timestamp;
-- Result: 2025-12-26 14:30:45.123456+00

-- Find records created today
SELECT * FROM orders
WHERE created_at::DATE = CURRENT_DATE;

-- Calculate age
SELECT
    birth_date,
    CURRENT_DATE - birth_date AS days_old,
    AGE(CURRENT_DATE, birth_date) AS age
FROM users;

-- Set default date in table
CREATE TABLE daily_reports (
    id SERIAL PRIMARY KEY,
    report_date DATE DEFAULT CURRENT_DATE,
    data JSONB
);

-- Find upcoming events
SELECT event_name, event_date
FROM events
WHERE event_date >= CURRENT_DATE
  AND event_date <= CURRENT_DATE + INTERVAL '30 days'
ORDER BY event_date;

-- Business day calculations
SELECT
    CURRENT_DATE AS today,
    CURRENT_DATE + 1 AS tomorrow,
    CURRENT_DATE - 1 AS yesterday,
    EXTRACT(DOW FROM CURRENT_DATE) AS day_of_week;

-- Time-based access control
SELECT * FROM content
WHERE publish_date <= CURRENT_DATE
  AND (expire_date IS NULL OR expire_date > CURRENT_DATE);

Common issues

1. No parentheses: CURRENT_DATE and CURRENT_TIME are used without parentheses (unlike NOW()).
2. Time zone considerations: CURRENT_TIME includes time zone, while LOCALTIME does not.
3. Comparison with timestamps: When comparing CURRENT_DATE with timestamp columns, cast appropriately.
4. Transaction consistency: Like NOW(), these return transaction start time, not statement execution time.

Best practices

1. Use CURRENT_DATE for date-only comparisons to avoid time zone complications.
2. For date range queries, use >= CURRENT_DATE AND < CURRENT_DATE + 1 instead of date truncation.
3. Consider using LOCALTIME and LOCALTIMESTAMP when time zone is not needed.
4. Store dates in ISO format (YYYY-MM-DD) for consistency across time zones.

Frequently Asked Questions

Q: Why don't CURRENT_DATE and CURRENT_TIME use parentheses?
A: They're SQL standard keywords, not functions, so they don't require parentheses. However, CURRENT_DATE() will also work in PostgreSQL for compatibility.

Q: What's the difference between CURRENT_TIME and LOCALTIME?
A: CURRENT_TIME returns time with time zone, while LOCALTIME returns time without time zone. Use LOCALTIME when you don't need time zone information.

Q: How do I get yesterday's or tomorrow's date?
A: Use interval arithmetic: CURRENT_DATE - 1 for yesterday, CURRENT_DATE + 1 for tomorrow, or CURRENT_DATE - INTERVAL '1 day'.

Q: Are CURRENT_DATE values consistent within a transaction?
A: Yes, like NOW(), CURRENT_DATE returns the transaction start date. All calls within the same transaction return the same value.

Q: How do I find all records from today regardless of time?
A: Use WHERE created_at::DATE = CURRENT_DATE or WHERE created_at >= CURRENT_DATE AND created_at < CURRENT_DATE + 1.