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?
>Solution :
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;