How to CURRENT_DATE in PostgreSQL

Galaxy Glossary

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

CURRENT_DATE returns the server-side current date (without time) as a DATE value.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does CURRENT_DATE return?

CURRENT_DATE returns the date according to the server’s timezone. It comes back as a DATE type (YYYY-MM-DD) with no time component, making it ideal for date comparisons, partitioning, and audit fields.

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

Place CURRENT_DATE anywhere a DATE literal is allowed. Example: SELECT CURRENT_DATE; or include it in a SELECT list alongside other columns.

How can I offset the current date?

Add or subtract INTERVAL values. Example: CURRENT_DATE + INTERVAL '7 days' gets the date one week from today. Use negative intervals for past dates.

Can I store CURRENT_DATE in a table?

Yes. Insert it directly or set a column’s default to CURRENT_DATE. This ensures every new row captures the insert date automatically.

Best practices for CURRENT_DATE

Use CURRENT_DATE for date-only logic such as report cut-offs. For precise timestamps, prefer NOW() or CURRENT_TIMESTAMP. Keep server timezone settings consistent across environments.

Common use cases in ecommerce datasets?

Filter today’s orders, calculate customer tenure, or build daily inventory snapshots. It pairs well with BETWEEN for date ranges like “today”.

Why How to CURRENT_DATE in PostgreSQL is important

How to CURRENT_DATE in PostgreSQL Example Usage


-- List today’s orders with product counts
SELECT o.id AS order_id,
       c.name AS customer_name,
       COUNT(oi.id) AS items,
       CURRENT_DATE AS report_date
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
JOIN OrderItems oi ON oi.order_id = o.id
WHERE o.order_date = CURRENT_DATE
GROUP BY o.id, c.name;

How to CURRENT_DATE in PostgreSQL Syntax


-- Basic usage
SELECT CURRENT_DATE;

-- Offset by days
SELECT CURRENT_DATE + INTERVAL '3 days' AS ship_date;

-- As a column default
ALTER TABLE Orders
ADD COLUMN order_day DATE DEFAULT CURRENT_DATE;

-- In comparisons
SELECT *
FROM Orders
WHERE order_date = CURRENT_DATE;

-- Joined example with ecommerce tables
SELECT c.id, c.name, o.id AS order_id, CURRENT_DATE AS today
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
WHERE o.order_date = CURRENT_DATE;

Common Mistakes

Using CURRENT_TIMESTAMP when only the date is needed. The time portion breaks <code>=</code> comparisons. Fix by switching to CURRENT_DATE or casting: <code>current_timestamp::date</code>.
Assuming CURRENT_DATE respects client timezone. It follows the server’s timezone. Fix by aligning server settings or using AT TIME ZONE for explicit conversions.