Convert SQL Query to MySQL with Split Time Interval

up vote
0
down vote

favorite

I wanted to request help in achieving the following scenario, we have table that logs user activity and with in the single row; we log start and stop time and calculate the duration using a mysql trigger when end time is updated in the row.

Following is the table structure

---------------------------------------------------

USERs Activity Table

---------------------------------------------------



created_at              Event       User            Start_timestamp         End_Timestamp           Duration

-------------------     ---------   -------         -------------------     -------------------     --------

2018-11-20 18:01:22     LOGIN       john.doe        2018-11-20 18:01:22     2018-11-20 19:01:22     3600

2018-11-20 18:01:22     LOGIN       jane.miller     2018-11-20 18:10:03     2018-11-20 20:10:03     7200

2018-11-20 18:15:16     BREAK       john.doe        2018-11-20 18:15:16     2018-11-20 18:33:47     300

2018-11-20 18:33:47     ONCALL      john.doe        2018-11-20 18:33:47     2018-11-20 18:36:47     180

We want to generate a report which can give us 15 minutes interval of all users as shown below

---------------------------------------------------

Desired Result in 15 minutes time interval 

---------------------------------------------------



Intervals               User            LOGIN TIME      BREAK TIME      CALL TIME

-------------------     ----------      ----------      ----------      ---------

2018-11-20 18:00:00     john.doe        00:15:00        00:00:00        00:00:00

2018-11-20 18:00:00     jane.miller     00:04:57        00:00:00        00:00:00

2018-11-20 18:15:00     john.doe        00:15:00        00:00:00        00:00:00

2018-11-20 18:15:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 18:30:00     john.doe        00:15:00        00:15:00        00:00:00

2018-11-20 18:30:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 18:45:00     john.doe        00:15:00        00:03:15        00:03:00

2018-11-20 18:45:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:00:00     john.doe        00:01:22        00:00:00        00:00:00

2018-11-20 19:00:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:15:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:30:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:45:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 20:00:00     jane.miller     00:10:03        00:00:00        00:00:00

-------------------                     ----------      ----------      ----------

What I am unable to achieve is that we have a single row for each event and if I get report based on 15 minutes interval, I get the total duration against the interval the event started in and it doesn't look right to show 30 mins of time in a 15 min interval. It should however be shown e.g. 3 mins in 1 interval and 12 mins in other; the actual time spent in each interval.

Please advise on how this can be achieved. I also created a temp table which is populated with 15 min intervals that I want the data to be generated against and use joins but I am not an expert in SQL and need help / advise from you guys.

Following is the query I have been playing around with but no success so far.

    SELECT 

      DATE_FORMAT(SEC_TO_TIME(TIME_TO_SEC(a.created_at) - TIME_TO_SEC(a.created_at) % (15 * 60)),"%Y-%m-%d %H:%i") AS time_interval

      , a.user

      , SEC_TO_TIME(IFNULL(SUM(CASE WHEN a.event = 'LOGIN' THEN a.duration END),0)) AS login_time

      , SEC_TO_TIME(IFNULL(SUM(CASE WHEN a.event = 'BREAK' THEN a.duration END),0)) AS break_time

      , SEC_TO_TIME(IFNULL(SUM(CASE WHEN a.event = 'ONCALL' THEN a.duration END),0)) AS talk_time

    FROM users_activity AS a

    WHERE a.created_at > '2018-11-20 00:00:00' 

    GROUP BY time_interval, a.user

    ORDER BY time_interval DESC;

SQL Fiddle with Schema https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0d64482c499a07e9d1c7ec232915969e

Thanks in Advance.

edited Nov 22 at 9:17

asked Nov 21 at 16:48

Habib Shahid

105

If you put an sql fiddle ( or equivalent ) together for us we might find it a little easier to help you. Rebuilding the relevant schema from the question data is not something I'm particularly interested in undertaking.
– Dan Farrell
Nov 21 at 16:50

1

Thank you for pointing out about the fiddle. here it is sqlfiddle.com/#!9/0ada4b/1/0
– Habib Shahid
Nov 21 at 18:17

add a comment |

up vote
0
down vote

favorite

Following is the table structure

---------------------------------------------------

USERs Activity Table

---------------------------------------------------



created_at              Event       User            Start_timestamp         End_Timestamp           Duration

-------------------     ---------   -------         -------------------     -------------------     --------

2018-11-20 18:01:22     LOGIN       john.doe        2018-11-20 18:01:22     2018-11-20 19:01:22     3600

2018-11-20 18:01:22     LOGIN       jane.miller     2018-11-20 18:10:03     2018-11-20 20:10:03     7200

2018-11-20 18:15:16     BREAK       john.doe        2018-11-20 18:15:16     2018-11-20 18:33:47     300

2018-11-20 18:33:47     ONCALL      john.doe        2018-11-20 18:33:47     2018-11-20 18:36:47     180

We want to generate a report which can give us 15 minutes interval of all users as shown below

---------------------------------------------------

Desired Result in 15 minutes time interval 

---------------------------------------------------



