Re: SQL Time Issue

--- Michael Glaesemann <grzm(at)myrealbox(dot)com> wrote:

>
> On Sep 1, 2005, at 4:33 AM,
> <operationsengineer1(at)yahoo(dot)com> wrote:
>
> > WHERE t_inspect.inspect_timestamp::date >
> > (current_date::timestamp - 720 'sec'::interval)
>
> What error did you get? Also, it's always helpful to
> provide a small,
> self-contained test case so others may try exactly
> what you have done.
>
> Looking at it quickly, I'd say you want '720
> sec'::interval or 720 *
> '1 sec'::interval. On v8.0.3:
>
> test=# select 720 'sec'::interval;
> ERROR: syntax error at or near "'sec'" at character
> 12
> LINE 1: select 720 'sec'::interval;
> ^
> test=# select '720 sec'::interval;
> interval
> ----------
> 00:12:00
> (1 row)
>
> > inspect_timestamp is datatype timestamp. i
> probably
> > shouldn't cast it to date, now that i think about
> > it... but i bet that won't make the query run
> right
> > all by itself.
>
> Don't bet :) Try it and see!
>
> > i want to pull all the events that occurred from
> > (today - 2 hours) forward (all events during
> todays
> > current date and the last 2 hours from yesterday).
>
> (As an aside, you can see that 720 seconds is not
> two hours. I think
> you mean 7200 seconds.)
>
> Does this help?
>
> Michael Glaesemann
> grzm myrealbox com

this did the trick!

t_inspect.inspect_timestamp > (current_date - 1 * '2
hr'::interval)

the only thing to remember is that current_date starts
at the very beginning of the current_date (it is day,
not time).

current_date - 12 hrs = noon yesterday, regardless of
the time it is today (it took me a minute to figure
that out even though it is pretty obvious when one
thinks it over).

thanks for the guidance - you helped me get through
one problem... now i have only infinity - 1 problems
to work through... -lol-

____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs