MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Group by hourly time

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
sunz
Beginner


Joined: 22 Feb 2011
Posts: 6
Topics: 3
PostPosted: Thu Mar 03, 2011 9:52 pm    Post subject: Group by hourly time Reply with quote
Hi

I want to count the number of records between each hour using the timestamp column. Do we have any function that can be used for this in the Group by clause. Thank you.
Code:

TYPE         Timestamp
-------      ------------------------------------
RD           2009-10-01-01.29.49.937718
RD           2009-10-01-12.07.49.774781
RD           2009-10-01-01.56.59.436038
RD           2009-10-01-01.22.51.201398
RD           2009-10-01-01.11.57.089807
RD           2009-10-01-01.19.50.136999
RD           2009-10-02-01.38.49.814558
RD           2009-10-02-02.08.49.766543
RD           2009-10-02-12.30.50.346080
RD           2009-10-02-01.43.23.994274

Expected output
Code:

Date             Hour      No of Records
----------     ------    ----------------
2009-10-01        12           1
2009-10-01         1           5
2009-10-02        12           1
2009-10-02         1           2
2009-10-02         2           3
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Thu Mar 03, 2011 10:54 pm    Post subject: Reply with quote
sunz,

Untested SQL modified from this post by Nascar9

http://www.mvsforums.com/helpboards/viewtopic.php?t=9865&highlight=timestamp

Code:

SELECT DATE(TIMESTAMP_COL)   AS DATE           
      ,HOUR(TIMESTAMP_COL)   AS HOUR           
      ,COUNT(*)              AS NO_OF_RECORDS 
  FROM TABLE                     
 GROUP BY DATE(TIMESTAMP_COL)               
         ,HOUR(TIMESTAMP_COL)               
  ;


Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
sunz
Beginner


Joined: 22 Feb 2011
Posts: 6
Topics: 3
PostPosted: Fri Mar 04, 2011 11:34 am    Post subject: Reply with quote
Kolusu

The query worked. Thank you so much
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group