Intervals               User            LOGIN TIME      BREAK TIME      CALL TIME

-------------------     ----------      ----------      ----------      ---------

2018-11-20 18:00:00     john.doe        00:15:00        00:00:00        00:00:00

2018-11-20 18:00:00     jane.miller     00:04:57        00:00:00        00:00:00

2018-11-20 18:15:00     john.doe        00:15:00        00:00:00        00:00:00

2018-11-20 18:15:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 18:30:00     john.doe        00:15:00        00:15:00        00:00:00

2018-11-20 18:30:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 18:45:00     john.doe        00:15:00        00:03:15        00:03:00

2018-11-20 18:45:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:00:00     john.doe        00:01:22        00:00:00        00:00:00

2018-11-20 19:00:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:15:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:30:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:45:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 20:00:00     jane.miller     00:10:03        00:00:00        00:00:00

-------------------                     ----------      ----------      ----------

Following is the query I have been playing around with but no success so far.

    SELECT 

      DATE_FORMAT(SEC_TO_TIME(TIME_TO_SEC(a.created_at) - TIME_TO_SEC(a.created_at) % (15 * 60)),"%Y-%m-%d %H:%i") AS time_interval

      , a.user

      , SEC_TO_TIME(IFNULL(SUM(CASE WHEN a.event = 'LOGIN' THEN a.duration END),0)) AS login_time

      , SEC_TO_TIME(IFNULL(SUM(CASE WHEN a.event = 'BREAK' THEN a.duration END),0)) AS break_time

      , SEC_TO_TIME(IFNULL(SUM(CASE WHEN a.event = 'ONCALL' THEN a.duration END),0)) AS talk_time

    FROM users_activity AS a

    WHERE a.created_at > '2018-11-20 00:00:00' 

    GROUP BY time_interval, a.user

    ORDER BY time_interval DESC;

SQL Fiddle with Schema https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0d64482c499a07e9d1c7ec232915969e

Thanks in Advance.

edited Nov 22 at 9:17

asked Nov 21 at 16:48

Habib Shahid

105

If you put an sql fiddle ( or equivalent ) together for us we might find it a little easier to help you. Rebuilding the relevant schema from the question data is not something I'm particularly interested in undertaking.
– Dan Farrell
Nov 21 at 16:50

1

Thank you for pointing out about the fiddle. here it is sqlfiddle.com/#!9/0ada4b/1/0
– Habib Shahid
Nov 21 at 18:17

add a comment |

up vote
0
down vote

favorite

Following is the table structure

---------------------------------------------------

USERs Activity Table

---------------------------------------------------



created_at              Event       User            Start_timestamp         End_Timestamp           Duration

-------------------     ---------   -------         -------------------     -------------------     --------

2018-11-20 18:01:22     LOGIN       john.doe        2018-11-20 18:01:22     2018-11-20 19:01:22     3600

2018-11-20 18:01:22     LOGIN       jane.miller     2018-11-20 18:10:03     2018-11-20 20:10:03     7200

2018-11-20 18:15:16     BREAK       john.doe        2018-11-20 18:15:16     2018-11-20 18:33:47     300

2018-11-20 18:33:47     ONCALL      john.doe        2018-11-20 18:33:47     2018-11-20 18:36:47     180

We want to generate a report which can give us 15 minutes interval of all users as shown below

---------------------------------------------------

Desired Result in 15 minutes time interval 

---------------------------------------------------



Intervals               User            LOGIN TIME      BREAK TIME      CALL TIME

-------------------     ----------      ----------      ----------      ---------

2018-11-20 18:00:00     john.doe        00:15:00        00:00:00        00:00:00

2018-11-20 18:00:00     jane.miller     00:04:57        00:00:00        00:00:00

2018-11-20 18:15:00     john.doe        00:15:00        00:00:00        00:00:00

2018-11-20 18:15:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 18:30:00     john.doe        00:15:00        00:15:00        00:00:00

2018-11-20 18:30:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 18:45:00     john.doe        00:15:00        00:03:15        00:03:00

2018-11-20 18:45:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:00:00     john.doe        00:01:22        00:00:00        00:00:00

2018-11-20 19:00:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:15:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:30:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:45:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 20:00:00     jane.miller     00:10:03        00:00:00        00:00:00

-------------------                     ----------      ----------      ----------

Following is the query I have been playing around with but no success so far.

    SELECT 

      DATE_FORMAT(SEC_TO_TIME(TIME_TO_SEC(a.created_at) - TIME_TO_SEC(a.created_at) % (15 * 60)),"%Y-%m-%d %H:%i") AS time_interval

      , a.user

      , SEC_TO_TIME(IFNULL(SUM(CASE WHEN a.event = 'LOGIN' THEN a.duration END),0)) AS login_time

      , SEC_TO_TIME(IFNULL(SUM(CASE WHEN a.event = 'BREAK' THEN a.duration END),0)) AS break_time

      , SEC_TO_TIME(IFNULL(SUM(CASE WHEN a.event = 'ONCALL' THEN a.duration END),0)) AS talk_time

    FROM users_activity AS a

    WHERE a.created_at > '2018-11-20 00:00:00' 

    GROUP BY time_interval, a.user

    ORDER BY time_interval DESC;

