How to compare dates in PostgreSQL

In data analysis, dates may take a crucial focus across various industries, including finance, statistics, sales, and other businesses. For example, when comparing sales data within a specific date range, companies may make informed forecasts about their future sales or profit. Furthermore, analyzing dates may help businesses define the time periods when sales experienced the highest and lowest growth.

In the article, we will make a brief overview of PostgreSQL date data types and explore how to use the WHERE and BETWEEN clauses, DATE_TRUNC and data type formatting functions, as well as the RANGE type to compare dates in PostgreSQL. To demonstrate the examples of the date comparison, we are going to use our convenient tool - dbForge Studio for PostgreSQL.

What is the datatype of Date and Time in PostgreSQL?

In PostgreSQL, date data types are used to store date and time values, as well as time intervals. PostgreSQL supports the following date data types:

How to compare two dates in PostgreSQL

When you need to compare two dates - the given date value with the one stored in the table, you can use the comparison operators in the WHERE clause. For example, we need to get information about the orders that were set on January 5, 2020. To achieve this, execute the SELECT statement and specify the condition in the WHERE clause. PostgreSQL compares the given value with the dates from the order_date column. Upon the comparison, the matching values will be returned in the result grid.

Use BETWEEN to compare dates

You can use the BETWEEN operator to compare dates when you need to check if a date falls within a specified range. For example, run the following statement:

SELECT
 *
FROM sales.orders
WHERE order_date BETWEEN '01-Jan-2020' AND '31-Jan-2020';

By the way, instead of typing the code manually, you can avoid mistakes in your code by using our advanced PostgreSQL syntax checker available in dbForge Studio for PostgreSQL.

In the example, order_date is the name of the column whose dates we want to compare, and '01-Jan-2020' and '31-Jan-2020' are the dates that define the range we want to check. In the Data Editor, all the rows matching the specified range will be displayed. With PostgreSQL Data Editor built into the Studio, you can view, sort, or group data per your needs.

Use the DATE_TRUNC function

The PostgreSQL DATE_TRUNC function is used to truncate the date and time values to a specific precision (into a whole value), such as 'year', 'month', 'day', 'hour', 'minute', or 'second', in a string format.

The syntax of the function is as follows:

DATE_TRUNC('precision', expression);

where expression is a timestamp or an interval to truncate.

For example, if you want to use the DATE_TRUNC function for the year value, all the timestamp values followed by the year will be truncated to their initial values, i.e. the value of month and day will start from 01, and the value of hour, minute, and second - from 00.

Let's see how the DATE_TRUNC function works. In the example, we run the following SELECT statements and see the result:

-- Use 'year' as a date-part argument
SELECT DATE_TRUNC('year', TIMESTAMP '2005-05-25 11:30);

-- Use 'month' as a date-part argument
SELECT DATE_TRUNC('month', TIMESTAMP '2005-05-25 11:30:37');

In the result, we'll get the following:

• Use 'year' as a date-part argument: The DATE_TRUNC function will truncate the values to January 1st, and the value of hour, minute, and second will start from 00.
• Use 'month' as a date-part argument: The DATE_TRUNC function will truncate the values to May 1st, and the value of hour, minute, and second will start from 00.

Compare dates using the RANGE type

The RANGE type stores a range of values starting from the lowest value to the highest one. Every range has a lower bound and an upper bound. The range includes all values between these bounds. The '[' and ']' symbols represent the inclusive lower or upper bounds and they are included in the range. The '(' and ')' symbols serve for the exclusive lower or upper bounds, which means that they are not included in the range. The RANGE for the date data type supports the following types:

• tsrange: Range of timestamp values without time zone
• tstzrange: Range of timestamp values with time zone
• daterange: Range of date values

Let's consider the following example. We need to find all the bookings and check whether the specified ranges overlap, i.e. have any dates in common.

In this example, the daterange() function creates a daterange object from the start_date and end_date columns for each row in the bookings table. The '[)' parameter indicates that the range is inclusive on the lower bound and exclusive on the upper bound. The '&&' operator checks for common dates in the ranges. In the output, the query compares the given condition with the values stored in the table and returns the rows from the table where the date range of the booking overlaps with the specified range [2022-01-01, 2022-12-31).

Compare dates using the data type formatting functions

If you want to output the date values in a specified format, you can use the TO_CHAR() function that will change the output format of a given input date value to the specified format.

The basic syntax is as follows:

TO_CHAR(input_date, 'format');

where input_value is the column name whose date datatype you want to convert, and 'format' is the new output format.

For example, we can use the TO_CHAR() function to convert the dates to a specific format and then compare the result as strings. Let's execute the following SELECT statement and see the result:

As you can see, the TO_CHAR() function is used to get the year component of the order_date column as a string in the format of 'YYYY'. By comparing the order_date values stored in the orders table to the value '2022', the statement returns only those rows where the order date is within 2022.

Compare dates with the current date in PostgreSQL

When you need to get the current date, you can use either NOW or CURRENT_DATE functions that differ in the information they return. The NOW function outputs the date and time of the server, while the CURRENT_DATE function returns only the date of the server. For example, let's use the NOW function to get the current date.

For example, you need to check the orders that should be delivered today - April 25, 2023. To achieve this, you can use the NOW() function in the WHERE clause to compare a given (current date) with those from the table. In the result, you'll get those orders that match your condition.

Consider the following statement:

SELECT
  *
FROM neworders n
WHERE n.shipped_date = NOW() :: DATE;

The date values from the shipped_date column stored in the neworders table will be compared to the current date and then the SELECT statement will return only those orders that match the current date.

Conclusion

In the article, we have learned different ways to compare dates in PostgreSQL, including the use of functions such as NOW(), DATE_TRUNC(), TO_CHAR(), as well as comparison operators. To demonstrate how these techniques work, we used dbForge Studio for PostgreSQL, a powerful IDE that offers comprehensive tools for database development, deployment, and management in PostgreSQL. With its intuitive user interface and advanced functionality, the Studio can enhance your productivity and help you streamline your PostgreSQL workflows. To experience the benefits of this all-in-one IDE, download a 30-day trial version and evaluate its versatile features and capabilities!