'Order By Date desc evaluates 01-01-1970 higher then everything else

I have the following simplified query

SELECT 
   id
   to_char(execution_date, 'YYYY-MM-DD') as execution_date
FROM schema.values
ORDER BY execution_date DESC, id DESC

execution_date can be null.

If no value is present in execution_date it will be set to 1970-01-01 as default. My problem is, that the following table values will lead to a result where 1970-01-01 is treated as the newest date.

Table:

id execution_date
1
2 2020-01-01
3 2022-01-02
4

Result I would expect

id execution_date
3 2022-01-02
2 2020-01-01
4 1970-01-01
1 1970-01-01

What I get

id execution_date
4 1970-01-01
1 1970-01-01
3 2022-01-02
2 2020-01-01

How can I get the correct order and is it possible to easily return an empty varchar if the date is empty?

postgresqldatesql-order-by


Solution 1:[1]

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=5d1fc31a3cf2d3121092f2446cce87e5

SELECT 
   id,
   to_char(coalesce( execution_date, '1970-01-01'::date), 'YYYY-MM-DD') as execution_date
FROM values1
ORDER BY execution_date DESC, id DESC;

Solution 2:[2]

If you table has NULL values, not empty values, you can try to use nulls last :

with t as (select 1 as id, NULL::date as dt
union select
2, '2020-01-01'::date
union select
3, '2020-01-02'::date
union select
4, NULL::date)
select * 
from t
order by t.dt desc nulls last, id desc;

enter image description here

It should work for an empty text values also:

with t as (select 1 as id, ''::text as dt
union select
2, '2020-01-01'::text
union select
3, '2020-01-02'::text
union select
4, NULL::text)
select * 
from t
order by t.dt desc nulls last, id desc

enter image description here

And if you need to change your NULL date to 1970 just use COALESCE() :

with t as (select 1 as id, NULL::date as dt
union select
2, '2020-01-01'::date
union select
3, '2020-01-02'::date
union select
4, NULL::date)
select coalesce(t.dt, '1970-01-01'::date) as dt
from t
order by t.dt desc nulls last, id desc

enter image description here

Here's the dbfiddle

Solution 3:[3]

I did not to see the forest for the trees...

Here is the simple solution:

SELECT 
   id
   CASE WHEN execution_date IS NULL THEN ''
            ELSE to_char(execution_date, 'YYYY-MM-DD') END 
            AS execution_date
FROM schema.values
ORDER BY execution_date DESC, id DESC

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mark
Solution 2
Solution 3 codlix

Related Questions