Re: timestamp interval issue

Lonni J Friedman skrev:
> Greetings,
> I've got an interesting problem.  I have a table with a column full of
> timestamps.  I need a means of returning only the rows which have a
> timestamp that falls after the last 16:00 and before the next 16:00
> (on the clock), regardless of date.
>
> For example, let's say I've got this in my table:
>
>      date_created
> ----------------------
>  10-05-2007 00:44:45
>  10-04-2007 17:59:43
>  10-04-2007 19:12:00
>  10-04-2007 17:59:54
>  10-03-2007 21:00:56
>  10-04-2007 19:12:00
>  10-03-2007 21:00:58
>
> and let's say that the current timestamp (select now()) returns:
> 2007-10-05 15:18:54.133368-07
>
> I need to get back just the following rows:
>  10-05-2007 00:44:45
>  10-04-2007 17:59:43
>  10-04-2007 19:12:00
>  10-04-2007 17:59:54
>  10-04-2007 19:12:00

Something like this?

SELECT *
FROM footable0
WHERE (date_created - interval '16 hours')::date = (now() - interval '16
hours')::date;

Tested.

Nis