PostgreSQL Order By Date

In Postgresql, we will use the ORDER BY clause in our SELECT statement to perform a query sorted by date. In this article, I’ll cover everything you need to know about ordering results by date in PostgreSQL, from basic sorting to advanced date manipulation techniques.

PostgreSQL Order By Date

Before diving into specific commands, it’s quite necessary to understand how PostgreSQL handles dates. PostgreSQL supports several temporal data types, including:

• DATE: Stores date values only (year, month, day)
• TIME: Stores time values only (hour, minute, second)
• TIMESTAMP: Stores both date and time values
• TIMESTAMPTZ: Stores timestamp with time zone information

PostgreSQL stores dates in a standardized internal format, making them easy to sort once you understand the syntax.

Example-1: Basic Date Sorting in PostgreSQL

The query below will return the records lists from the sales table in ascending order by sale_date.

SELECT product_name, amount, sale_date
FROM sales
ORDER BY sale_date;

After executing the above query, I got the expected output, as shown in the screenshot below.

Now, if you wish to get the lists of records in Descending order, you can use the

SELECT product_name, amount, sale_date
FROM sales
ORDER BY sale_date DESC;

After executing the above query, I got the expected output successfully, as shown in the screenshot below.

Example-2: Sorting by Multiple Date Components

The query below sorts first by year and then by month within each year.

SELECT 
    product_name,
    sale_date,
    amount
FROM sales
ORDER BY 
    EXTRACT(YEAR FROM sale_date),
    EXTRACT(MONTH FROM sale_date);

After executing the above query, I got the expected output, as shown in the screenshot below.

Example-3: Sorting by Dates With Time Zone Considerations

We can execute the below query to use the Sorting by Dates With Time Zone.

SELECT 
    product_name,
    sale_date AT TIME ZONE 'America/Chicago' AS chicago_time
FROM sales
ORDER BY sale_date AT TIME ZONE 'America/Chicago';

After executing the above query, I got the expected output, as shown in the screenshot below.

Example-4: Handling NULL Dates in Your Sorting

By default, NULL values appear last in ascending order and first in descending order. I often use NULLS FIRST or NULLS LAST to control this behavior explicitly. The below query will get you the output with the Null values at the last.

SELECT *
FROM Sales
ORDER BY sale_date DESC NULLS LAST;

After executing the above query, I got the expected output, as shown in the below screenshot.

Conclusion

Mastering PostgreSQL’s date sorting capabilities plays an important role while working with different applications. With the techniques I’ve shared in this article, you’ll be able to handle any date-based sorting challenge that comes your way.

You may also like the articles below.

• Postgresql order by limit
• PostgreSQL Order By
• PostgreSQL INSERT Multiple Rows (Complete tutorial)
• PostgreSQL INSERT INTO table