CODEWITHSUNDEEP
sqldatabasecounttimestampdb2
Hunain Vhora
Hunain Vhora

Reputation: 11

db2 sql query to display the count for the every hour between given timestamp

I am trying to query the table. The query should display the count of unique id for every hour between the given timestamp. The timestamp is in yyyy-mm-dd hh-mm-ss format. Kindly, help. There is no join operation needed as we only target one table.

The columns in the table which will be our main focus would be

OBJ_ID (unique id for the count purpose) Date_create (timestamp for the range that we would specify to have the counts for all the hours) Subtype (for other condition check)

I have tried various queries from various forum but none work as we would expect to

We want to display the timestamp for every hour and the counts of unique id in the result set

Any help would be greatly appreciated

Thanks!

Upvotes: 0

Views: 3625

Answers (2)

MichaelTiefenbacher
MichaelTiefenbacher

Reputation: 4005

Try something like this

SELECT date(date_create), HOUR(date_create), count(DISTINCT obj_id)
  FROM temp
 GROUP BY date(date_create), HOUR(date_create)

Upvotes: 1

user11866010
user11866010

Reputation: 46

select count(obj_id) as id_count, hour(date_create) as hour
from cnt
where date_create between '2019-10-22-12.00.00' and '2019-10-22-18.00.00'
group by year(date_create),month(date_create), day(date_create), hour(date_create)"

Upvotes: 1

Related Questions