DATE, TIME, and TIMESTAMP Teradata

Teradata has a date function and a time function built into the database and the ability to request this data from the system. In the early releases, 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

how Teradata converts a date to the YYYMMDD date format

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

stored data for the date January 1, 1999 is converted

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 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):

next conversion shows the data stored for January 1, 2000

Additionally, since the date is stored as an integer and an integer is a signed value, dates prior to the base year of 1900 can also be stored. The same formula applies for the date conversion regardless of which century. However, since dates prior to 1900, like 1800 are smaller values, the result of the subtraction is a negative number.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics