CODEWITHSUNDEEP
mysqlsql
Arrovil
Arrovil

Reputation: 945

Get time duration per each available day

I need to get users visits duration for each day in MySQL. I have table like:

user_id,date,time_start, time_end
1, 2018-09-01, 09:00:00, 12:30:00
2, 2018-09-01, 13:00:00, 15:10:00
1, 2018-09-03, 09:30:00, 12:30:00
2, 2018-09-03, 13:00:00, 15:10:00

and need to get:

user_id,2018-09-01_duration,2018-09-03_duration
1,03:30:00,03:00:00
2,02:10:00,02:10:00

So columns need to be dynamic as some dates can be missed (2018-09-02). Is it possible to do with one query without explicit joins per each day (as some days can be null)?

Update #1

Yes, I can generate columns in application side, But I still have terrible query like

SELECT user_id, d1.dt AS "2018-08-01_duration", d2.dt AS "2018-08-03_duration"...
FROM (SELECT 
            user_id,
            time_format(TIMEDIFF(TIMEDIFF(time_out,time_in),time_norm),"%H:%i") AS dt 
        FROM visits 
        WHERE date = "2018-09-01") d1
        LEFT JOIN(
        SELECT 
            user_id,
            time_format(TIMEDIFF(TIMEDIFF(time_out,time_in),time_norm),"%H:%i") AS dt 
        FROM visits 
        WHERE date = "2018-09-03") d3 
        ON users.id = d3.user_id...

Update #2

Yes, data like

select user_id, date, SEC_TO_TIME(SUM(TIME_TO_SEC(time_out) - TIME_TO_SEC(time_in))) as total
from visits
group by user_id, date;

is correct, but in this case data for users goes consistently. And I hope there's the way when I have rows with users and columns with dates (like in example above)

Upvotes: 0

Views: 103

Answers (4)

mohabbati
mohabbati

Reputation: 1158

By the query you can solve your problem. the query is dynamic and you can improve it. i use TSQL for the query, you can use the idea in MySQL.

declare
    @columns    as nvarchar(max),
    @query      as nvarchar(max)

select
    @columns    =
    stuff
    ((
            select
            distinct
                ',' + quotename([date]) 
            from
                table_test
            for xml path(''), type
    ).value('.', 'nvarchar(max)'), 1, 1, '')

--select    @columns

set @query =
'with
    cte_result
as
(
    select
        [user_id]   ,
        [date]      ,
        time_start  ,
        time_end    ,
        datediff(minute, time_start, time_end)  as duration
    from
        table_test
)
select
    [user_id], ' + @columns + '
from 
(
    select
        [user_id]   ,
        [date]      ,
        duration
    from
        cte_result
)
    sourceTable
pivot 
(
    sum(duration)
    for [date] in (' + @columns + ')
)
    pivotTable'


execute(@query)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

If you know the dates that you want in the result set, you don't need a dynamic query. You can just use conditional aggregation:

select user_id,
    SEC_TO_TIME(SUM(CASE WHEN date = '2018-09-01' THEN TIME_TO_SEC(time_out) - TIME_TO_SEC(time_in))) as total_20180901,
    SEC_TO_TIME(SUM(CASE WHEN date = '2018-09-02' THEN TIME_TO_SEC(time_out) - TIME_TO_SEC(time_in))) as total_20180902,
    SEC_TO_TIME(SUM(CASE WHEN date = '2018-09-03' THEN TIME_TO_SEC(time_out) - TIME_TO_SEC(time_in))) as total_20180903
from visits
group by user_id;

You only need dynamic SQL if you don't know the dates you want in the result set. In that case, I would suggest following the same structure with the dates that you do want.

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

There's no dynamic way to use pivotting in MySQL but you might use the following for your case :

create table t(user_id int, time_start timestamp, time_end timestamp);
insert into t values(1,'2018-09-01 09:00:00', '2018-09-01 12:30:00');
insert into t values(2,'2018-09-01 13:00:00', '2018-09-01 15:10:00');
insert into t values(1,'2018-09-03 09:30:00', '2018-09-03 12:30:00');
insert into t values(2,'2018-09-03 13:00:00', '2018-09-03 15:10:00');

select min(q.user_id) as user_id, 
       min(CASE WHEN (q.date='2018-09-01') THEN q.time_diff END) as '2018-09-01_duration',
       min(CASE WHEN (q.date='2018-09-03') THEN q.time_diff END) as '2018-09-03_duration'
  from
  (
   select user_id, date(time_start) date,
          concat(concat(lpad(hour(timediff(time_start, time_end)),2,'0'),':'),
          concat(lpad(minute(timediff(time_start, time_end)),2,'0'),':'),
          lpad(second(timediff(time_start, time_end)),2,'0')) as time_diff      
     from t
  ) q
  group by user_id;

Upvotes: 1

NiVeR
NiVeR

Reputation: 9796

Try something like this:

select user_id, date, sum(time_end - time_start) 
from table
group by user_id, date;

You will need to do some tweaking, as you didn't mention the RDBMS provider, but it should give you a clear idea on how to do it.

Upvotes: 1

Related Questions