Re: timestamp interval issue

On 06.10.2007 00:47 Lonni J Friedman wrote:
> 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
>
> The closest I've come is the following, which unfortunately only works
> if its not yet after 16:00 on the current date:
>
> select count(id) from footable0 where ('today'::timestamp - interval
> '8 hours') < date_created::timestamp AND ('tomorrow'::timestamp +
> interval '8 hours') > date_created::timestamp ;

where ((now>today:16:00 && date_created>today:16:00) || (now<today:16:00
&& date_created>yesterday:16:00))

--
Valentin Gjorgjioski
--
Human knowledge belongs to the world
--