Count rows for every x minute interval

Database

Count rows for every x minute interval

I am new to teradata and to this forum. We use a tool called Business Objects (MyInsight) to query a teradata reporting database. I am trying to run a query that will count rows per every 30 minute interval.  Here is the very basic query:

SELECT

  trim(cast(BI_DMV.IVR_CALL_LOG.CALL_TM_MAN as varchar(20))),

  count(*)

FROM

  BI_DMV.IVR_CALL_LOG

WHERE

  BI_DMV.IVR_CALL_LOG.CALL_DT  IN  ( {d '2016-02-13'}  )

GROUP BY

  1

It's output look like this:




Detail Call Time Call Count
00:00:00 2
00:00:01 1
00:00:02 3
00:00:03 4
00:00:04 1
00:00:06 1
00:00:07 1
00:00:08 1
00:00:09 1
00:00:10 1
00:00:11 1
00:00:12 1
00:00:13 2
00:00:14 5
00:00:15 2

How can I modify my query so that the call count is for every 30 minutes? So in my pretend output below, the 00:00 row, the call ount is really a count from 12am to 12:30, the 00:30 row is really a countr from 12:30am to 1am and so on and so forth?

Detail Call Time Call Count
00:00  30
00:30 40
01:00 50
01:30 400

Please help! A little desparate here, I am not finding much when I google search; none that I can understand anyway.

Thanks in advance!!

1 REPLY
Junior Contributor

Re: Count rows for every x minute interval

You can use following calculation to truncate a timestamp to half hour intervals: 

TS - ((EXTRACT(MINUTE FROM TS) MOD 30) * INTERVAL '1' MINUTE +
EXTRACT(SECOND FROM TS) * INTERVAL '1' SECOND)