CODEWITHSUNDEEP
datedatetimestatateradata
dimitriy
dimitriy

Reputation: 9460

Converting dates and timestamps when inserting data into Teradata

I am block-inserting data from Stata (a statistics package) into a Teradata database. I am having trouble converting dates and timestamps from Stata's native format to Teradata's.

Stata stores dates as days since 01/01/1960, so that 01jan1960 is 0 and 02jan1960 is 1. Timestamps are stored as milliseconds since 01jan1960 00:00:00.000, so that 1000 is 01jan1960 00:00:01. Here are some examples: 

          timestamp   Stata's tstamp  date           Stata's date  
2015-04-13 03:07:08   1744513628000   2015-04-13     20191  
2015-04-14 19:55:43   1744660543000   2015-04-14     20192  
2015-04-08 11:41:39   1744112499000   2015-04-08     20186  
2015-04-15 06:53:34   1744700014000   2015-04-15     20193

I tried 2 approaches. The first involves converting the dates/timestamps to strings in Stata before inserting and then doing something like this once the data is inserted:

ALTER TABLE mytable ALTER date_variable DATETIME

However, I cannot figure out how to do the second part from the documentation I have and after searching the various fora.

The second approach is leaving the dates and timestamps as integers, and then doing some of conversion once the integers are inserted. Perhaps I can also pre-convert dates in Stata to TD's internal format with:

gen td_date = ((year(stata_dt)-1900)*10000 + month(stata_dt)*100 + day(stata_dt))

However, I am not sure what the formula for timestamps would be. I am also not sure how to do the second part (making the integers into dates/timestamps).

Upvotes: 0

Views: 2437

Answers (1)

dnoeth
dnoeth

Reputation: 60462

You can't change the datatype of a column in Teradata from string to date/timestamp.

But when you insert a string into a date/timestamp column there will be an automatic typecast. So simply convert to a string with 'yyyy-mm-dd' or 'yyyy-mm-dd hh:mi:ss' format.

You could also do the conversion during load on Teradata using calculations, but IMHO the 1st solution is preferable:

 -- add the number of days to the start date
DATE '1960-01-01' + stata_dt

-- I use a similar approach for Unix Timestamps starting 1970 :-)
-- split into days and seconds
CAST(DATE '1960-01-01' + (stata_ts / 86400000) AS TIMESTAMP(0))
+ ((stata_ts MOD 86400000 / 1000) * INTERVAL '00:00:01' HOUR TO SECOND)

Upvotes: 1

Related Questions