postgres sorting based on multiple date value's

Question

I have one project table with projects with the columns start_at (date) end_at(date) pauzed_at (date) etc. And I want to order the projects, based on date values.

Sorting should be;

- STARTED projects
- PAUZED projects
- IDLE projects 
- ENDED projects

project is started when the date is between start_at & end_at.

project is idle when start_at date is not reached.

project is ended when end_at date is reached.

project is paused when paused_at is set etc etc.

How can I sort this table, based on the state of the project?

Mureinik · Accepted Answer · 2017-09-14 08:07:16Z

2

The states can be represented by a case expression:

SELECT *
FROM   projects
ORDER BY CASE WHEN paused_at IS NOT NULL THEN 2 -- paused
              WHEN ended_at() < NOW() THEN 4 -- ended
              WHEN started_at() > NOW() THEN 3 -- idle
              ELSE 1 -- started
         END ASC

answered Sep 14, 2017 at 8:07

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Clodoaldo Neto · Accepted Answer · 2017-09-14 08:19:48Z

1

false orders before true:

order by
    now() >= start_at and now() < end_at desc,
    paused_at is null,
    now() < start_at desc,
    now() >= end_at desc

A case expression is an optimization barrier to the planner.

answered Sep 14, 2017 at 8:19

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

postgres sorting based on multiple date value's

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related