ADSM-L

Date math and the Events table

2002-01-17 17:42:54
Subject: Date math and the Events table
From: Ted Byrne <tbyrne AT DSSCORP DOT COM>
Date: Thu, 17 Jan 2002 17:40:01 -0500
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
<Prev in Thread] Current Thread [Next in Thread>
  • Date math and the Events table, Ted Byrne <=