CODEWITHSUNDEEP
sqlteradata
dev
dev

Reputation: 961

is there anything like timestamp(9) in teradata

I just encountered a scenario where I have to load data like :

'2014-12-24 13:59:10:060606066'

in a Teradata table have datatype as Timestamp as you can see in millisecond format there are 9 digits , is there any way to load such data or Teradata does'nt supports it at all.

for now i have removed last 3 digits made it like '2014-12-24 13:59:10:060606' and loaded in database but I may need to handle it in future.

Upvotes: 0

Views: 1765

Answers (1)

Rob Paller
Rob Paller

Reputation: 7786

Perhaps you can accomplish this by storing three fields in your table:

  1. Message_Queue_Timestamp TIMESTAMP(6) NOT NULL
  2. Message_Queue_Date DATE NOT NULL
  3. Message_Queue_Seconds_From_Midnight FLOAT NOT NULL

Using dnoeth's suggestion to convert the input using to_timestamp() to retain a DBA-friendly timestamp in the table. This is Message_Queue_Timestamp.

Then split the incoming timestamp into two fields: Message_Queue_Date and Message_Queue_Seconds_From_Midnight. I was originally going to suggest that the latter be stored as DECIMAL(14,9) but then realized as a FLOAT formatting/converting it to a TIME in Teradata is easier. These two fields allow you to retain the precision of the timestamp that was ingested by the database without having to store the data as a character string.

The conversion of the timestamp to seconds should be fairly straight forward in your ETL processing. Hope this helps.

Upvotes: 1

Related Questions