CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Prateeth
Prateeth

Reputation: 33

How to split time range into intervals in a 'Select' SQL query

I'm a newbie in SQL and I'm writing a few SQL queries offlate at work. I've come across a certain blocker, can you guys help me with a solution or an alternative?

Here's the query that I use (using just 1 row as reference):

Select 
USER_NAME, 
TIME(CONVERT_TZ(START_TIME, '+00:00','America/Los_Angeles')) as 'PST Start',
TIME(CONVERT_TZ(END_TIME, '+00:00','America/Los_Angeles')) as 'PST End',
name as 'Type',
date(start_time) as 'Date',
WORKGROUP_NAME
from USER_SCHEDULE_INSTANCES 
where user_name = 'prateeth' 
and date(START_TIME) = '2018-01-01'
limit 1

and this is the output

USER_NAME   PST Start   PST End     Date        Type    WORKGROUP_NAME
prateeth    06:00:00    09:00:00    2018-01-01  Work    NACS Command Center

what do I do to split this into half hour intervals, without using a macro and to receive the output like this:

USER_NAME   PST Start   PST End     Date        Type    WORKGROUP_NAME
prateeth    06:00:00    06:30:00    2018-01-01  Work    NACS Command Center
prateeth    06:30:00    07:00:00    2018-01-01  Work    NACS Command Center
prateeth    07:00:00    07:30:00    2018-01-01  Work    NACS Command Center
prateeth    07:30:00    08:00:00    2018-01-01  Work    NACS Command Center
prateeth    08:00:00    08:30:00    2018-01-01  Work    NACS Command Center
prateeth    08:30:00    09:00:00    2018-01-01  Work    NACS Command Center

Upvotes: 2

Views: 1451

Answers (1)

Twelfth
Twelfth

Reputation: 7180

Comment to answer. Create a time table and populate with the time frames you are looking for. something along these lines:

Table Name: Dim_Time 
example data:

ID    starttime   endtime
1     06:00       06:30
2     06:30       07:00
etc

When you need to split to time frames, in your from statement cross join to this table.

From  USER_SCHEDULE_INSTANCES 
join dim_time on 1=1

You can add a second ID column in this time table that will allow you to have multiple 'versions' of this time break down as well.

Hope that is clear, comment if you need more.

Upvotes: 1

Related Questions