CODEWITHSUNDEEP
sqlpostgresqlselectsql-order-by
jesuisbonbon
jesuisbonbon

Reputation: 992

postgres sorting based on multiple date value's

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?

Upvotes: 2

Views: 514

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

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.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

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

Upvotes: 2

Related Questions