SQL Server : populate table in 15 minute intervals

Question

I need to populate a table in SQL Server with an ID column and a TimeValue column with 15 minute intervals between 01/01/2000 and 01/01/2020.

It seems there must e a simple way to do it, but I'm new to T-SQL and can't find any easy way to achieve this.

i.e.

ID         Timevalue           
------------------------------
      0    01/01/2000 00:00:00
      1    01/01/2000 00:15:00
      2    01/01/2000 00:30:00
   ...      ...                
 701280    01/01/2020 00:00:00

If you're interested this table is being used to join with a table with timestamps and values - the joined table may/may not have blanks for some intervals but shouldn't do any grouping/averaging if multiple values appear between say 01:00-01:15 it should only report the value at 01:00 and 01:15. There needs to be a fixed number of output rows so it "syncs" with other tables which are being produced in Excel.

If you can think of a better way of doing this I would be keen to know!

Solution 1

You can use a numbers table

WITH Numbers AS
(
    SELECT TOP (10000) n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
    FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
)
SELECT id = ROW_NUMBER() OVER (ORDER BY n), [timestamp] = DATEADD(MINUTE, n, '00:00:00')
FROM Numbers
WHERE n % 15 = 0

Solution 2

You can create your time intervals using Recursive CTE:

CREATE TABLE Table1 (ID INT IDENTITY(0,1), TIMEVALUE DATETIME);

DECLARE @start DATETIME;
DECLARE @end DATETIME;

SET @start = '20000101';
SET @end = '20200101';

WITH CTE_DT AS 
(
    SELECT @start AS DT
    UNION ALL
    SELECT DATEADD(MINUTE,15,DT) FROM CTE_DT
    WHERE DT< @end
)
INSERT INTO Table1
SELECT DT FROM CTE_DT
OPTION (MAXRECURSION 0);

SQLFiddle DEMO

Solution 3

let's keept it simple, as there is always another looking our code ;)

DECLARE @start DATETIME, @end DATETIME  

SET @start = '20000101';
SET @end = '20000105';    --SET @end = '20200101';

WHILE @start < @end
BEGIN
  INSERT INTO Table1
  VALUES (@start)

  SET @start = DATEADD(MINUTE, 15, @start)
END

Comments

[Frédéric]

I would remove the filter where n % 15 = 0 and instead multiply n by fifteen, so dateAdd(mi, n * 15, dateFromParts(2000, 1, 1)). Why throwing away most of the generated numbers? Or am I missing something?