Chapter 6 – Date Functions

"An inch of time cannot be bought with an inch of gold."

- Chinese Proverb

Current_Date

“Not all who wander are lost.”

–J. R. R. Tolkien

The Current_Date will return today’s date.

Date, Time, and Current_Timestamp Keywords

Above are the keywords you can utilize to get the date, time, or timestamp. These are reserved words that the system will deliver to you when requested.

Dates are stored Internally as INTEGERS from a Formula

/* Example – Tom’s Birthday January 10, 1999 */

990110

/* Example – Tom’s Birthday January 10, 2000 */

1000110

/* Send Tom a birthday present on January 10, 2014 */

1140110

The reason the Smart Calendar works so well is that it stores EVERY date in Teradata as something known as an INTEGERDATE.

Displaying Dates for INTEGERDATE and ANSIDATE

SELECT Date	AS "Date"
,Current_Date	AS Display_Date

Teradata in release V2R3 defaulted to a display of YY/MM/DD. This is called the INTEGERDATE. This can be changed to ANSIDATE, which is YYYY-MM-DD for a specific session or by Default if the DBA changes the DATEFORM in DBS Control. This has nothing to do with how the date is stored internally. It has to do with the display of dates when using any ODBC tool or load utility. Above are some examples.

DATEFORM

Teradata in release V2R3 defaulted to a display of YY/MM/DD. This is called the INTEGERDATE. This can be changed to ANSIDATE, which is YYYY-MM-DD for a specific session or by Default if the DBA changes the DATEFORM in DBS Control. This has nothing to do with how the date is stored internally. It has to do with the display of dates when using any ODBC tool or load utility.

Changing the DATEFORM in Client Utilities such as BTEQ

We logged in to BTEQ and we ran the query SELECT DATE. The date came back as INTEGERDATE. Notice the YY/MM/DD form. INTEGERDATE is the default. Then we ran a command to set the DATEFORM to ANSIDATE. Then we ran the command SELECT DATE again. This time the date came back as ANSIDATE. Notice the YYYY/MM/DD form.

Date, Time, and Timestamp Recap

SELECT Date	AS "Date"
,Current_Date	AS Display_Date

Timestamp Differences

SELECT Current_Timestamp(0)	AS Col1
,Current_Timestamp(6)	AS Col2

A timestamp has the date separated by a space and the time. In our second example we have asked for 6 microseconds

Troubleshooting Timestamp

SELECT Timestamp(0)	AS Col1
, Timestamp(6)	AS Col2

Error

There is NO Timestamp KEYWORD, but only ANSI’s Current_Timestamp!

Add or Subtract Days from a date

Because Dates are stored internally on disk as integers it makes it easy to add days to the calendar. In the query above we are adding 60 days to the Order_Date.

A Summary of Math Operations on Dates

  DATE-DATE = Interval (days between dates)

  DATE+or - Integer = Date

A DATE – DATE is an interval of days between dates. A DATE + or – Integer = Date. Both queries above perform the same function, but the top query uses the internal date functions and the query on the bottom does dates the traditional way.

Age in Days vs. Age in Years

  DATE - DATE = Interval (days between dates)

  DATE + or - Integer = Date

A DATE – DATE is an interval of days between dates. A DATE + or – Integer = Date. Both queries above perform a Date function, but the top query brings back Tom’s age in days and the bottom query brings back Tom’s age in years.

Find What Day of the week you were Born

The above subtraction results in the number of days between the two dates. Then, the MOD 7 divides by 7 to get rid of the number of weeks and results in the remainder. A MOD 7 can only result in values 0 thru 6 (always 1 less than the MOD operator). Since January 1, 1900 ( 101(date) ) is a Monday, Tom was born on a Saturday.

The ADD_MONTHS Command

This is the Add_Months Command. What you can do with it is add a month or many months to your date columns. Can you convert this to one year? There is no ADD_YEAR command!

Using the ADD_MONTHS Command to Add 1 Year

The Add_Months command adds months to any date. Above we used a great technique that would give us 1 year. Can you give me 5 years?

Using the ADD_MONTHS Command to Add 1 Year

The Add_Months command adds months to any date. Above we used a great technique that would give us 1 year. Can you give me 5 years?

Using the ADD_MONTHS Command to Add 5 Years

Above you see a great technique for adding multiple years to a date. Can you now SELECT only the orders in September?

The EXTRACT Command

This is the Extract command. It returns a date part, such as a day, month, or year, from a timestamp value or expression. It can be used in the SELECT list or the WHERE Clause, or the ORDER BY Clause!

Another Version of the EXTRACT Command

Both examples above are equivalent, but beware! The EXTRACT command is a better form because it also works on Day, Year, Hour, Minute and Second. The example on the bottom won’t work with all of them.

