CODEWITHSUNDEEP
sqlt-sqldatetimedate-range
Indigo_heart
Indigo_heart

Reputation: 139

For each minute add row between ranges

I have table with date ranges

enter image description here

and i need to add rows between this ranges. I must granulate this table to minutes. How can i add this extra rows

Upvotes: 0

Views: 737

Answers (2)

MatBailie
MatBailie

Reputation: 86716

The recursive CTE option from @MatthewBaker would only need minor changes to meet your needs.

WITH
   by_minute
AS
(
    SELECT *, datetime_from, minute_marker FROM your_table
    UNION ALL
    SELECT *, DATEADD(minute, 1, minute_marker) FROM by_minute WHERE DATEADD(minute, 1, minute_marker) < datetime_to
)

SELECT
    *
FROM
    by_minute
OPTION
    (MAXRECURSION 0)

The OPTION (MAXRECURSION 0) allows SQL Server to keep recursively generating the minutes beyond the default of 100. Still, I would not recommend this if the intervals being generated are more than a few hundred minutes long (maybe up to one day [1440 minutes]).

In such a case the simpler approach would be to utilise a table of numbers, and simply join on to that.

An example for creating such a table could be : https://www.mssqltips.com/sqlservertip/4176/the-sql-server-numbers-table-explained--part-1/

From there, you just join on the number of row that you need...

SELECT
    yourTable.*,
    DATEADD(minute, Numbers.[Number], yourTable.datetime_from)  AS minute_marker
FROM
    yourTable
INNER JOIN
     dbo.Numbers
         ON  Numbers.[Number] >= 0
         AND Numbers.[Number] <  DATEDIFF(minute, yourTable.datetime_from, yourTable.datetime_to)

Another recommendation I have is to NOT use 59th second to represent the end of a minute. What if you get data at 59.600 seconds? That's after then end of the minute but before the start of the new one? Instead use markers that are Inclusive Start and Exclusive End...

The first minute of 2012 = '2012-01-01 00:00:00.000' -> '2012-01-01 00:01:00.000'
The final minute of 2012 = '2012-12-31 23:59:00.000' -> '2013-01-01 00:00:00.000'

With such a structure you only ever need my_point_in_time >= start AND my_point_in_time < end, and you never need worry about the precision of the datatypes being used.

(It also matches human natural language. When we say things like between 1 and 2 we most often mean >= 1 AND < 2.)

Upvotes: 1

Matthew Baker
Matthew Baker

Reputation: 2729

If you use the following:

WITH    cte
      AS (SELECT    CAST('2017-01-01 00:00:00' AS DATETIME) AS startTime
          UNION ALL
          SELECT    DATEADD(MINUTE, 1, startTime)
          FROM      cte
          WHERE     startTime < '2017-01-02 00:00:00'
         )
SELECT  *
FROM    cte

OPTION (MAXRECURSION 0)

It will give you a minute by minute result. Substitute in the range you want. You can then use that as a basis to write an insert. Iterative CTE's aren't the most efficient, but probably the easiest

Upvotes: 0

Related Questions