0

Here is my sample table

id  from_time   to_time    user_login_id
1  1583721900 1583782500       2
2  1583721900 158378255        2
3  1583721900 1583782555       5
4  1583721900 1583782555       3

(date times are stored in UNIX time stamp )

I wanted SQL query to fetch sum of total hour between started and ended within given date

For ex: i wanted fetch total hour between 01-02-2020 to 31-02-2020 from table

 from-time             to_time         user_login_id
01-01-2020 10:00   01-01-2020 12:00       1
01-2-2020 10:00    02-01-2020 12:00       1
02-02-2020 10:00   02-02-2020 12:00       2
03-02-2020 10:00   03-02-2020 12:00       2

Output:

user_login_id total_hour
 2              4

It should give from time started after 01-01-2020 and close time ended before 31-01-2020

Thanks in advance

Bijesh Samani
  • 145
  • 1
  • 1
  • 10
  • Does this answer your question? [MySQL How to get timedifference in unix timestamp](https://stackoverflow.com/questions/46526008/mysql-how-to-get-timedifference-in-unix-timestamp) – Ne Ma Mar 11 '20 at 11:03
  • Thanks for reply, but that's not answer for my question – Bijesh Samani Mar 11 '20 at 11:27
  • Does this answer your question? [converting Epoch timestamp to sql server(human readable format)](https://stackoverflow.com/q/4787827/2029983). If not ***why***? – Thom A Mar 11 '20 at 11:50
  • No, it's not my question, i wanted to find total time between multiple date based on start date and end date – Bijesh Samani Mar 11 '20 at 11:53
  • How exactly would you treat rounding? – El_Vanja Mar 11 '20 at 13:29

1 Answers1

1

This may work as you expect

SELECT FROM_UNIXTIME(`from_time`,'%Y-%m-%d %h:%i:%s'),FROM_UNIXTIME(`to_time`,'%Y-%m-%d %h:%i:%s'),user_login_id,SUM(TIMESTAMPDIFF(HOUR, FROM_UNIXTIME(`from_time`,'%Y-%m-%d %h:%i:%s'),FROM_UNIXTIME(`to_time`,'%Y-%m-%d %h:%i:%s'))) as total_hour
FROM 
  your_table_here
WHERE 
(FROM_UNIXTIME(`to_time`,'%Y-%m-%d') BETWEEN  STR_TO_DATE("01-03-2020",'%d-%m-%Y') and STR_TO_DATE("31-03-2020",'%d-%m-%Y')) and 
(FROM_UNIXTIME(`from_time`,'%Y-%m-%d') BETWEEN  STR_TO_DATE("01-03-2020",'%d-%m-%Y') and STR_TO_DATE("31-03-2020",'%d-%m-%Y'))
GROUP BY `user_login_id`

//here 01-03-2020 is considered as start date and 31-03-2020 is end date that you have to make dynamic
//note :- it will produce result only in hours and ignore minute difference if any

Example fiddle

Sandeep Modak
  • 832
  • 1
  • 5
  • 10
  • Of course gets more complicated if there is a login that overlaps one of the boundaries. And the first two columns retrieved probably aren't helpful. – Garr Godfrey Mar 12 '20 at 06:57