First off, I should say that I have read (and think I understand) Andy
Raibeck's explanation from 11/23/2001 of the restrictions on queries
against the Events table. However, in trying to craft a query that returns
records from a relative timeframe, I am running into some problems getting
the result that I would like to get.
What I am trying to do is run a query that yields the same results as
"q ev * * begind=-1 endd=today begint=08:00 endt=07:59"
with an eye toward doing some calculations on the number of events with
different status conditions
This is what I came up with:
select -
schedule_name, -
time(scheduled_start) as "Scheduled", -
time(actual_start) as "Actual", -
status as "Status", -
node_name as "Client" -
from events -
where node_name is not null -
and date(scheduled_start+16 hour-1 minute)=date(current_timestamp)
This returns events only from the current day
However, if I hard-code the dates as follows:
select -
schedule_name, -
date(scheduled_start),-
time(scheduled_start) as "Scheduled", -
time(actual_start) as "Actual", -
status as "Status", -
node_name as "Client" -
from events -
where node_name is not null -
and scheduled_start between -
'2002-01-16 08:01:00' and -
'2002-01-17 08:00:59'
I get a lengthy listing of all events, as I do from the query event command.
I suspect that there is some voodoo happening with the relative date
calculation not occurring before the events are restricted to the current
date, but I have not been able to come up with a query that works as I
would like it to.
Any suggestions or pointers on performing date math in SQL queries would be
greatly appreciated
Thanks,
Ted
