How to Get the Current Date in PostgreSQL

Galaxy Glossary

How do I get today’s date in PostgreSQL?

CURRENT_DATE returns the server’s current date (00:00:00 time) as a DATE value.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What does CURRENT_DATE return?

CURRENT_DATE is a SQL standard keyword that returns the date of the database server according to its time zone, with the time portion set to midnight. The result type is DATE, not TIMESTAMP.

How do I select today’s date?

Run SELECT CURRENT_DATE; or SELECT CURRENT_DATE AS today;. PostgreSQL evaluates the keyword once per transaction, so multiple calls in the same query return identical values.

Can I alias CURRENT_DATE in a result set?

Yes.Aliasing improves readability and lets client code reference the column easily: SELECT CURRENT_DATE AS order_cutoff;

How do I use CURRENT_DATE in conditions?

Filter rows created today: SELECT * FROM Orders WHERE order_date = CURRENT_DATE;. Compare with intervals: WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'.

How do I store today’s date in a table?

Use CURRENT_DATE in INSERT or UPDATE statements: INSERT INTO Customers (name,email,created_at) VALUES ('Ana','ana@shop.com',CURRENT_DATE);

What are best practices when using CURRENT_DATE?

Prefer CURRENT_DATE for pure date logic—billing cycles, daily reports.Use CURRENT_TIMESTAMP when you need both date and time. Always be mindful of the server’s time zone.

Code examples for an ecommerce DB

Daily revenue: SELECT SUM(total_amount) FROM Orders WHERE order_date = CURRENT_DATE;

Common mistakes and fixes

Mistake 1: Selecting CURRENT_TIMESTAMP when only the date is required. Fix: Use CURRENT_DATE to avoid implicit casting. Mistake 2: Comparing a TIMESTAMP column directly to CURRENT_DATE without casting. Fix: Cast explicitly or use the ::date shorthand.

Frequently asked questions

Is CURRENT_DATE affected by SET TIMEZONE?

Yes.Changing the session time zone alters the date CURRENT_DATE returns.

Does CURRENT_DATE change during a long transaction?

No. PostgreSQL evaluates it once at the start of the transaction.

.

Why How to Get the Current Date in PostgreSQL is important

How to Get the Current Date in PostgreSQL Example Usage


-- Calculate today’s revenue per product
SELECT p.name, SUM(oi.quantity * p.price) AS revenue_today
FROM Orders o
JOIN OrderItems oi ON o.id = oi.order_id
JOIN Products p ON p.id = oi.product_id
WHERE o.order_date = CURRENT_DATE
GROUP BY p.name
ORDER BY revenue_today DESC;

How to Get the Current Date in PostgreSQL Syntax


SELECT CURRENT_DATE;
-- Alias for clarity
SELECT CURRENT_DATE AS today;

-- Filter today’s orders
SELECT *
FROM Orders
WHERE order_date = CURRENT_DATE;

-- Orders in the last 7 days
SELECT *
FROM Orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days';

-- Insert with default date
INSERT INTO Customers (name, email, created_at)
VALUES ('Liam', 'liam@shop.com', CURRENT_DATE);

Common Mistakes

Using CURRENT_TIMESTAMP when only the date is needed. This forces implicit casts and can prevent index usage. Fix: use CURRENT_DATE.
Comparing a TIMESTAMP column directly to CURRENT_DATE, which returns DATE, causing mismatched types. Fix: cast the TIMESTAMP to DATE or compare with CURRENT_DATE::timestamp.

Frequently Asked Questions (FAQs)

Is CURRENT_DATE the same as NOW()::date?

Functionally yes. NOW() returns a TIMESTAMP that you can cast to DATE, but CURRENT_DATE is cleaner and avoids extra casts.

Can I use CURRENT_DATE in check constraints?

Yes. Example: CHECK (ship_date >= CURRENT_DATE) ensures future shipping dates.

Does CURRENT_DATE respect daylight saving changes?

It follows the server’s time zone settings, so any DST shift applied to the server will reflect in CURRENT_DATE after the change.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie