Problem with Where clause

From: Jeff McClure <jmcab1_at_ameritech.net>
Date: Fri, 02 Oct 1998 18:06:01 -0500
Message-ID: <36155C59.A1405D36@ameritech.net>

Folks,

I have a problem in a query. Who doesn't? This one is driving me nuts. The problem appears to be in the where clause.I am attempting to obtain records whose timestamp is within a time minute window spanning the current time to the current time - 10 minutes (a ten minute moving window). This SQL will run from cron every ten minutes and be used to monitor an event. In my where clause, I specified 1)to_char(timestamp,'MM/DD/YYYY HH24:MI:SS') <= to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') and to_char(timestamp,'MM/DD/YYYY HH24:MI:SS') >= to_char(sysdate -
.006944444,'MM/DD/YYYY HH24:MI:SS')

.006944444 is the fractional (day) equivelant of 10 minutes...

2)to_char(timestamp,'MM/DD/YYYY HH24:MI:SS') between to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') and to_char(timestamp,'MM/DD/YYYY HH24:MI:SS') >= to_char(sysdate -
.006944444,'MM/DD/YYYY HH24:MI:SS')
Both seem to return records within the ten minute timeframe corresponding to when the statement was run. However, both also return records whose date (day) is different.
For example, if I were to run this statement now (10/02/1998 17:30:00) I would recieve records back with the timestamp of 09/25/1998 17:26:07, etc.

Am I missing something here? What am I doing wrong (except attempting to write SQL? I'm obviously not a programmer type by trade!!!, but I can write some pretty slick SQL) Is there a better way of doing this?

Heres my SQL:

clear buffer
clear columns
set echo off
set feedback off
set verify off
set heading off
set linesize 132
set pagesize 60

column col_start FORMAT A17 
column col_chnum FORMAT 099
column col_logname FORMAT A50 
column col_mins FORMAT 99999 
column phy_addr FORMAT A20 
column stat1	FORMAT A13
column stats	FORMAT A13 

spool NVOD_Sch_cron.out

select

             h.channel_number col_chnum,

substr(physical_network_address,5,instr(physical_network_address,'_',1,1) - 5) ||':'||
substr(physical_network_address,instr(physical_network_address,':',1,2) + 1, 5) phy_addr,

	a.status stat1,
	b.status stats,
	to_char(b.start_date, 'MM/DD/YY HH24:MI:SS') col_start,
	(sum(g.milliseconds)/1000)/60 col_mins,
     	substr(d.name,1,instr(d.name,'(',1,1) - 1) col_logname

from nvod a, bi_schedules b, lcontent_track c, logical_content d, track_clip e, clips f, content g, bi_channels h where
to_char(b.start_date, 'MM/DD/YY HH24:MI:SS') between to_char(sysdate -
.006944444,'MM/DD/YY HH24:MI:SS') and to_char(sysdate,'MM/DD/YY
HH24:MI:SS')

and (b.id = a.schedule_id)
and (d.id = a.logical_content_id)
and (c.logical_content_id = a.logical_content_id)
and (e.track_id = c.track_id)
and (f.id = e.clip_id)
and (g.id = f.content_id)
and (h.id = a.channel_id)

group by
h.channel_number,h.physical_network_address,a.status,b.status,b.start_date,b.stop_date,d.name order by
h.channel_number,h.physical_network_address,a.status,b.status,b.start_date,b.stop_date,d.name ;

spool off

Any input would be greatly appreciated as I am on an extremely tight timeline, and this SQL could be one of a very few factors that affect a go/no-go decision on this project.

Thanks in advance,

Jeff McClure
Specialist Database Analyst
Ameritech New Media
e-mail: Work: Jeff.W.McClure_at_Ameritech.com

           Home:  jmcclure_at_mcs.com
                  jmcab1_at_Ameritech.net

Received on Fri Oct 02 1998 - 18:06:01 CDT