How do i use ceil and floor value based on miliseconds

Database
Enthusiast

How do i use ceil and floor value based on miliseconds

How do i select Ceil and Floor value on seconds based on condition?

If the miliconds are less than or equal to 900 miliseconds then use floor value for seconds and if the miliseconds are greater than 900 then use Ceiling value.(See examples below).

TimestampExpected TS
08:43:46.71008:43:46.000
08:42:37.90008:42:37.000
11:50:06.93011:50:07.000

Thank you in advance.:-)

5 REPLIES 5
Teradata Employee

Re: How do i use ceil and floor value based on miliseconds

CEIL and FLOOR apply only to numeric values.

 

In your case, it may be easier to add INTERVAL '0.099' SECOND and then truncate the fractional seconds by converting to character and back (CASTs or TO_CHAR/TO_TIMESTAMP).

Ambassador

Re: How do i use ceil and floor value based on miliseconds

Or without casting to string and back:

                           (ts + interval '0.099' second)
- cast(extract(second from (ts + interval '0.099' second)) mod 1 as interval second(1,3))

 

Enthusiast

Re: How do i use ceil and floor value based on miliseconds

Thank you.....Can you please share syntax how to write this cause i am very new to teradata.

Appreciate it.

Enthusiast

Re: How do i use ceil and floor value based on miliseconds

I used as below but i got the error "Interval field overflow".

,(TIMESTAMP_COLUMN + INTERVAL '0.099' SECOND)
- CAST(EXTRACT(SECOND FROM (TIMESTAMP_COLUMN + INTERVAL '0.099' SECOND)) MOD 1 AS INTERVAL SECOND(1,3))

Enthusiast

Re: How do i use ceil and floor value based on miliseconds

Thank you and i really appreciate it you help me

 

I tried different way and it's worked for me (As below):

 CASE        WHEN EXTRACT(SECOND FROM TimeStampColumn)<10.00 THEN
        
  CASE WHEN CAST(SUBSTRING(CAST(EXTRACT(SECOND FROM TimeStampColumn) AS VARCHAR(6)) FROM 3 FOR 3) AS INT) <= 900
             THEN SUBSTRING(CAST(TimeStampColumn AS VARCHAR(26)) FROM 12 FOR 8)
             WHEN CAST(SUBSTRING(CAST(EXTRACT(SECOND FROM TimeStampColumn) AS VARCHAR(6)) FROM 3 FOR 3) AS INT) > 900
             THEN SUBSTRING(CAST(TimeStampColumn AS VARCHAR(26)) FROM 12 FOR 5)
             ||':'||
     
        CAST('0'||OREPLACE(CAST(TRIM(CAST(SUBSTRING(CAST(EXTRACT(SECOND FROM TimeStampColumn) AS VARCHAR(6)) FROM 0 FOR 2) AS INTEGER))+1 AS VARCHAR(3)),'.','') AS VARCHAR(2))
           END ||'.000'
          
             WHEN EXTRACT(SECOND FROM TimeStampColumn)>=10.00 THEN
            
        CASE WHEN CAST(SUBSTRING(CAST(EXTRACT(SECOND FROM TimeStampColumn) AS VARCHAR(6)) FROM 4 FOR 3) AS INT) <= 900
             THEN SUBSTRING(CAST(TimeStampColumn AS VARCHAR(26)) FROM 12 FOR 8)
             WHEN CAST(SUBSTRING(CAST(EXTRACT(SECOND FROM TimeStampColumn) AS VARCHAR(6)) FROM 4 FOR 3) AS INT) > 900
             THEN SUBSTRING(CAST(TimeStampColumn AS VARCHAR(26)) FROM 12 FOR 5)
             ||':'||
     
        CAST( OREPLACE(CAST(TRIM(CAST(SUBSTRING(CAST(EXTRACT(SECOND FROM TimeStampColumn) AS VARCHAR(6)) FROM 1 FOR 2) AS INTEGER))+1 AS VARCHAR(3)),'.','') AS VARCHAR(2))
           END
               ||'.000'
 END
  ) AS VARCHAR(12)) AS ExpectedTimeStamp