0

I would like to generate below list for any given date to pass in a MySQL query.

Example : given date is "2018-08-28"

I need the output as 

between '2018-08-28 00:00:00' and '2018-08-28 00:29:59'
between '2018-08-28 00:30:00' and '2018-08-28 00:59:59'
between '2018-08-28 1:00:00' and '2018-08-28 1:29:59'
between '2018-08-28 1:30:00' and '2018-08-28 1:59:59'
-
-
-
-

between '2018-08-28 23:00:00' and '2018-08-28 23:29:59'
between '2018-08-28 23:30:00' and '2018-08-28 23:59:59'
dbar
  • 33
  • 1
  • 5
  • Is the above the _literal_ output you need? If not, then what do you need? – Tim Biegeleisen Aug 29 '18 at 05:31
  • Read this: https://stackoverflow.com/questions/701444/how-do-i-make-a-row-generator-in-mysql then generate 48 numbered rows, then add (rownumber * 30) minutes to your date, then use a bit of string concat to build your results – Caius Jard Aug 29 '18 at 05:41
  • @TimBiegeleisen yes above is the output I need. It has to be for all the hours(0-23) – dbar Aug 29 '18 at 05:42
  • Or do it in excel (easier) and paste it in (I assume you're looking for an easy way to write a case when). If yoire trying to produce a report of a day cut into half hour blocks and eg a count of the number of events in that block, the query would be different, post the actual problem you're trying to solve – Caius Jard Aug 29 '18 at 05:44
  • 1
    MySQL is for the storage and retrieval of data. Do other stuff elsewhere. – Strawberry Aug 29 '18 at 05:51
  • 1
    what time is thirty o'clock – Strawberry Aug 29 '18 at 06:11

1 Answers1

0
DECLARE @dtStart AS DATETIME ='20180828 00:00:00' 
        ,@dtEnd AS DATETIME = '20180828 23:59:59'
        ,@iInterval AS INT = 30;  --30 min interval


WITH aCTE
AS(
    SELECT 
        @dtStart AS StartDateTime,
        DATEADD(MINUTE,@iInterval,@dtStart) AS EndDateTime
    UNION ALL
    SELECT 
        DATEADD(MINUTE,@iInterval,StartDateTime),
        DATEADD(MINUTE,@iInterval,EndDateTime)
    FROM aCTE
    WHERE
        DATEADD(MINUTE,@iInterval,EndDateTime) <= @dtEnd
)

SELECT 

    CONVERT(VARCHAR(10),StartDateTime,108) 
    + ' ' + RIGHT(CONVERT(VARCHAR(30), StartDateTime, 9), 2) 
    + ' - ' +

    CONVERT(VARCHAR(10),EndDateTime,108) 
    + ' ' + RIGHT(CONVERT(VARCHAR(30), EndDateTime, 9), 2) AS Result
FROM aCTE

Try this and revert me if you find any issue.

SolveProblem
  • 83
  • 1
  • 9