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:
1 | sudo su - postgres |
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:
1 | CREATE DATABASE billings; |
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:
1 2 | CREATE TABLE TABLE_NAME( COL NAME + DATA TYPE + COL_CONSTRAINT); |
Here’s the statement we’ll use to create our sample table:
1 2 3 4 5 | CREATE TABLE collection( name text, billing_date DATE, due_date DATE, collection_date DATE); |
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:
1 2 | INSERT INTO TABLE_NAME(col_1, col_2 . . . ,) VALUES (value_1,value_2. . . ,); |
NOTE: When you list values to insert, they should be in the same order as their respective column names.
1 2 3 4 5 6 7 | name | billing_date | due_date | collection_date ----------+--------------+------------+----------------- sally | 2020-01-02 | 2020-01-05 | 2020-01-10 jeremiah | 2020-01-03 | 2020-01-06 | 2020-01-20 ensim | 2020-01-01 | 2020-01-09 | 2020-01-06 joven | 2020-01-05 | 2020-01-12 | 2020-01-07 (4 ROWS) |
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:
1 2 3 | SELECT col_1,col_2...,FROM TABLE_NAME ORDER BY col_1 [ASC | DESC], col_2 [ASC | DESC]..., |
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:
1 2 3 | SELECT billing_date, FROM collection ORDER BY billing_date DESC; |
The result of this query would be:
1 2 3 4 5 6 7 | billing_date -------------- 2020-01-05 2020-01-03 2020-01-02 2020-01-01 (4 rows) |
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:
1 | SELECT due_date FROM collection ORDER BY due_date ASC; |
This example should return records sorted by the due_date
field in ascending order.
The result of this query would be:
1 2 3 4 5 6 7 | due_date ------------ 2020-01-05 2020-01-06 2020-01-09 2020-01-12 (4 rows) |
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:
1 2 3 4 | SELECT due_date,collection_date FROM collection ORDER BY due_date ASC, collection_date DESC; |
The output will look like this:
1 2 3 4 5 6 7 | due_date | collection_date ------------+----------------- 2020-01-05 | 2020-01-10 2020-01-06 | 2020-01-20 2020-01-09 | 2020-01-06 2020-01-12 | 2020-01-07 (4 rows) |
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:
1 2 | SELECT column_name FROM TABLE [WHERE conditions]ORDER BY column_name; |
Here’s an example of how it works:
1 2 3 | SELECT billing_date FROM collection WHERE name = 'joven' ORDER BY billing_date ASC; |
For this example, the output would be:
1 2 3 4 | billing_date -------------- 2020-01-05 (1 row) |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started