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
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?