This week, I’ve got a request from our project manager to make an update to an existing feature. The update required the feature to have groups by date, which are shown descending, and then in those days, the times should be in ascending order.
Everything I’ve written here is executed on a Mac, iTerm 2 command line and using
pgcli tool. It’s similar to psql, just with autocomplete and some other neat features.
To make a clear example, I’ll create a database, and populate it with some data. You can follow along.
CREATE DATABASE order_by_sample;
-- connect to that database
-- and add extension for generating id using UUID
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE sample (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
date_created TIMESTAMP WITH TIME ZONE DEFAULT now(),
note text
);
-- insert some data to work with
INSERT INTO sample (date_created, note) VALUES (now() - '1 hour'::INTERVAL,'1');
INSERT INTO sample (date_created, note) VALUES (now() - '2 hours'::INTERVAL,'2');
INSERT INTO sample (date_created, note) VALUES (now() - '3 hours'::INTERVAL,'3');
INSERT INTO sample (date_created, note) VALUES (now() - '4 hours'::INTERVAL,'4');
INSERT INTO sample (date_created, note) VALUES (now() - '5 hours'::INTERVAL,'5');
INSERT INTO sample (date_created, note) VALUES (now() - '1 day 1 hour'::INTERVAL,'6');
INSERT INTO sample (date_created, note) VALUES (now() - '1 day 2 hours'::INTERVAL,'7');
INSERT INTO sample (date_created, note) VALUES (now() - '1 day 3 hours'::INTERVAL,'8');
INSERT INTO sample (date_created, note) VALUES (now() - '1 day 4 hours'::INTERVAL,'9');
INSERT INTO sample (date_created, note) VALUES (now() - '1 day 5 hours'::INTERVAL,'10');
INSERT INTO sample (date_created, note) VALUES (now() - '2 days 1 hour'::INTERVAL,'11');
INSERT INTO sample (date_created, note) VALUES (now() - '2 days 2 hours'::INTERVAL,'12');
INSERT INTO sample (date_created, note) VALUES (now() - '2 days 3 hours'::INTERVAL,'13');
INSERT INTO sample (date_created, note) VALUES (now() - '2 days 4 hours'::INTERVAL,'14');
INSERT INTO sample (date_created, note) VALUES (now() - '2 days 5 hours'::INTERVAL,'15');
I’ve used interval for creating different times. It’s nice that it’s descriptive, so I can write day or days
interchangeably. I’ve noticed that it works like that when I’ve seen that we have an enum in our code that are in
plural, like HOURS, DAYS, WEEKS - and then I looked at Postgres manual to see that it uses singular. But I tried
with plural anyway and it worked. You can write 1 days too, even if it’s a grammatical error, it’s not for postgres.
Back to the example, let’s see what’s in sample table:
SELECT * FROM sample;
+--------------------------------------+-------------------------------+--------+
| id | date_created | note |
|--------------------------------------+-------------------------------+--------|
| 34d20fb6-9d53-45d1-9a97-5a89995253d7 | 2019-08-18 06:04:09.050167+02 | 1 |
| 6d9b8206-7942-4a00-8f82-54882f1f6391 | 2019-08-18 05:04:09.051335+02 | 2 |
| 4469966c-ce64-4229-90d1-c88b0b226b1e | 2019-08-18 04:04:09.051906+02 | 3 |
| f3dbda1e-369e-48bd-8002-4fa9347c9870 | 2019-08-18 03:04:09.052571+02 | 4 |
| ae57e584-c3c2-41bd-a533-94f09f561d75 | 2019-08-18 02:04:09.053061+02 | 5 |
| c7995f72-026c-40a6-b57d-e137b5080df9 | 2019-08-17 06:04:09.05356+02 | 6 |
| 1172baeb-5137-4a99-887d-b8a99c00ec93 | 2019-08-17 05:04:09.054049+02 | 7 |
| a873fb06-4617-499b-99d6-a447f5090a94 | 2019-08-17 04:04:09.05453+02 | 8 |
| fd49820b-d2be-4509-be08-fd8039090daa | 2019-08-17 03:04:09.055001+02 | 9 |
| f4ae359b-7e65-40e4-b35c-33963b3432f9 | 2019-08-17 02:04:09.055475+02 | 10 |
| 39ed438d-9f74-493a-9db4-a793bcdd43dc | 2019-08-16 06:04:09.055901+02 | 11 |
| 2d579b1f-26f5-4490-a804-ef0be9eaf266 | 2019-08-16 05:04:09.056367+02 | 12 |
| e35fd6d9-9849-41bc-9df3-6e16880ffa3e | 2019-08-16 04:04:09.05683+02 | 13 |
| ebc98756-05b3-4242-8a58-cb4be4d23483 | 2019-08-16 03:04:09.057394+02 | 14 |
| 9564c34c-15cc-4069-a41b-8acb4f10241d | 2019-08-16 02:04:09.057979+02 | 15 |
+--------------------------------------+-------------------------------+--------+
Now we want to order by date_created, first just date descending, then by time ascending.
SELECT * FROM sample ORDER BY date_created::DATE DESC, date_created ASC;
+--------------------------------------+-------------------------------+--------+
| id | date_created | note |
|--------------------------------------+-------------------------------+--------|
| ae57e584-c3c2-41bd-a533-94f09f561d75 | 2019-08-18 02:04:09.053061+02 | 5 |
| f3dbda1e-369e-48bd-8002-4fa9347c9870 | 2019-08-18 03:04:09.052571+02 | 4 |
| 4469966c-ce64-4229-90d1-c88b0b226b1e | 2019-08-18 04:04:09.051906+02 | 3 |
| 6d9b8206-7942-4a00-8f82-54882f1f6391 | 2019-08-18 05:04:09.051335+02 | 2 |
| 34d20fb6-9d53-45d1-9a97-5a89995253d7 | 2019-08-18 06:04:09.050167+02 | 1 |
| f4ae359b-7e65-40e4-b35c-33963b3432f9 | 2019-08-17 02:04:09.055475+02 | 10 |
| fd49820b-d2be-4509-be08-fd8039090daa | 2019-08-17 03:04:09.055001+02 | 9 |
| a873fb06-4617-499b-99d6-a447f5090a94 | 2019-08-17 04:04:09.05453+02 | 8 |
| 1172baeb-5137-4a99-887d-b8a99c00ec93 | 2019-08-17 05:04:09.054049+02 | 7 |
| c7995f72-026c-40a6-b57d-e137b5080df9 | 2019-08-17 06:04:09.05356+02 | 6 |
| 9564c34c-15cc-4069-a41b-8acb4f10241d | 2019-08-16 02:04:09.057979+02 | 15 |
| ebc98756-05b3-4242-8a58-cb4be4d23483 | 2019-08-16 03:04:09.057394+02 | 14 |
| e35fd6d9-9849-41bc-9df3-6e16880ffa3e | 2019-08-16 04:04:09.05683+02 | 13 |
| 2d579b1f-26f5-4490-a804-ef0be9eaf266 | 2019-08-16 05:04:09.056367+02 | 12 |
| 39ed438d-9f74-493a-9db4-a793bcdd43dc | 2019-08-16 06:04:09.055901+02 | 11 |
+--------------------------------------+-------------------------------+--------+
The date_created::DATE casts date_created to date and results in (i.e.) 2019-08-18, so we have groups by date
which are sorted, and now inside those groups we have date_created sorted ascending. Note that ASC part can be
omitted, but I’ve preserved it just to make a clear distinction of orders.