How to Round Timestamp(6) to nearest hour

Database
Junior Contributor

Re: How to Round Timestamp(6) to nearest hour

Use this function (or the calculation within) to get seconds:

REPLACE FUNCTION TimeStamp_Diff_Seconds
(
   ts1 TIMESTAMP(6)
  ,ts2 TIMESTAMP(6)
)
RETURNS DECIMAL(18,6)
LANGUAGE SQL
CONTAINS SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
(Cast((Cast(ts2 AS DATE)- Cast(ts1 AS DATE)) AS DECIMAL(18,6)) * 60*60*24)
      + ((Extract(  HOUR From ts2) - Extract(  HOUR From ts1)) * 60*60)
      + ((Extract(MINUTE From ts2) - Extract(MINUTE From ts1)) * 60)
      +  (Extract(SECOND From ts2) - Extract(SECOND From ts1))
;

Then divide by 60 to get minutes and apply ROUND or CEILING or CAST:

Cast(TimeStamp_Diff_Seconds (TIMESTAMP '2018-02-12 14:08:02.000000',TIMESTAMP '2018-02-12 17:54:52.000000') / 60 AS DEC(18,0))

 

Enthusiast

Re: How to Round Timestamp(6) to nearest hour

I want the timestamp diffrence in Minutes and Round before the Converting timestamp to minutes.

I can't use UDF function as we have to give queeries to business users.

Junior Contributor

Re: How to Round Timestamp(6) to nearest hour

Use this function (or the calculation within) to get seconds:

Then divide by 60 to get minutes and apply ROUND or CEILING or CAST: 


 

Highlighted
Enthusiast

Re: How to Round Timestamp(6) to nearest hour

Thank you Dnoeth its working.. but I have

 

IN_Timestamp: 2/12/2018 10:51:32.000000 (A.M)

OUT_TIMESTAMP : 2/12/2018 13:18:05.000000 (P.M)

Output should be 146 but i got o/p as 147 ..How to handle P.M and A.M Timestamps?

 

Junior Contributor

Re: How to Round Timestamp(6) to nearest hour

Well, it's 146 minutes and 33 seconds, of course 146.55 rounds to 147 minutes.

Enthusiast

Re: How to Round Timestamp(6) to nearest hour

but in reports it is showing as 146 but in report also they are using round function

Junior Contributor

Re: How to Round Timestamp(6) to nearest hour

Then this report doesn't apply rounding, there must be some other rules to determine the result.

Enthusiast

Re: How to Round Timestamp(6) to nearest hour

https://www.timeanddate.com/date/durationresult.html?m1=2&d1=12&y1=2018&m2=2&d2=12&y2=2018&h1=10&i1=...

 

I did subtract 2 timestamp in (2/12/2018 10:51:32.000000 - 2/12/2018 13:18:05.000000) in above site - i got 146 (rounded minutes)

 

SyntaxEditor Code Snippet

select case when extract (second from ts)>30 then ts  - cast(extract(second from ts) as interval second)+interval '1' minute
else ts  -cast(extract(second from ts) as interval second)end 

Rounding function used in report 

Teradata Employee

Re: How to Round Timestamp(6) to nearest hour

If you do the arithmetic yourself, as @dnoeth did, you will see that the difference is 146.55 seconds.  Which

definition of "rounding" do you want to use?

Junior Contributor

Re: How to Round Timestamp(6) to nearest hour

The link you posted claims 146 minutes (rounded down), but it's simply truncated (same 146 minutes for 2:26:59).

 

Maybe someone should explain them what rounding actually means :-)