From: "Greg Sabino Mullane" <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: Funny date-sorting task
In-Reply-To: <4646508B.5050404@gmx.net>
Date: Sun, 13 May 2007 13:47:20 -0000
Message-ID: <b480e4aa32acfdc5172399e72249e00a@biglumber.com>


-----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-----