Tag Archive

Tag Archives for " calendar "

Some useful Teradata Date Calculations

Teradata Date Calculations

Teradata saves the date internally as INTEGER.

Dates after 01.01.1900 can be calculated with the following formula:

((year- 1900) * 10000) + (month * 100) + day

Calculation using the above method is efficient and requires only a few resources. However, I would avoid it because it is difficult to read.

Teradata stores date internally as INTEGER values. Dates after 1900-01-01 can be calculated with the following formula:
((year- 1900) * 10000) + (month * 100) + day

Nevertheless, personally, i would avoid above calculation as it is difficult to read and wrong for dates before 1900.

Here are some more useful Teradata Date Calculations:

First day of the month:
CURRENT_DATE – (EXTRACT(DAY FROM CURRENT_DATE) – 1)

Last day of the previous month:
CURRENT_DATE – EXTRACT(DAY FROM CURRENT_DATE)

Last day of the month, n months ago:
ADD_MONTHS(CURRENT_DATE – (EXTRACT(DAY FROM CURRENT_DATE) – 1), -n + 1) – 1

First day of the month, n months ago:
ADD_MONTHS(CURRENT_DATE – (EXTRACT(DAY FROM CURRENT_DATE) – 1), -n)

First day of the year:
ADD_MONTHS(CURRENT_DATE – (EXTRACT(DAY FROM CURRENT_DATE) – 1), -EXTRACT(MONTH FROM CURRENT_DATE) + 1)

Day of week (1-7; 1 = monday, 2 = tuesday, …)
(CURRENT_DATE – DATE ‘0001-01-01’) MOD 7 + 1

All the above calculations use the EXTRACT function.

In any case, you should avoid substrings for date calculations.

Below are three alternative ways to calculate the first day of a month for any date (based on a large test table).

As you can see, the CPU times used are very different. Although the calculations with INTEGER values (the second table row) uses the fewest resources, you should avoid them because of their poor readability.

CPU Time Statement
114,40 thedate – EXTRACT(DAY FROM thedate ) + 1
74,10 thedate  / 100 * 100 + 1 (DATE)
166,50 thedate  – SUBSTRING(thedate  FROM 1 FOR 8) || ’01’ (DATE)

 

1

Use Teradata Recursions to build a Calendar

 

teradata recursion

At times we need to join records in one table with a specified range of dates in a second table. One application which comes to my mind in the first place is to fake a snapshot history table (containing a snapshot date) based on a change history table (containing timelines limited by a start date and an end date). Here is one example:

CREATE TABLE Snapshot_History
(
PK INTEGER NOT NULL,
ATTRIB CHAR(10),
SNAPSHOT_DATE DATE
);

CREATE TABLE Change_History
(
PK INTEGER NOT NULL,
ATTRIB CHAR(10),
START_DATE DATE,
END_DATE DATE
);

The solution would probably look like this:

SELECT  t01.PK, t01.ATTRIB, t02, table?CALENDAR_DATE AS SNAPSHOT_DATE
FROM
Change_History t01
INNER JOIN
SYS_CALENDAR.CALENDAR t02
WHERE
t02.CALENDAR_DATE BETWEEN START_DATE AND END_DATE;

But what should you do if you do not have permissions to read from the SYS_CALENDAR.CAtable? You probably will have to create your calendar table, if you are not in the lucky situation that somewhere in your Teradata Data Warehouse exists already one as standard.

I came to hate building my calendar tables for several reasons, but principal reason is that each Data Warehouse I have seen until now ended up having calendar tables widely scattered across all subject areas. To make things even worse, this tables will show up in different shapes and naming conventions, making your life even harder.

Hence I started to research for a solution which should not need any real table at all. Further, from a performance point of view, I wanted to avoid the joining of tens of thousands of calendar dates despite only needing a few dates.

From my experience, you can end up with an awful performing query if Teradata is not able to apply early in the execution plan some partition elimination on the unneeded dates (This is especially valid for any Teradata Release before 13).

This was the moment when I started to play around with recursions and here is the result (you may probably want to transform the below SQL statement into a recursive view). My solution does not need any real table, and it only creates the range of dates you want:

WITH RECURSIVE DateRanges(CALENDAR_DATE) AS
(
SELECT DATE’2014-04-01′ AS CALENDAR_DATE
FROM
( SELECT * FROM (SELECT NULL AS X) X) DUMMYTABLE
UNION ALL
SELECT
CALENDAR_DATE + INTERVAL ‘1’ DAY
FROM
DateRanges
WHERE
CALENDAR_DATE < CURRENT_DATE
)
SELECT *
FROM DateRanges;

I am sure; you will find many other useful applications by adapting above SQL statement. I could, for example, imagine to change it in a way to deliver only the last day of each month for a certain time range.

I leave it to you as an easy task to post any further solutions.

Conclusion:

Teradata recursions can be helpful if your requirement is to create any ranks. Besides, the “dummy table creation” statement “SELECT * FROM (SELECT NULL AS X) X” permits to achieve this without any physical tables.

>