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

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?

CodePudding user response：

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;

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

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

Here's the dbfiddle

CodePudding user response：

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;

CodePudding user response：

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