How to Execute a Psql Sort by Date Query

Introduction

When you perform a query in PostgreSQL, you may want your results to come back in a particular order. Sorting by date is one of the most common ways to order results. In this article, we’ll explain how to perform a psql sort by date query, using the ORDER BY clause to specify our chosen sort order in the SELECT statement.

Prerequisites

Before you begin this tutorial, make sure that PostgreSQL is installed and configured on your machine. You should also have some working knowledge of PostgreSQL in order to follow along with the examples in this article.

Access the Command-line Interface

To execute queries in psql, you’ll need to log in as the ‘postgres’ superuser. You would then use the command psql to connect to the command-line console.

First, you’ll enter the command shown below:

You’ll be prompted for a password. Enter it and then use the command psql to begin querying data in the PostgreSQL database.

PostgreSQL Sort by Date

To perform a query that’s sorted by date, you use the ORDER BY clause in your SELECT statement. This clause allows you to sort rows in ascending or descending order based on the specified criteria.

By default, PostgreSQL queries are returned in an unspecified order. Therefore, you can control how the results are sorted by using ORDER BY.

Creating a Database in PostgreSQL

Let’s begin by creating a PostgreSQL database. Later, we’ll create a table within this database with sample data that we can use in our examples.

To create a new database, we can use the following command:

If you’d like to see a list of all databases, use the command \l.

To connect to your new database, enter \c followed by the database name. You’ll get a response from PostgreSQL that you are now connected to the database.

Creating a Table in PostgreSQL

To create a new table, we use the CREATE TABLE statement. The basic syntax of this statement is shown below:

Here’s the statement we’ll use to create our sample table:

Notice that the table name always comes after the CREATE TABLE statement. After that comes the list of column names, their data types and any optional constraints.

Inserting Data into the PostgreSQL Table

Now that we created a table, let’s use an INSERT INTO statement to add some records to our table:

NOTE: When you list values to insert, they should be in the same order as their respective column names.

You can see the unspecified order of dates in the example above.

Sorting the Date in a PostgreSQL Table

In PostgreSQL, sorting by date requires the ORDER BY clause, which is used in conjunction with a SELECT statement.

The syntax for ORDER BY clause is:

Sorting the Date in Descending Order

Now that we understand how the ORDER BY clause works to oontrol sorting, let’s look at an example where we sort the billing_date in descending order:

The result of this query would be:

Sorting the Date in Ascending Order

In PostgreSQL, it’s possible to use the ORDER BY clause to sort the date in either ascending or descending order. When you use the ORDER BY clause, ascending order is used as the default when no ASC or DESC modifier is specified.

Let’s look at an example:

This example should return records sorted by the due_date field in ascending order.

The result of this query would be:

NOTE: You can omit the ASC attribute from your ORDER BY clause when you sort in ascending order since the ascending attribute is set as default.

Sorting by Date in Multiple Columns

You can even use the ORDER BY clause to sort on multiple columns in PostgreSQL.

Let’s say we want to sort by _duedate in ascending order but sort by _collectiondate in descending order. We’d use the SELECT statement shown below:

The output will look like this:

It’s clear that both sort orders specified in the ORDER BY clause are apparent in the query results.

Sorting Date using WHERE Clause in PostgreSQL

In PostgreSQL, you can also sort data using a WHERE clause in your SELECT statement. This will allow you to filter out rows that you don’t want included in your results.

The syntax for this type of query is shown below:

Here’s an example of how it works:

For this example, the output would be:

Notice that the ORDER BY clause returned the records sorted by the billing date field in ascending order, and the WHERE clause limited results to those whose name field was equal to ‘joven’ in the collection table.

Conclusion

If you need your query results to be returned in a certain order, the ORDER BY clause provides a simple way to get the job done. In this article, we showed you how to perform a psql sort by date query, adding the ORDER BY clause to a SELECT statement to specify a particular sort order. With our examples to guide you, you’ll be able to create your own PostgreSQL queries that are sorted by date.