How to Query Date and Time in PostgreSQL

PostgreSQL provides a wide range of functions and operators to handle date and time data types efficiently. This article explores how to query date and time values, perform comparisons, and manipulate data using SQL functions and operators in PostgreSQL.

1. Querying Date and Time Values

In PostgreSQL, the DATE type stores the date (year, month, and day), while the TIME type stores the time of day without any time zone. The TIMESTAMP type combines both date and time information.

SELECT * FROM events WHERE event_date = '2025-05-01';

This query fetches records from the events table where the event_date is '2025-05-01'.

2. Working with Date and Time Functions

PostgreSQL has built-in functions to manipulate date and time data. Some of the most commonly used functions are:

• CURRENT_DATE: Returns the current date.
• CURRENT_TIME: Returns the current time.
• CURRENT_TIMESTAMP: Returns the current date and time.
• DATE_PART: Extracts a specific part of the date (e.g., year, month, day).

SELECT CURRENT_TIMESTAMP;

This query returns the current date and time.

3. Date and Time Comparisons

You can compare dates and times using standard comparison operators like =, !=, >, and <. Here’s an example of filtering records that occurred after a specific date:

SELECT * FROM meetings WHERE meeting_date > '2025-01-01';

This query retrieves records where the meeting_date is after January 1st, 2025.

4. Timezone Handling

PostgreSQL supports time zone-aware date and time types. The TIMESTAMP WITH TIME ZONE type allows storing both date, time, and timezone information. You can convert time zones with the AT TIME ZONE operator.

SELECT event_date AT TIME ZONE 'UTC' FROM events;

This query converts the event_date to UTC time zone.

5. Extracting Parts of a Date or Time

If you need to extract specific parts of a date or time, PostgreSQL’s DATE_PART function is extremely useful:

SELECT DATE_PART('year', CURRENT_DATE);

This query returns the current year.