EXTRACT from DATES and TIME

Just like the Add_Months, the EXTRACT Command is a Temporal Function or a Time-Based Function.

Why EXTRACT is a Better Form

Most of the time a user needs to extract it will be done on a month or year. That can be done using either technique above. The problem is that if they use the form of the second example only month or year will work. All others will fail. You can’t use the bottom technique with Day, Hour, Minute, Second, Timezone_Hour or Timezone_Minute.

EXTRACT or Math Can Accomplish the Same Thing

The Extract Temporal Function can be used to extract a portion of a date. As you can see, basic arithmetic accomplishes the same thing.

EXTRACT with DATE and TIME Literals

Just like the Add_Months, the EXTRACT Command is a Temporal Function or a Time-Based Function, and the above is designed to show how to use it with literal values.

CAST the Date of January 1, 2011 and the Year 1800

The Convert And Store (CAST) command is used to give columns a different data type temporarily for the life of the query. Notice our dates and how they’re stored.

EXTRACT of the Month on Aggregate Queries

The above SELECT uses the EXTRACT to only display the month and also to control the number of aggregates displayed in the GROUP BY. Notice the Answer Set headers.

The System Calendar

In every Teradata system, there is something known as a System Calendar (or as Teradata calls it Sys_Calendar.Calendar). Get ready for AWESOME!

Using the System Calendar In Its Simplest Form

Tera-Tom was born on a Saturday! It was the first full week of the month, the first full week of the year and it was the first quarter of the year!

How to really use the Sys_Calendar.Calendar

Above is the perfect example of how you can utilize the Sys_Calendar.Calendar to join to any date field and then expand your search options.

Storing Dates Internally

4 bytes store Date_col internally because dates are considered a 4-byte integer.

Storing Time Internally

It takes 6 bytes to store Time_col internally.

Storing TIME With TIME ZONE Internally

It takes 8 bytes to store TIMETIMEZONE_col internally.

Storing Timestamp Internally

It takes 10 bytes to store TIMESTAMP_col internally.

Storing Timestamp with TIME ZONE Internally

It will take 12 bytes to store TIMEZONE_col internally.

Storing Date, Time, and Timestamp with Zone Internally

Each data type increase its internal storage by 2 bytes.

Time Zones

Teradata has the ability to adjust the time and timestamp values to reflect the hours difference between the user's time zone, the system time zone, and the United Kingdom location that was historically called Greenwich Mean Time (GMT). Since the Greenwich observatory has been "decommissioned," the new reference to this same time zone is called Universal Time Coordinate (UTC). Here, the time zones used are represented from the perspective of the system at EST. In the above, it appears to be backward. This is because the time zone is set using the number of hours that the system is from the user.

Setting Time Zones at the System Level

The time zone is established for the system and then each user in each different time zone. The system default time zone is set by the database administrator (DBA) in the DBSControl record. The commands Modify General for settings 16 and 17 control the hours and minutes.

Setting Time Zones at the User Level

A time zone should be established for the system and then every user in each different time zone. The is done with the CREATE USER command. In the CREATE statement if TIME ZONE is set to LOCAL then the user's time zone will default to the same time zone as the system setting. If the TIME ZONE is set to NULL then the user's time zone will default to the system level unless the user overrides that by changing it at the session level when logging on. If the TIME ZONE is set to an explicit value then that is there local time offset by the Universal Time Coordinate (UTC).

Setting Time Zones at the Session Level

A Teradata session can modify the time zone without requiring a logoff and logon.

Seeing your Time Zone

A user’s time zone is now part of the information maintained by Teradata. The settings can be seen in the extended information available in the HELP SESSION request. Teradata converts all TIME and TIMESTAMP values to Universal Time Coordinate (UTC) prior to storing them. All operations, including hashing, collation, and comparisons that act on TIME and TIMESTAMP values are performed using their UTC forms. This will allow users to CAST the information to their local times.

Creating a Sample Table for Time Zone Examples

We have created the table above to run some experiments. Turn the page and we will insert data into it.

Inserting Rows in the Sample Table for Time Zone Examples

We have inserted the data inside our table named Tstamp_Test. It has four rows in it.

Inserting Rows in the Sample Table for Time Zone Examples

We have inserted the data inside our table named Tstamp_Test. It has four rows in it.

Normalizing our Time Zone Table with a CAST

Notice that the Time Zone value was added to or subtracted from the time portion of the time stamp to adjust it to a perspective of the same time zone. As a result, at that moment, it has normalized the different Times Zones in respect to the system time.

