Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    1

    Cool Unanswered: Group by range of hours

    Hello All,
    In my database i have a table with two timestamp fields like startDate and endDate
    Now i want to pass startDate and endDate in my query and according to the difference between those dates i want to group the data by hours.

    example 1) If differece is 24 hours then i will get 00-23

    example 2) If the difference is 48 hours then i want to group 2 hours of the same day so i can finally get only 24 rows
    |
    |
    example 7) If difference is 168 hours ie 7 days then group by 7 hours



    condition is group hours of the same day

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    One method might be to convert the two times into seconds (unix_timestamp) Then subtract one from the other to give number of seconds between the two datetimes. Then divide by 3600 to give the number of hours. Put this through a case statement to decide what type of output ie group by 2 hours etc. Unsure if you need to group the data by this field as well. It might of been clearer if you provided some sample data and output. What is the project and why does it need the output this way?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    [offtopic] it's not might of been, mike, it's might of beed [/offtopic]

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Seeing as it's a small town in India then surely it should be might of Beed.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •