How to round a timestamp ?

Database

How to round a timestamp ?

Hello

I would like to round a timestamp(6) to have this kind of result :
2008-02-26 08:50:13.312000 --> 2008-02-26 08:50:13
2008-02-26 08:50:13.951000 --> 2008-02-26 08:50:14
The number of seconds has to be rounded depending of the number of millisecondes.
If I use a CAST(myfield AS CHAR(19)), the timestamp is truncated, not rounded.
Do you know if there is a solution to do that ?
Thanks a lot for your help.
Emilie
2 REPLIES
Teradata Employee

Re: How to round a timestamp ?


Select
CAST(CURRENT_TIMESTAMP as TIMESTAMP(6)) AS A_TIMESTAMP,
Extract(second FROM A_TIMESTAMP) as SECONDS,
(CASE WHEN (SECONDS MOD CAST(SECONDS as Integer)) > 0.5
THEN INTERVAL '1' SECOND
ELSE INTERVAL '0' SECOND END) as SUBSECONDS,
CAST(CAST(A_TIMESTAMP as Char(19)) as TIMESTAMP(0)) + SUBSECONDS;

rgs
Enthusiast

Re: How to round a timestamp ?

Another way! Try this:

select the_event, cast(cast((the_event +
cast(0.5 as interval second(1,6))) as char(19)) as timestamp(0))
from mytime order by 1;

the_event (the_event+0.5)
-------------------------- -------------------
2008-03-12 15:54:57.520000 2008-03-12 15:54:58
2008-03-12 15:55:01.140000 2008-03-12 15:55:01
2008-03-12 15:55:02.500000 2008-03-12 15:55:03
2008-03-12 15:55:03.770000 2008-03-12 15:55:04
2008-03-12 15:55:04.960000 2008-03-12 15:55:05
2008-03-12 15:55:08.190000 2008-03-12 15:55:08

By adding a 1/2 second to the time anything at a .5 or over will go to the next second. Then you truncate the fraction and you end up with what you want.