How to use CURRENT_DATE in PostgreSQL

How do I use CURRENT_DATE to get today’s date in PostgreSQL?

CURRENT_DATE returns the current session-timezone date as a value of type date.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does CURRENT_DATE return?

CURRENT_DATE yields the calendar date based on the session’s time zone, with the data type date. It contains no time-of-day component, so comparisons and formatting are straightforward.

How do I get today’s date in a query?

Run SELECT CURRENT_DATE; to see today’s date. In a script, assign an alias for clarity: SELECT CURRENT_DATE AS today;.

What is the exact syntax of CURRENT_DATE?

CURRENT_DATE is a SQL standard special register—no parentheses needed. You can optionally cast or alias it. See full syntax below.

Can I filter rows using CURRENT_DATE?

Yes. Use it in WHERE clauses to fetch records created today: ... WHERE created_at::date = CURRENT_DATE.

How do I offset the current date?

Add or subtract intervals: CURRENT_DATE + INTERVAL '7 days' returns the date one week ahead; subtract for past dates.

Does CURRENT_DATE respect my time zone?

It reflects the session time zone. Execute SHOW TIME ZONE; or set one with SET TIME ZONE 'America/New_York'; before calling CURRENT_DATE.

Best practices for CURRENT_DATE

Cast timestamp columns to date before comparison, index created_at when filtering by date, and avoid wrapping CURRENT_DATE in functions that prevent index use.

Why How to use CURRENT_DATE in PostgreSQL is important

How to use CURRENT_DATE in PostgreSQL Example Usage


-- Retrieve today’s orders and customer info
SELECT o.id,
       c.name,
       o.total_amount
FROM Orders AS o
JOIN Customers AS c ON c.id = o.customer_id
WHERE o.order_date = CURRENT_DATE
ORDER BY o.total_amount DESC;

How to use CURRENT_DATE in PostgreSQL Syntax


-- Basic
SELECT CURRENT_DATE;

-- Alias for readability
SELECT CURRENT_DATE AS today;

-- Use in WHERE clause (ecommerce example)
SELECT *
FROM Orders
WHERE order_date = CURRENT_DATE;

-- Offset by interval
SELECT CURRENT_DATE + INTERVAL '1 day'  AS tomorrow;

-- Assign to variable (plpgsql)
DECLARE
    report_date date := CURRENT_DATE;
BEGIN
    -- logic here
END;

Common Mistakes

Casting CURRENT_DATE to timestamp unnecessarily. CURRENT_DATE is already a date; casting to timestamp adds a 00:00:00 time, which may break equality checks. Skip the cast or compare dates directly.
Comparing a timestamp column directly to CURRENT_DATE. The types differ, leading to implicit casts that bypass indexes. Convert the timestamp column with <code>column::date</code> first or store a separate date column.