-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> Every task has a timestamp X that may be NULL or a date.
> ...
> Sometimes it has date and the time-part, too.
>
> The list should be like this:
> 1) X sometime today should come first in ascending time order.
> 2) X in the past should show up after (1) in descending order
> so that not so long back dates come first
> 3) X = NULL
> 4) X sometime in the future
Assuming you mean the literal sense of "today", and that future
dates show with the least furthest away first:
SELECT * FROM yourtable
ORDER BY
CASE WHEN X::date = now()::date THEN 1 WHEN X::date < now()::date THEN 2 WHEN X IS NULL THEN 3 ELSE
4
END,
CASE WHEN X::date-now()::date < 0 THEN now()-X ELSE X-now()
END;
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200705130942
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFGRxZIvJuQZxSWSsgRAwrsAJ9HzZXzf3sQs0FVNSrhxN5UpGhc+wCcDygQ
obe5G3b58+pXhqy4Ybh/OM8=
=rJpn
-----END PGP SIGNATURE-----