SQL Fiddle with Schema https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0d64482c499a07e9d1c7ec232915969e

Thanks in Advance.

edited Nov 22 at 9:17

asked Nov 21 at 16:48

Habib Shahid

105

Following is the table structure

---------------------------------------------------

USERs Activity Table

---------------------------------------------------



created_at              Event       User            Start_timestamp         End_Timestamp           Duration

-------------------     ---------   -------         -------------------     -------------------     --------

2018-11-20 18:01:22     LOGIN       john.doe        2018-11-20 18:01:22     2018-11-20 19:01:22     3600

2018-11-20 18:01:22     LOGIN       jane.miller     2018-11-20 18:10:03     2018-11-20 20:10:03     7200

2018-11-20 18:15:16     BREAK       john.doe        2018-11-20 18:15:16     2018-11-20 18:33:47     300

2018-11-20 18:33:47     ONCALL      john.doe        2018-11-20 18:33:47     2018-11-20 18:36:47     180

We want to generate a report which can give us 15 minutes interval of all users as shown below

---------------------------------------------------

Desired Result in 15 minutes time interval 

---------------------------------------------------



Intervals               User            LOGIN TIME      BREAK TIME      CALL TIME

-------------------     ----------      ----------      ----------      ---------

2018-11-20 18:00:00     john.doe        00:15:00        00:00:00        00:00:00

2018-11-20 18:00:00     jane.miller     00:04:57        00:00:00        00:00:00

2018-11-20 18:15:00     john.doe        00:15:00        00:00:00        00:00:00

2018-11-20 18:15:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 18:30:00     john.doe        00:15:00        00:15:00        00:00:00

2018-11-20 18:30:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 18:45:00     john.doe        00:15:00        00:03:15        00:03:00

2018-11-20 18:45:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:00:00     john.doe        00:01:22        00:00:00        00:00:00

2018-11-20 19:00:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:15:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:30:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 19:45:00     jane.miller     00:15:00        00:00:00        00:00:00

2018-11-20 20:00:00     jane.miller     00:10:03        00:00:00        00:00:00

-------------------                     ----------      ----------      ----------

Following is the query I have been playing around with but no success so far.

    SELECT 

      DATE_FORMAT(SEC_TO_TIME(TIME_TO_SEC(a.created_at) - TIME_TO_SEC(a.created_at) % (15 * 60)),"%Y-%m-%d %H:%i") AS time_interval

      , a.user

      , SEC_TO_TIME(IFNULL(SUM(CASE WHEN a.event = 'LOGIN' THEN a.duration END),0)) AS login_time

      , SEC_TO_TIME(IFNULL(SUM(CASE WHEN a.event = 'BREAK' THEN a.duration END),0)) AS break_time

      , SEC_TO_TIME(IFNULL(SUM(CASE WHEN a.event = 'ONCALL' THEN a.duration END),0)) AS talk_time

    FROM users_activity AS a

    WHERE a.created_at > '2018-11-20 00:00:00' 

    GROUP BY time_interval, a.user

    ORDER BY time_interval DESC;

SQL Fiddle with Schema https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0d64482c499a07e9d1c7ec232915969e

Thanks in Advance.

mysql time intervals

edited Nov 22 at 9:17

asked Nov 21 at 16:48

Habib Shahid

105

edited Nov 22 at 9:17

asked Nov 21 at 16:48

Habib Shahid

105

edited Nov 22 at 9:17

asked Nov 21 at 16:48

Habib Shahid

105

asked Nov 21 at 16:48

Habib Shahid

105

asked Nov 21 at 16:48

Habib Shahid

105

If you put an sql fiddle ( or equivalent ) together for us we might find it a little easier to help you. Rebuilding the relevant schema from the question data is not something I'm particularly interested in undertaking.
– Dan Farrell
Nov 21 at 16:50

1

Thank you for pointing out about the fiddle. here it is sqlfiddle.com/#!9/0ada4b/1/0
– Habib Shahid
Nov 21 at 18:17

add a comment |

If you put an sql fiddle ( or equivalent ) together for us we might find it a little easier to help you. Rebuilding the relevant schema from the question data is not something I'm particularly interested in undertaking.
– Dan Farrell
Nov 21 at 16:50

1

Thank you for pointing out about the fiddle. here it is sqlfiddle.com/#!9/0ada4b/1/0
– Habib Shahid
Nov 21 at 18:17

If you put an sql fiddle ( or equivalent ) together for us we might find it a little easier to help you. Rebuilding the relevant schema from the question data is not something I'm particularly interested in undertaking.
– Dan Farrell
Nov 21 at 16:50

Thank you for pointing out about the fiddle. here it is sqlfiddle.com/#!9/0ada4b/1/0
– Habib Shahid
Nov 21 at 18:17

add a comment |

active

oldest

votes

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53416892%2fconvert-sql-query-to-mysql-with-split-time-interval%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

active

oldest

votes

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Btukfyl