Saturday, 7 March 2015

Teradata DATE and TIMESTAMP- Some Facts

Teradata has a date function and a time function built into the database and the ability to request this data from the system.

DATE was a valid data type for storing the combination of year, month and day, but TIME was not. Now, TIME and TIMESTAMP are both valid data types that can be defined and stored within a table.

The Teradata RDBMS stores the date in YYYMMDD format on disk. The YYY is an offset value from the base year of 1900. The MM is the month value from 1 to 12 and the DD is the day of the month.

Using this format, the database can currently work with dates beyond the year 3000. So, it appears that Teradata is Y3K compliant.

Teradata always stores a date as a numeric INTEGER value.

The following calculation demonstrates how Teradata converts a date to the YYYMMDD date format, for storage of January 1, 1999:


The stored data for the date January 1, 1999 is converted to:

YEAR: (1999-1900) * 10000 = 0990000 ->YEAR
MONTH = 01*100= +0100 -> MONTH
DATE=01  +01 -> DAY PORTION

SO DATE IS -> 0990101 ---STORED INTO DISK


Although years prior to 2000 look fairly "normal" with an implied year for the 20th Century, after 2000 years do not look like the normal concept of a year (100). Fortunately, Teradata automatically does all the conversion and makes it transparent to the user.

The remainder of this book will provide SQL examples using both a numeric date as well as the character formats of ‘YY/MM/DD’ and ‘YYYY-MM-DD’.

The next conversion shows the data stored for January 1, 2000 (notice that YYY=100 or 100 years from 1900):


YEAR=(2000-1900) * 10000 = 1000000
MONTH=01*100) -> +0100
DAY=01    -> +01
SO DATE IS 1000101-> STORED ON DISK


No comments:

Post a Comment