millisecond part is stripped from time(6) column

Database
HP
Enthusiast

millisecond part is stripped from time(6) column

Hi Experts,

I am trying to load a column of datatype time(6),

however when I load it with say value like 20:59:57.154

i get only 20:59:57 when i select and the millisecond part (.154) of the data is stripped off.

Please note, i tried changing the odbc datetime format to AAA in sql assistant connection.

Any idea what might be causing this, and any solution?

Regards

HP

6 REPLIES
Supporter

Re: millisecond part is stripped from time(6) column

Works fine on my system - see bteq log below.

How do you query the table?

Try

select cast(yourcolumn as varchar(30))

from yourtable

to check that this is not a problem of the query tool.

How do you import the data?

BTEQ LOG:

BTEQ 13.10.00.01 Sat Feb 25 09:57:07 2012

+---------+---------+---------+---------+---------+---------+---------+----
.run file logon.txt;
+---------+---------+---------+---------+---------+---------+---------+----
.logon xxx/yyy,

*** Logon successfully completed.
*** Teradata Database Release is 13.10.01.02
*** Teradata Database Version is 13.10.01.04
*** Transaction Semantics are BTET.
*** Session Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----
*** Warning: EOF on INPUT stream.
+---------+---------+---------+---------+---------+---------+---------+----

.set width 500;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

Create table test_a_a (a timestamp(6)) primary index (a);

*** Table has been created.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
insert into test_a_a values ('2000-01-01 00:01:01.123456');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
select *
from test_a_a;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

a
--------------------------
2000-01-01 00:01:01.123456

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
*** Warning: EOF on INPUT stream.
drop table test_a_a;

*** Table has been dropped.
*** Total elapsed time was 1 second.

*** BTEQ exiting due to EOF on stdin.

*** Exiting BTEQ...
*** RC (return code) = 0
HP
Enthusiast

Re: millisecond part is stripped from time(6) column

Hi Ulrich,

Thanks for your reply,  My requirement is to load TIME datatype, and not the timestamp,

so if you try the script below, does it work for you

--drop TABLE tbla1;

CREATE SET TABLE tbla1 ,NO FALLBACK ,

NO BEFORE JOURNAL, NO AFTER JOURNAL

(c1 DATE FORMAT 'YYYY-MM-DD',

c2 TIME(6),

c3 TIMESTAMP(6))

PRIMARY INDEX ( c1 );

insert into tbla1

select date,current_time, current_timestamp;

sel * from  tbla1;






c1 c2 c3
2/25/2012 13:19:32 2/25/2012 13:19:32.360000

so if you see column c2 you will see the millisecond part is missing, but its there in column c3.

any idea how i can get the millisecond part in c2?

Thanks

HP

Supporter

Re: millisecond part is stripped from time(6) column

current_time(6) (and current_timestamp(6)) will have only 2 significant digits - see documentation...

if you import data with 6 digits it should work - see log below...

BTEQ 13.10.00.01 Sat Feb 25 15:59:52 2012

+---------+---------+---------+---------+---------+---------+---------+----
.run file logon.txt;
+---------+---------+---------+---------+---------+---------+---------+----
.logon neo/uli,

*** Logon successfully completed.
*** Teradata Database Release is 13.10.01.02
*** Teradata Database Version is 13.10.01.04
*** Transaction Semantics are BTET.
*** Session Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----
*** Warning: EOF on INPUT stream.
+---------+---------+---------+---------+---------+---------+---------+----

.set width 500;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

Create table test_a_a (a timestamp(6), b time(6) ) primary index (a);

*** Table has been created.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
insert into test_a_a values ('2000-01-01 00:01:01.123456','00:01:01.123456' );

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
select *
from test_a_a;

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

a b
-------------------------- ---------------
2000-01-01 00:01:01.123456 00:01:01.123456

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
*** Warning: EOF on INPUT stream.
drop table test_a_a;

*** Table has been dropped.
*** Total elapsed time was 1 second.

*** BTEQ exiting due to EOF on stdin.

*** Exiting BTEQ...
*** RC (return code) = 0

HP
Enthusiast

Re: millisecond part is stripped from time(6) column

Hi Ulrich,

Yes the above query works for me in bteq, however i am not able to see the same results in sql Assistant.

the millisecond part is not visible in sql assistant.

please see the result below from sqla..

a                                          |        b

1/1/2000 00:01:01.123456 |        00:01:01

i am using sql assistant 13.10.0.02, and the documentation suggests that fractional seconds are not displayed by ODBC.

Alternatively, i tried SQL JE  and it works like a charm :)

Regards

HP

Supporter

Re: millisecond part is stripped from time(6) column

yes, seems to be an interface problem. its interessting that timestamp and time is giving different results in respect to the millisec. Also try .net connection, maybe this show them correctly - I didn't run it...

Supporter

Re: millisecond part is stripped from time(6) column

Hi HP,

seems to be an ODBC issue.

Teradata.Net connection with SQL Assitent 13.11 gives

a                                           b

01.01.2000 00:01:01.123456 00:01:01.123456

ODBC connections with SQL Assitents 13.11 gives 

a                                           b

01.01.2000 00:01:01.123456 00:01:01