如何在PostgreSQL数据库中仅按日期（不含时间戳）查询数据？

Got it, let's break down how to pull data based solely on the date part—ignoring the hours/minutes/seconds in your timestamp columns—this is a super common task, so I’ve got a few reliable approaches to share.

1. Convert the Timestamp to Date (Simple, but Watch Indexes) #

The easiest way is to cast your timestamp column to a DATE type, then compare it directly to your target date. You can do this with either the DATE() function or PostgreSQL's type casting shorthand ::DATE.

Examples: #

• Using DATE() function:

  SELECT * FROM your_table
  WHERE DATE(created_at) = '2024-05-20';
  

• Using type casting:

  SELECT * FROM your_table
  WHERE created_at::DATE = '2024-05-20';
  

Heads up: This works great for small datasets, but if your table is large and you have an index on the timestamp column, using these functions/casts will bypass the index. That means PostgreSQL has to scan every row to convert the timestamp, which can slow things down.

2. Range Query (Index-Friendly, Best for Large Tables) #

If performance matters (especially with big datasets), use a range condition instead of modifying the column. This lets PostgreSQL leverage any existing index on your timestamp column.

The idea is to query all timestamps that fall on or after the start of your target date, and before the start of the next day.

Example: #

SELECT * FROM your_table
WHERE created_at >= '2024-05-20'::TIMESTAMP
  AND created_at < '2024-05-21'::TIMESTAMP;

You can also write this more cleanly using DATE_TRUNC if you prefer:

SELECT * FROM your_table
WHERE created_at >= DATE_TRUNC('day', '2024-05-20'::TIMESTAMP)
  AND created_at < DATE_TRUNC('day', '2024-05-20'::TIMESTAMP) + INTERVAL '1 day';

This approach is fast because it doesn’t alter the column value—PostgreSQL can directly use the index to find matching rows.

3. Handling Timezone-Aware Timestamps (TIMESTAMPTZ) #

If your column is a TIMESTAMPTZ (timestamp with time zone), you need to account for time zones to avoid getting incorrect date matches. For example, a timestamp like 2024-05-20 23:30:00 UTC might be 2024-05-21 in a different time zone.

Example (using UTC as the reference time zone): #

SELECT * FROM your_table
WHERE created_at AT TIME ZONE 'UTC'::DATE = '2024-05-20';

Or for an index-friendly range query:

SELECT * FROM your_table
WHERE created_at >= '2024-05-20 00:00:00 UTC'
  AND created_at < '2024-05-21 00:00:00 UTC';

Quick Summary #

• Small tables: Use DATE() or ::DATE for simplicity.
• Large tables: Stick to range queries to keep index usage intact and maintain performance.
• Timezone-aware columns: Always specify the time zone to avoid date mismatches across regions.

内容的提问来源于stack exchange，提问作者Chaitanya Mahamana