CODEWITHSUNDEEP
sqlsql-server
MissKnacki
MissKnacki

Reputation: 279

Truncate timestamp

I would like into a stored procedure, truncate timestamp input values at the top hour or at the lower hour.

For example, if my input values are 2020-02-12 06:56:00 and 2020-02-12 07:14:00, I would like to transforme it in 2020-02-12 06:00:00 and 2020-02-12 08:00:00

Is a cast function can work?

Upvotes: 0

Views: 706

Answers (3)

Squirrel
Squirrel

Reputation: 24783

Using a bit of arithmetic calculation, convert to hours with decimal and use floor() and ceiling() to perform the round up / down

first it find the time different with 00:00:00 in terms of second. convert(date, date_col) convert the datetime to date, so effectively it is 00:00:00

datediff(second, convert(date, date_col), date_col)

then you divide by 60 x 60 = 3600 seconds. Gives you fraction of hours

then you use floor() or ceiling() to perform the rounding

and lastly you add that back to the date (convert(date, date_col))

Final query

select  *,
        RoundDown   = convert(datetime, convert(date, date_col)) 
        + dateadd(hour, floor(datediff(second, convert(date, date_col), date_col) / (3600.0)), 0),
        RoundUp     = convert(datetime, convert(date, date_col))
        + dateadd(hour, ceiling(datediff(second, convert(date, date_col), date_col) / (3600.0)), 0)

from    (
            values 
            ('2020-02-12 06:56:00'),
            ('2020-02-12 07:14:00')
        ) d (date_col)

/*
2020-02-12 06:56:00 2020-02-12 06:00:00 2020-02-12 07:00:00
2020-02-12 07:14:00 2020-02-12 07:00:00 2020-02-12 08:00:00
*/

EDIT : a much simpler query below

find the different in minute divide by 60.0 minutes to get different in terms of hour (with decimal places) and then apply floor or ceiling. Finally add that result back

select  getdate() as Now, 
        dateadd(hour, floor(datediff(minute, 0, getdate()) / 60.0), 0) as RoundDown,
        dateadd(hour, ceiling(datediff(minute, 0, getdate()) / 60.0), 0) as RoundUp

Upvotes: 1

Nicola Lepetit
Nicola Lepetit

Reputation: 795

This is a simpler solution:

declare @start datetime = '2020-02-12 06:56:00' 
declare @end datetime = '2020-02-12 07:14:00'    

select @start as OriginalStart,
       @end as OriginalEnd,
       dateadd(hour, datediff(hour, 0, @start), 0) as TruncatedStart, 
       dateadd(hour, datediff(hour, 0, dateadd(hour, 1, @end)), 0) as TruncatedEnd

In both cases the function substracts the hour part from the original timestamp. For the TruncatedEnd, one hour is added, so that the result is the subsequent hour.

Upvotes: 1

Marc Guillot
Marc Guillot

Reputation: 6465

You can construct the new datetimes from the parts that you want of your original datetimes.

declare @start datetime = '2020-02-12 06:56:00'
declare @end datetime = '2020-02-12 07:14:00'

select @start as OriginalStart,
       @end as OriginalEnd,
       datetimefromparts(year(@start), month(@start), day(@start), datepart(hour, @start), 0, 0, 0) as TruncatedStart,
       dateadd(hour, 1, datetimefromparts(year(@end), month(@end), day(@end), datepart(hour, @end), 0, 0, 0)) as TruncatedEnd

The first truncation of the interval is the lower hour, and the second one adds an additional hour so it returns the higher hour.

PS: If what you want is to round to the nearest hour, then you can add 30 minutes and truncate :

declare @date datetime = '2020-02-12 06:56:00'

set @date = dateadd(minute, 30, @date)

select datetimefromparts(year(@date), month(@date), day(@date), datepart(hour, @date), 0, 0, 0) as NearestHour

or in a single step (using Lepetit's shortcut for truncation) :

declare @date datetime = '2020-02-12 06:56:00'

select dateadd(hour, datediff(hour, 0, dateadd(minute, 30, @date)), 0) AS NearestHour

Upvotes: 2

Related Questions