Data between 2 timestamps

• PNS

  Right there with Babe

  Points: 773

  More actions

  October 6, 2007 at 10:16 pm

  #178623

  Hi,

  I need to generate a report between 2 timestamps in specific intervals.

  For e.g. I have to show data for 1 hour in 5 min interval.

  StartTime : 2007-10-07 08:23:08.467 (starttime is passed as one of the params with the duration (here 1 hour)

  EndTime: 2007-10-07 09:23:08.467

  I need to show reults like this

  0 2007-10-07 08:23:08.467

  0 2007-10-07 08:24:08.467

  0 2007-10-07 08:25:08.467

  0 2007-10-07 08:26:08.467

  0 2007-10-07 08:27:08.467

  5 2007-10-07 08:28:08.467

  5 2007-10-07 08:29:08.467

  5 2007-10-07 08:30:08.467

  5 2007-10-07 08:31:08.467

  5 2007-10-07 08:32:08.467

  .

  .

  .

  .

  59

  59

  I am able to achieve this with the help of a temp table(tmpTable) where i am storing these intervals with starttime and endtime as 2 columns (each with 5 min interval).

  tmpStarttime tmpEndtime

  0 2007-10-07 08:23:08.467 2007-10-07 08:27:08.467

  5 2007-10-07 08:27:08.467 2007-10-07 08:31:08.467

  .

  .

  .

  I am passing these 2 timestamps in the query where i am actually fecthing the data.

  select tmptable.tmpinterval,table.starttime from

  table join tmpTable

  on startime between tmpstarttime and tmpendtime

  I would like to know is theren any way where i can get this resultset with the help of a query itself rahter thn having a temp table.some set based approach.

  Thanks in advance.

  PS

• Loner

  SSC-Insane

  Points: 21279

  More actions

  October 7, 2007 at 1:30 pm

  #739855

  I assume the lowest interval you want to get is minute.

  SELECT DATEDIFF(mi, starttime, endtime) FROM Table

  DATEDIFF ( datepart , startdate , enddate )

  Returns the number of date and time boundaries crossed between two specified dates.

  second = ss

  minute = mi

  Hour = hh

  Both startdate and enddate had to be DATETIME or SMALLDATETIME format.

• Tomm Carr

  SSCertifiable

  Points: 5426

  More actions

  October 8, 2007 at 12:12 pm

  #740117

  If you have a Number or Tally table, you can generate a list of time values very easily. My Tally table column is Num and the values start at 0. The following will generate a list of 5-minute datetime values starting at starttime and ending at endtime.

  [Code]declare @StartTime datetime,

  @EndTime datetime;

  select @StartTime = '2007-10-07 08:23:08.467',

  @EndTime = '2007-10-07 09:23:08.467';

  select DateAdd( mi, Num * 5, @StartTime ) as IntervalStart,

  DateAdd( mi, (Num+1) * 5, @StartTime ) as IntervalEnd

  from Utility..Tally

  where DateAdd( mi, Num * 5, @StartTime ) < @EndTime;

  [/Code]

  You can then join this output with your table of interest and pull out what you want, all in one statement.

  [Code]select t.IntervalStart, t.IntervalEnd, d.whatever

  from datatable d

  join (

  select DateAdd( mi, Num * 5, @StartTime ) as IntervalStart,

  DateAdd( mi, (Num+1) * 5, @StartTime ) as IntervalEnd

  from Utility..Tally

  where DateAdd( mi, Num * 5, @StartTime ) < @EndTime

  ) t

  on d.TimeCol between t.IntervalStart and t.IntervalEnd;[/Code]

  If you don't have a Tally table, it is just a table with one integer column with sequential values from 0 (or 1) to some arbitrary maximum (mine is 32767. Why 32767? No particular reason. I just like round numbers.:P)).

  Tomm Carr
  --
  Version Normal Form -- http://groups.google.com/group/vrdbms