Whom can help me explain the ‘46.06:18:08’ time.

Database
Enthusiast

Whom can help me explain the ‘46.06:18:08’ time.

Dear SQLers,

A small question about time presentation using a integer data type. I juesed INTEGER FORMAT '99:99:99' to show time.

below in steps my chalance :-)

SELECT CAST( 110025 AS INTEGER FORMAT '99:99:99') AS VALUE1;
/*
  WAARDE1
1 11:00:25
*/

SELECT CAST( 111033 AS INTEGER FORMAT '99:99:99') AS VALUE2;
/*
  WAARDE2
1 11:10:33
*/

SELECT CAST( 111033 AS INTEGER FORMAT '99:99:99') -
       CAST( 110025 AS INTEGER FORMAT '99:99:99') AS VALUE3;
/*
 WAARDE3
1    1008 --<<-- value not presentation in time, I expected; 00:10:08
*/

/* My solution do an extra cast */
SELECT CAST( ( CAST( 111033 AS INTEGER FORMAT '99:99:99')-
               CAST( 110025 AS INTEGER FORMAT '99:99:99') ) AS INTEGER FORMAT '99:99:99') AS VALUE4;
/*
       WAARDE4
1 00:10:08 --<<-- OK !
*/

/* I made a type mistake in my query and deduced the future time form the start time.
   I expected a minus time; -00:10:08 but I was surprised by the result; 46.06:18:08 */
SELECT CAST( ( CAST( 110025 AS INTEGER FORMAT '99:99:99')-
               CAST( 111033 AS INTEGER FORMAT '99:99:99') ) AS INTEGER FORMAT '99:99:99') AS VALUE4;
/*
      VALUE4
1 46.06:18:08  --<<-- ??? I expected; -00:10:08
*/

Whom can help me explain the ‘46.06:18:08’ time.

Tags (2)
9 REPLIES
Teradata Employee

Re: Whom can help me explain the ‘46.06:18:08’ time.

You are really substracting two integer numbers not two "time" columns.

So you are doing  111033- 1100025 and display the result in the formnmat '99:99:99'

You are not converting integer to time. Format clause is not doing a comnversion is just a way of displaying the result.

Format '99:99:99' is not a time format  what means is you want to display ":" between 2nd and 3rd digit and another one between 4th and 5th.

You can use format to intoduce any character you want , but does not mean any conversion

Format '99-99-99' will display 46-08-00

You should cast to time instead that cast to integer

Enthusiast

Re: Whom can help me explain the ‘46.06:18:08’ time.

Hi gerado,

I agree with you to some extent, But convertin this to time is not solving the issue, Can you please try the following queries:

--Converting literals to time
SELECT CAST( '11:10:25' AS TIME(0)) - CAST('11:00:33' AS TIME(0)) ;

--Integer arithmatic
SELECT 111033 - 110025 ;

--Integer arithmatic with format
SELECT CAST(111033 - 110025 AS INTEGER FORMAT '99:99:99') ;

--Integer arithmatic with time conversion
SELECT CAST( CAST( 110025 AS INTEGER FORMAT '99:99:99') AS TIME(0))-
CAST( CAST( 111033 AS INTEGER FORMAT '99:99:99') AS TIME(0)) AS VALUE4;
Khurram
Enthusiast

Re: Whom can help me explain the ‘46.06:18:08’ time.

Hi,

as gerado has explained that even with a format the underlying data type do not change and the resulting arithmatic is simple INTEGER arithmatic not the Time arithmatic. 

You can use the below query to find the time difference:

 SELECT TIME  '11:10:25'  - TIME '11:00:33' HOUR TO SECOND(0) ;
Khurram
Junior Contributor

Re: Whom can help me explain the ‘46.06:18:08’ time.

This strange value is only returned when you have an ODBC connection, so it seems to be a SQLA or ODBC bug: 

SELECT
CAST(-1008 AS SMALLINT FORMAT '99:99:99'),
CAST(-1008 AS INTEGER FORMAT '99:99:99'),
CAST(-1008 AS FLOAT FORMAT '99:99:99')

-1,008 46.06:18:08 46.06:18:08

You should open an incident.

As already noted don't try to calculate a time difference using INTs, use TIMEs instead.

Enthusiast

Re: Whom can help me explain the ‘46.06:18:08’ time.

all SQL college's,

Thanks for the response, I have changed my procedure to make use of time instead of integer presented as time "lesson learned" THX. And Dieter, you’re right, I'm working from a windows client using SQLA wit which is connected by ODBC to the database.
Opening an incident is another chapter, it would be my first and at this moment i don't have a clue how to do this.

Joop
Enthusiast

Re: Whom can help me explain the ‘46.06:18:08’ time.

Hi,

There are several options available in ODBC for how to treat date and time, like III, AAA etc, I tried them as well but it didn't worked. 

But its always good to use time and date data types for date and time manipulations. It saves a lot of unexpected results.

Khurram
Teradata Employee

Re: Whom can help me explain the ‘46.06:18:08’ time.

when you wrote

SELECT CAST( '11:10:25' AS TIME(0)) - CAST('11:00:33' AS TIME(0))

 you are missing HOUR TO SECOND

You should write

 

SELECT CAST( '11:10:25' AS TIME(0)) - CAST('11:00:33' AS TIME(0)) HOUR TO SECOND ;

Enthusiast

Re: Whom can help me explain the ‘46.06:18:08’ time.

Hi gerado,

This query is not working for me, Have you tested it on your machine?

Khurram
Teradata Employee

Re: Whom can help me explain the ‘46.06:18:08’ time.

I have TD 14.00 and using SQL assistant 14.01

Running  

SELECT CAST( '11:10:25' AS TIME(0)) - CAST('11:00:33' AS TIME(0)) HOUR TO SECOND ;

I get  as answer :

0:09:52.000000

that is right .

I have tried also with BTEQ 13.10 and works fine.