Search code examples
sqlt-sqldateadddatepart

Update datetime timestamp minute value

Our data logger has recorded some non-processable data.
The logger is logging 10 min mean values.
Due to a bug of manufacturer device it has recorded some timestamps with jumping minute values like follows

plantid                                 Value               Timestamp  
1F617EDE-7B11-416A-B8D1-FDD566020E13    1.10000002384186    2013-10-26 06:02:00.000
1F617EDE-7B11-416A-B8D1-FDD566020E13    0.800000011920929   2013-10-26 08:56:00.000

As you can see, in the first record there is 02 and in the second 56 minutes. I have to fix These values back to 0 (in row 1) and to 50 in in the second row to make the data available for further processing and analysis. So 10 minute values.

I tried like the following

DECLARE @ttest DATETIME = '2013-10-26 08:56:00.000'
SELECT @ttest
SET @ttest = Dateadd(minute,-DATEPART(MINUTE, @ttest),@ttest)
SELECT @ttest

but the result would be 2013-10-26 08:00:00.000 because I calculate minus 56 but I would need to calculate minus 6

So how can I subtract only the single minute value 6 instead of 56

Solution

  • You can use modulo %10 to get the single digit minute value:

    Dateadd(minute, -DATEPART(MINUTE, @ttest) % 10, @ttest)

    Modulo returns the remainder of a division, so in case of 56%10 the remainder will be 6.

    If you need to remove second/millisecond/nanosecond too one way would be to chain several dateadd operations, although I suspect that there might be a better way.