As an illustration, when the transaction occurred at 8:12 AM locally in the PST Time Zone, it was already 11:12 AM in EST, the location of the system. The times in the columns have been normalized in respect to the time zone of the system.

Intervals for Date, Time and Timestamp

Interval Chart
Simple Intervals	More involved Intervals
YEAR	DAY TO HOUR
MONTH	DAY TO MINUTE
DAY	DAY TO SECOND
HOUR	HOUR TO MINUTE
MINUTE	HOUR TO SECOND
SECOND	MINUTE TO SECOND

“It’s not the size of the dog in the fight, but the size of the fight in the dog.”

– Archie Griffin

Redshift has added INTERVAL processing, however, it is not ANSI compliant. Intervals are used to perform DATE, TIME and TIMESTAMP arithmetic and conversion.

Interval Data Types and the Bytes to Store Them

Above are the interval data types and the bytes to store them.

Using Intervals

“The afternoon knows what the morning never suspected.”

- Swedish Proverb

To use the ANSI syntax for intervals, the SQL statement must be very specific as to what the data values mean and the format in which they are coded. ANSI standards tend to be lengthier to write and more restrictive as to what is and what is not allowed regarding the values and their use.

Troubleshooting The Basics of a Simple Interval

Our_Date	Leap_Year
01/29/2012	02/29/2012

Error – Invalid Date

The first example works because we added 1 month to the date '2012-01-29' and we got '2012-02-29'. Because this was leap year, there actually is a date of February 29, 2012. The next example is the real point. We have a date of '2011-01-29' and we add 1-month to that, but there is no February 29^th in 2011, so the query fails.

Interval Arithmetic Results

DATE and TIME arithmetic results using intervals:

“Once the game is over, the king and the pawn go back in the same box.”

- Italian Proverb

To use DATE and TIME arithmetic, it is important to keep in mind the results of various operations. The above chart is your Interval guide.

A Date Interval Example

ERROR – Interval Field Overflow

The Error occurred because the default
for all intervals is 2 digits.

The default for all intervals is 2 digits. We received an overflow error because the Actual_Days is 4017. The second example works because we demanded the output to be 4 digits (the maximum for intervals).

A Time Interval Example

ERROR – Interval Field Overflow

The default for all intervals is 2 digits, but notice in the top example, we put in 3 digits for Minute, 4 digits for Second, and 4,4 digits for the Acutal_Seconds4. If we had not, we would have received an overflow error as in the bottom example.

A DATE Interval Example

Date	Two_Year_Ago
06/18/2012	06/18/2010

“I know that you believe that you understand what you think I said, but I am not sure you realize that what you heard is not what I meant.”

-Sign on Pentagon office wall

The above Interval example uses a –'2' to go back in time.

A Complex Time Interval Example using CAST

The following converts an INTERVAL of 6 years
and 2 months to an INTERVAL number of months:

6-02

74

The CAST function (Convert And Store) is the ANSI method for converting data from one type to another. It can also be used to convert one INTERVAL to another INTERVAL representation. Although the CAST is normally used in the SELECT list, it works in the WHERE clause for comparison reasons.

A Complex Time Interval Example using CAST

ERROR

Years & Month

108-04

The top query failed because the INTERVAL result defaults to 2-digits and we have a 3-digit answer for the year portion (108). The bottom query fixes that specifying 3-digits. The biggest advantage in using the INTERVAL processing is that SQL written on another system is now compatible.

The OVERLAPS Command

When working with dates and times, sometimes it is necessary to determine whether two different ranges have common points in time. Redshift provides a Boolean function to make this test for you. It is called OVERLAPS; it evaluates true if multiple points are in common, otherwise it returns a false. The literal is returned because both date ranges have from October 15 through November 30 in common.

An OVERLAPS Example that Returns No Rows

“I don’t know who my grandfather was. I am more interested in who
his grandson will become.”

– Abraham Lincoln

The above SELECT example tests two literal dates and uses the OVERLAPS to determine whether or not to display the character literal. The literal was not selected because the ranges do not overlap. So, the common single date of November 30 does not constitute an overlap. When dates are used, 2 days must be involved, and when time is used, 2 seconds must be contained in both ranges.

The OVERLAPS Command using TIME

The above SELECT example tests two literal times and uses the OVERLAPS to determine whether or not to display the character literal. This is a tricky example, and it is shown to prove a point. At first glance, it appears as if this answer is incorrect because 02:01:00 looks like it starts 1 second after the first range ends. However, the system works on a 24-hour clock when a date and time (timestamp) is not used together. Therefore, the system considers the earlier time of 2AM time as the start and the later time of 8 AM as the end of the range. Therefore, not only do they overlap, the second range is entirely contained in the first range.

The OVERLAPS Command using a NULL Value