Excluding time period from results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Excluding time period from results

Author

Topic

ajw
Starting Member

10 Posts

Posted - 2006-07-12 : 05:01:41

Hello, I would like to exclude the time period after 5.30pm and before 8.30am in my results. The time is in a 13 digit timestamp format which is the same as a standard unix timestamp with 3 digits which are microseconds.

I used:

dataadd(ss, TTIME/1000, '1970-01-01')AS time

to create a column with a readable time in it.

Here is a screenshot: http://www.abtecnet.com/timescreenshot.jpg

Can anyone help me with this. Thanks very much.
Andrew

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-12 : 05:20:14

where (datepart(hour,[YOURDATE]) = 5 and datepart(minutes>30)) OR
(datepart(hour,[YOURDATE]) = 8 and datepart(minutes<30)) OR
(datepart(hour,[YOURDATE]) between 6 and 8)

The between part I am not sure of. Here you can try some different values...

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-12 : 05:52:56

Here's another idea...

--data (create a line for every half hour from now to a few days' time) - you can ignore this
declare @t table (TTIME bigint)
insert @t
select Now + number * Hour * 0.5
from
    (select cast(datediff(second, '19700101', getdate()) as bigint) * 1000 as Now, (1000 * 60 * 60) as Hour) a,
    (select distinct top 100 number from master.dbo.spt_values where number > 0 order by number) b

--calculation - the bit you need
select time
from (
    select dateadd(ss, TTIME/1000, '1970-01-01') AS time from @t
) a
where
    time - datediff(day, 0, time) between '08:30' and '17:30'

/*results
time                                                   
------------------------------------------------------ 
2006-07-12 11:18:36.000
2006-07-12 11:48:36.000
..
2006-07-12 16:48:36.000
2006-07-12 17:18:36.000
2006-07-13 08:48:36.000
2006-07-13 09:18:36.000
..
2006-07-13 16:48:36.000
2006-07-13 17:18:36.000
2006-07-14 08:48:36.000
2006-07-14 09:18:36.000
..
2006-07-14 12:18:36.000
2006-07-14 12:48:36.000
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

ajw
Starting Member

10 Posts

Posted - 2006-07-12 : 06:41:07

Thank you very much for this and please excuse my ignorance. RYANRANDALL Could you possibly break down your explanation. Can you explain how I can integrate it with my original query of

SELECT POLLID, INSTANCE, VAL, DATEADD(ss, TTIME /1000, '1970-01-01')AS time
FROM STATSDATA_HOURLY
WHERE (POLLID=100)
ORDER BY time ASC

I am quite new to SQL. Thanks very much for the help.

Andrew

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-12 : 06:57:05

Try one of these (meaning try them both and pick the one with the best performance / easiest for you to understand)...

select POLLID, INSTANCE, VAL, time from (
    SELECT *, DATEADD(ss, TTIME /1000, '1970-01-01') AS time FROM STATSDATA_HOURLY WHERE (POLLID=100)) a
where time - datediff(day, 0, time) between '08:30' and '17:30'
ORDER BY time ASC


SELECT POLLID, INSTANCE, VAL, DATEADD(ss, TTIME /1000, '1970-01-01') AS time
FROM STATSDATA_HOURLY
WHERE (POLLID=100)
  and DATEADD(ss, TTIME /1000, '1970-01-01') - datediff(day, 0, DATEADD(ss, TTIME /1000, '1970-01-01'))
      between '08:30' and '17:30'
ORDER BY time ASC

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

ajw
Starting Member

10 Posts

Posted - 2006-07-12 : 09:18:34

that works a treat thanks. I had been trying a similar thing but I was missing some brackets. Thats fantastic!

Thanks
Andrew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources