title: Aggregating (group) time by 15 seconds intervals // Microsoft SQL Server 2019
body: <p>I'm new to Microsoft SQL Server 2019. Currently I'm trying to aggregate <code>Timestamp</code> (type <code>datetime</code>) by 15 second intervals.</p>
  
  <p>Desired result:</p>
  
  <pre><code>Timestamp               Timestamp2
  2019-01-01 04:00:00.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:01.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:02.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:03.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:04.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:05.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:06.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:07.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:08.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:09.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:10.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:11.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:12.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:13.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:14.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:15.487 2019-01-01 04:00:30.000
  2019-01-01 04:00:16.487 2019-01-01 04:00:30.000
  2019-01-01 04:00:17.487 2019-01-01 04:00:30.000
  2019-01-01 04:00:18.487 2019-01-01 04:00:30.000
  2019-01-01 04:00:19.487 2019-01-01 04:00:30.000
  </code></pre>
  
  <p>I've tried to convert "1 minute interval"
  from </p>
  
  <pre><code>Select 
  Timestamp, 
  dateadd(MINUTE, 1+datediff(MINUTE, 0, [Timestamp]), 0) AS Timestamp2 
  FROM tags.dbo.jan
  where ValueID = '349' and Timestamp &lt; '2019-01-01 04:02:00.000'
  </code></pre>
  
  <p>to</p>
  
  <pre><code>Select   
  Timestamp, 
  dateadd(second, 15+datediff(second, 0, [Timestamp]), 0) AS Timestamp2 
  FROM tags.dbo.jan
  where ValueID = '349' and Timestamp &lt; '2019-01-01 04:02:00.000'
  </code></pre>
  
  <p>and faced with an issue </p>
  
  <pre><code>The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
  </code></pre>
  
  <p>Then I've tried to use <code>datediff_big</code></p>
  
  <pre><code>Select   
  Timestamp, 
  dateadd(second, 15+datediff_big(second, 0, [Timestamp]), 0) AS Timestamp2 
  FROM tags.dbo.jan
  where ValueID = '349' and Timestamp &lt; '2019-01-01 04:02:00.000'
  </code></pre>
  
  <p>and got error </p>
  
  <pre><code>Arithmetic overflow error converting expression to data type int.
  </code></pre>
  
  <p>I've tried to use </p>
  
  <pre><code>Select Timestamp,
         datetimefromparts(year(Timestamp), month(Timestamp), day(Timestamp),
                           datepart(hour, Timestamp),
                           datepart(minute, Timestamp),
                           (ceiling(datepart(second, Timestamp)) / 15) * 15,
                           0
                          ) as timestamp2
  FROM jan.dbo.jan
  where ValueID = '349' and
        Timestamp &lt; '2019-01-01 04:02:00.000';
  </code></pre>
  
  <p>but I have these result</p>
  
  <pre><code>2019-01-01 04:00:12.487 2019-01-01 04:00:00.000
  2019-01-01 04:00:13.487 2019-01-01 04:00:00.000
  2019-01-01 04:00:14.487 2019-01-01 04:00:00.000
  2019-01-01 04:00:15.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:16.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:17.487 2019-01-01 04:00:15.000
  </code></pre>
  
  <p>instead of desired </p>
  
  <pre><code>2019-01-01 04:00:12.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:13.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:14.487 2019-01-01 04:00:15.000
  2019-01-01 04:00:15.487 2019-01-01 04:00:30.000
  2019-01-01 04:00:16.487 2019-01-01 04:00:30.000
  2019-01-01 04:00:17.487 2019-01-01 04:00:30.000
  </code></pre>
  
  <p>It should start from the first 15 second group </p>