How to Get Current Date in PostgreSQL

How do I get today’s date in Amazon Redshift?

CURRENT_DATE returns today’s date (00:00:00) as a DATE value in Redshift and PostgreSQL.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is CURRENT_DATE in Redshift?

CURRENT_DATE is a built-in, zero-argument function that returns the current date based on the cluster’s time zone. The time component is set to 00:00:00, so the result is of type DATE, not TIMESTAMP.

How do I retrieve today’s date?

Run SELECT CURRENT_DATE; and Redshift will return the date for the moment the query starts. No parentheses are required, but CURRENT_DATE() also works.

Can I use CURRENT_DATE in calculations?

Yes. You can add or subtract integer days. Example: CURRENT_DATE - 7 returns the date seven days ago; CURRENT_DATE + 30 returns the date 30 days ahead.

How do I filter rows from the last 24 hours?

Because CURRENT_DATE has no time, combine it with CURRENT_TIMESTAMP when you need precision. Example filter: WHERE order_date >= CURRENT_DATE - 1 includes all yesterday’s rows.

What about CURRENT_TIMESTAMP, SYSDATE, and GETDATE?

All three return TIMESTAMP values. Use them when you need both date and time. CURRENT_DATE is best when time is irrelevant and you want implicit midnight truncation.

Best practice: keep types consistent

Compare DATE to DATE and TIMESTAMP to TIMESTAMP. Mixing types forces implicit casts that prevent index use and slow queries.

Why How to Get Current Date in PostgreSQL is important

How to Get Current Date in PostgreSQL Example Usage


-- Show today’s orders and customer emails
SELECT c.name,
       c.email,
       o.id          AS order_id,
       o.total_amount
FROM   Orders       o
JOIN   Customers    c ON c.id = o.customer_id
WHERE  o.order_date = CURRENT_DATE;

How to Get Current Date in PostgreSQL Syntax


SELECT CURRENT_DATE;
-- or
SELECT CURRENT_DATE() AS today;

-- Date arithmetic
SELECT CURRENT_DATE - 7  AS seven_days_ago,
       CURRENT_DATE + 30 AS in_30_days;

-- Example filter in ecommerce context
SELECT id, order_date, total_amount
FROM   Orders
WHERE  order_date = CURRENT_DATE;

Common Mistakes

Using CURRENT_DATE to compare with TIMESTAMP columns. This forces an implicit cast on every row. Fix by casting the TIMESTAMP column to DATE or using CURRENT_TIMESTAMP instead.
Adding INTERVAL literals to CURRENT_DATE. Redshift supports integer day math, not INTERVAL types. Use CURRENT_DATE + 7, not CURRENT_DATE + INTERVAL '7 days'.