On 10/5/07, Valentin Gjorgjioski <tinodj@mt.net.mk> wrote:
> 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))
Thanks. Unfortunately, that doesn't seem to work. I get a syntax
error at the first colon:
ERROR: syntax error at or near ":" at character 55
LINE 1: ...ct id,last_update from footable0 where ((now>today:16:00 && ...
^