Skip to content
Advertisement

How to get a count of records by minute using a datetime column

I have a table with columns below:

Customer Time_Start
A 01/20/2020 01:25:00
A 01/22/2020 14:15:00
A 01/20/2020 03:23:00
A 01/21/2020 20:37:00

I am trying to get a table that outputs a table by minute (including zeros) for a given day.

i.e.

Customer Time_Start Count
A 01/20/2020 00:01:00 5
A 01/20/2020 00:02:00 2
A 01/20/2020 00:03:00 0
A 01/20/2020 00:04:00 12

I would like to have it show only 1 day for 1 customer at a time.

Here is what I have so far:

select 
customer,
cast(time_start as time) + ((cast(time_start as time) - cast('00:00:00' as time)) hour(2)) as TimeStampHour,
count(*) as count
from    Table_1
where   customer in ('A')
group by    customer, TimeStampHour
order by    TimeStampHour

Advertisement

Answer

In Teradata 16.20 this would be a simple task using the new time series aggregation

SELECT 
   customer
  ,Cast(Begin($Td_TimeCode_Range) AS VARCHAR(16))
  ,Count(*)
FROM table_1
WHERE customer = 'A'
  AND time_start BETWEEN TIMESTAMP'2020-01-20 00:00:00' 
                     AND Prior(TIMESTAMP'2020-01-20 00:00:00' + INTERVAL '1' DAY)
GROUP BY TIME(Minutes(1) AND customer) 
         USING timecode(time_start)
         FILL (0)
;

Before you must implement it similar to Ramin Faracov answer, create a list of all minutes first and then left join to it. But I prefer doing the count before joining:

WITH all_minutes AS
 ( -- create a list of all minutes
   SELECT 
      Begin(pd) AS bucket
   FROM 
    ( -- EXPAND ON requires FROM and TRUNC materializes the FROM avoiding error
      -- "9303. EXPAND ON clause must not be specified in a query expression with no table references."
      SELECT Cast(Trunc(DATE '2020-01-20') AS TIMESTAMP(0)) AS start_date
    ) AS dt
   EXPAND ON PERIOD(start_date, start_date + INTERVAL '1' DAY) AS pd
          BY INTERVAL '1' MINUTE  
 )
SELECT customer
  ,bucket
  ,Coalesce(Cnt, 0)
FROM all_minutes
LEFT JOIN
 (
   SELECT customer
     ,time_start
      - (Extract (SECOND From time_start) * INTERVAL '1' SECOND) AS time_minute
      ,Count(*) AS Cnt
   FROM table_1
   WHERE customer = 'A'
     AND time_start BETWEEN TIMESTAMP'2020-01-20 00:00:00' 
                        AND Prior(TIMESTAMP'2020-01-20 00:00:00' + INTERVAL '1' DAY)
   GROUP BY customer, time_minute
 ) AS counts
ON counts.time_minute = bucket 
ORDER BY bucket
;
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement