CODEWITHSUNDEEP
mysql
Iqbal Hossen
Iqbal Hossen

Reputation: 113

How to calculate total time between datetime stamps in MySQL

Lets say I have this record:

**Task Details Table: task_details**

id         task_id    user_id      task_type         date_time
1            2          7            1               2015-08-23 10:05:06
2            2          7            2               2015-08-23 10:05:33
3            2          7            1               2015-08-23 10:05:53
4            2          7            2               2015-08-24 10:10:53
5            2          7            3               2015-08-24 10:11:50
6            3          9            1               2015-08-23 10:08:06
7            3          9            2               2015-08-23 10:15:33

N.B: task_type mean is 1 = running, 2 = Pause, 3 = stop

I want to get total pause time in MySql. how can select the date_timein total pause time convert in (hours:minutes).

SELECT 
  SEC_TO_TIME( SUM( TIME_TO_SEC( `date_time` ) ) ) AS psuse_time  
FROM task_details where task_type = 2 GROUP BY task_id, user_id

Can any body offer some explanation as to what might be going on here.

Upvotes: 1

Views: 276

Answers (1)

Christian
Christian

Reputation: 3988

You can use the Unixtime functions in MySQL to do your calculations.

mysql> SELECT UNIX_TIMESTAMP('2015-08-24 10:10:53') - UNIX_TIMESTAMP('2015-08-23 10:05:53');
+-------------------------------------------------------------------------------+
| UNIX_TIMESTAMP('2015-08-24 10:10:53') - UNIX_TIMESTAMP('2015-08-23 10:05:53') |
+-------------------------------------------------------------------------------+
|                                                                         86700 |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You could use MIN() and MAX() maybe to get the earliest and latest dates?

mysql> SELECT UNIX_TIMESTAMP(MAX(datestamp)) - UNIX_TIMESTAMP(MIN(datestamp)) FROM table;
+-------------------------------------------------------------------------------+
| UNIX_TIMESTAMP(MAX(datestamp)) - UNIX_TIMESTAMP(MIN(datestamp)) |
+-------------------------------------------------------------------------------+
|                                                                         86700 |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Are you trying to calculate the difference between all of them or only those who are next to each other in the list?

I'll update this answer with more info if requ'd.

EDIT

Updated with conditional.

SELECT UNIX_TIMESTAMP(MAX(datestamp)) - UNIX_TIMESTAMP(MIN(datestamp)) FROM task_details WHERE task_type = 2 GROUP BY task_id ORDER BY datestamp;

Try this out. The dummy data above didn't have many rows. I tested briefly. Please report back.

EDIT #2

I just added some additional rows to the test table I have and the query worked well.

Note that 'datetime' is a reserved word in MySQL and you should consider renaming that column.

Upvotes: 1

Related Questions