CODEWITHSUNDEEP
sqlteradatacreate-table
dev
dev

Reputation: 961

store duration in teradata

I am getting source data with duration between 2 timestamps as 

Duration            Start date          End date        Start station
14h 26min. 2sec.    12/31/2010 23:49    1/1/2011 14:15  10th & U St NW (31111)

how can I import this data ( which is in CSV file ) in Teradata database to store duration in correct data type, so that I can match it properly with the difference between start and end data?

Please help in correct approach here.

Thanks in advance

Upvotes: 1

Views: 116

Answers (1)

dnoeth
dnoeth

Reputation: 60462

That's quite tricky.

A pure SQL based solution (without features of your ETL-tool) needs to generate data which can be safely casted.

This will modify your duration into a format which can be passed to to_dsinterval by removing unneccessary characters besides HMS (target column should be defined as INTERVAL HOUR(4) TO SECOND(0))

Cast(to_dsinterval('PT'||Upper(OTranslate(duration, ' in.ec', ''))) AS INTERVAL HOUR(4) TO SECOND(0))

Your input timestamps show single digit day/month, which Teradata doesn't support (don't aks why), the RegEx adds those missing zeroes (when the seconds are missing remove the :ss part of the format):

Cast(RegExp_Replace(start_date, '\b([0-9])\b', '0\1') AS TIMESTAMP(0) Format 'mm/dd/yyyyBhh:mi:ss')

Finally pass duration & timestamps as VarChars and apply the Casts during Insert.

Upvotes: 2

Related Questions