ascending or descending order according to boolean column

Question

This is the table structure in PostgreSQL 10.

Even though "date" is an int, it represents a yyyy mm dd date. I am trying to write a SELECT that orders by date and when it is BC=true it is desc, so dates will be in the right order -500 01 02 then -500 01 03 (yyyy mm dd)
and when is is BC=false it is asc, so dates will be in the right order again 1500 01 02, then 150 01 03 (yyyy mm dd)

I came up with this SELECT * FROM test ORDER BY bc=true desc, bc=false asc; that does great on BC dates, but it flips the AD dates (15000103 then 15000102, that is wrong).

I know there is the date type available, but I want this to work as a hack for exact BC dates.

How can I change my SELECT to properly order the dates according to the BC boolean column?

Thanks

Answer 1

I don't think bc=true desc, bc=false asc is working good. I'll check again when I have my computer and update my answer later.

Maybe my solutions is just a trick or a cheat. This is not a legal way. You can try this.

SELECT * FROM test 
ORDER BY bc DESC, CASE WHEN bc THEN date*(-1) ELSE date END ASC;

Or

SELECT * FROM test 
ORDER BY bc DESC, CASE WHEN bc THEN abs(date) ELSE date END ASC;

Hopefully my answer will satisfy you.