Filter PostgreSQL Records Relative to Today

Today, the company I work for released a new membership feature. We don’t have an internal tool to monitor it yet, so I’m checking the records directly in the database.

I needed a query to see which memberships were created today. I could put a date in the query, but then I’d have to change it every morning. PostgreSQL has CURRENT_DATE, which makes this much more convenient.

The examples below use a table called memberships with a created_at timestamp column. Replace those names with the ones in your database.

Records created before today #

To get memberships created before today:

SELECT *
FROM memberships
WHERE created_at < CURRENT_DATE
ORDER BY created_at DESC;

When PostgreSQL compares a timestamp with CURRENT_DATE, the date represents midnight at the start of today. So this query includes yesterday and everything before it.

Records created today #

For today’s memberships, use a range:

SELECT *
FROM memberships
WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + 1
ORDER BY created_at DESC;

Adding 1 to a date gives the next day’s date. The query includes today’s midnight and stops just before tomorrow’s midnight.

For example, if today is October 1:

It’s tempting to write created_at = CURRENT_DATE, but for a timestamp column that only matches records at exactly midnight. PostgreSQL doesn’t automatically remove the time from created_at. If the column is a date rather than a timestamp, equality works as expected. See the PostgreSQL date/time documentation.

You can also cast the column to a date:

SELECT *
FROM memberships
WHERE created_at::date = CURRENT_DATE;

That reads nicely, but I prefer the range query. It lets PostgreSQL use a regular index on created_at when the planner finds it useful. Casting the column generally needs a matching expression index to get the same benefit. PostgreSQL explains this in its documentation on expression indexes.

Records created yesterday #

The same pattern works for yesterday:

SELECT *
FROM memberships
WHERE created_at >= CURRENT_DATE - 1
  AND created_at < CURRENT_DATE
ORDER BY created_at DESC;

This is useful when I want to check the previous day’s memberships without editing dates in the query.

Records created in the last 24 hours #

Sometimes the question is slightly different: how many memberships were created in the last 24 hours?

SELECT *
FROM memberships
WHERE created_at >= NOW() - INTERVAL '24 hours'
  AND created_at < NOW()
ORDER BY created_at DESC;

At 2 PM, that window starts at 2 PM yesterday. The query for today starts at midnight instead.

NOW() includes the time and returns a timestamp with time zone. One detail from the PostgreSQL docs: both NOW() and CURRENT_DATE are based on the start of the current transaction. In a long-running transaction, they won’t keep advancing as you run more queries.

Check what “today” means in your session #

Before relying on these queries, check the database session’s time zone:

SHOW TIME ZONE;

For a timestamptz column, the day boundaries above follow that session time zone. If you’re checking memberships by Jakarta’s calendar day, you can set it for your connection:

SET TIME ZONE 'Asia/Jakarta';

This doesn’t change the stored timestamps. It changes how that session interprets dates and displays timestamps with time zone. A timestamp without time zone column has no time zone attached, so you need to know whether your application stores local time or UTC in it. Changing the session time zone won’t convert those values for you. The date/time types documentation covers the distinction.

For my membership checks, the query for today is the one I can keep coming back to. I can save it and run it again tomorrow without changing a date.