"An inch of time cannot be bought with an inch of gold."
- Chinese Proverb
This example uses the Current_Date to return the current date.
SELECT Current_Date as ANSI_Date;
ANSI_Date
------------
2014-10-04
“Not all who wander are lost.”
–J. R. R. Tolkien
The Current_Date will return today’s date.
SELECT Date |
AS "Date" |
,Current_Date |
AS ANSI_Date |
,Time |
AS "Time" |
,Current_Time |
AS ANSI_Time |
,Current_Timestamp(6) |
AS ANSI_Timestamp |
There’s no keyword Timestamp,
but only ANSI’s
Current_Timestamp
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.
/* 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.
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 Controls the default display of dates.
DATEFORM display choices are either INTEGERDATE or ANSIDATE.
INTEGERDATE is (YY/MM/DD) and ANSIDATE is (YYYY-MM-DD).
DATEFORM is the expected format for import/export of dates in Load Utilities.
Can be over-ridden by USER or within a Session at any time.
The Default can be changed by the DBA through the DATEFORM in DBSControl.
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.
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.
SELECT Date |
AS "Date" |
,Current_Date |
AS Display_Date |
Dates are converted to an integer through a formula before being stored.
Dates are displayed by default as INTEGERDATE YY/MM/DD.
The DBA can set up the system to display as ANSIDATE YYYY-MM-DD.
Keywords Date or Current_Date will return the date automatically.
Time, Current_Time and Current_Timestamp are keywords to return time.
The Nexus Query Chameleon displays dates as MM/DD/YYYY.
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
SELECT Timestamp(0) |
AS Col1 |
, Timestamp(6) |
AS Col2 |
Error
Things to remember
There is Date and Current_Date (both work).
There is Time and Current_Time (both work).
There is NO Timestamp, but only Current_Timestamp!
There is NO Timestamp KEYWORD, but only ANSI’s Current_Timestamp!
SELECT Order_Date
,Order_Date + 60 as "Due Date"
,Order_Total
,"Due date" -10 as Discount
,Order_Total *.98 (FORMAT '$$$$,$$$.99')
FROM Order_Table
ORDER BY 1 ;
When you add or subtract from a Date you are adding/subtracting Days
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.
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.
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.
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.
SELECT Order_Date
,Add_Months (Order_Date,2) as "Due Date2"
,Order_Total
FROM Order_Table ORDER BY 1 ;
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!
SELECT Order_Date
,Add_Months (Order_Date,12) as "Due Date12"
,Order_Total
FROM Order_Table ORDER BY 1 ;
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?
SELECT Order_Date
,Add_Months (Order_Date,12) as "Due Date12"
,Order_Total
FROM Order_Table ORDER BY 1 ;
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?
SELECT Order_Date
,Add_Months (Order_Date,12 * 5) as "Due Date"
,Order_Total
FROM Order_Table
ORDER BY 1 ;
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 extracts portions of
Date, Time, and Timestamp
SELECT Order_Date
,Add_Months (Order_Date,12 * 5) as "Due Date"
,Order_Total
FROM Order_Table
WHERE EXTRACT(Month from Order_Date) = 9 ;
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!
The EXTRACT command extracts portions of
Date, Time, and Timestamp
SELECT Order_Date
,Add_Months (Order_Date,12 * 5) as "Due Date"
,Order_Total
FROM Order_Table
WHERE EXTRACT(Month from Order_Date) = 9 ;
Below is another version of the Extract Command
SELECT Order_Date
,Add_Months (Order_Date,12 * 5) as "Due Date"
,Order_Total
FROM Order_Table
WHERE Month (Order_Date) = 9 ;
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.
SELECT Current_Date
,EXTRACT(Year from Current_Date) as Yr
,EXTRACT(Month from Current_Date) as Mo
,EXTRACT(Day from Current_Date) as Da
,Current_Time
,EXTRACT(Hour from Current_Time) as Hr
,EXTRACT(Minute from Current_Time) as Mn
,EXTRACT(Second from Current_Time) as Sc
,EXTRACT(TIMEZONE_HOUR from Current_Time) as Th
,EXTRACT(TimeZONE_MINUTE from Current_Time) as Tm ;
Just like the Add_Months, the EXTRACT Command is a Temporal Function or a Time-Based Function.
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.
The Extract Temporal Function can be used to extract a portion of a date. As you can see, basic arithmetic accomplishes the same thing.
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.
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.
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.
Teradata systems have a table called Caldates.
Caldates has only one column in it called Cdates.
Cdates is a date column that contains a row for each date
starting from January 1, 1900 to December 31, 2100.
No user can access the table Caldates directly.
Views in the Sys_Calendar database accesses Caldates.
A view called Calendar is how USERS work with the calendar.
Users use Sys_Calendar.Calendar for advanced dates.
In every Teradata system, there is something known as a System Calendar (or as Teradata calls it Sys_Calendar.Calendar). Get ready for AWESOME!
Calendar_Date = 01/10/1959'
day_of_week = 7 (Sunday = 1)
day_of_month = 10
day_of_year = 10
day_of_Calendar = 21559 (since Jan 1, 1900)
weekday_of_month = 2
week_of_month = 1 (0 for partial week not starting with Sunday)
week_of_year = 1
week_of_calendar = 3079 (since Jan 1, 1900)
month_of_quarter = 1
month_of_year = 1
month_of_calendar = 709 (since Jan 1, 1900)
quarter_of_year = 1
quarter_of_calender = 237 (since Jan 1, 1900)
year_of_calendar = 1959
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!
We just brought back all Orders from the Order_Table that
were purchased on a Friday in the 4th Quarter, during the 1st
partial week. This means no Sunday seen yet for that month.
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.
CREATE SET TABLE TIMEZONE_table ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL ,
CHECKSUM = DEFAULT
(Date_col |
Date, |
TIME_col |
TIME(6), |
TIMETIMEZONE_col |
TIME(6) WITH TIME ZONE, |
TIMESTAMP_col |
TIMESTAMP(6), |
TIMEZONE_col TIMESTAMP(6) WITH TIME ZONE) |
UNIQUE PRIMARY INDEX (TIMEZONE_col) ;
DATE ‘1999-01-10’ is stored as 990110
DATE ‘2000-01-10’ is stored as 1000110
4 bytes store Date_col internally because dates are considered a 4-byte integer.
CREATE SET TABLE TIMEZONE_table ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(Date_col |
Date, |
TIME_col |
TIME(6), |
TIMETIMEZONE_col |
TIME(6) WITH TIME ZONE, |
TIMESTAMP_col |
TIMESTAMP(6), |
TIMEZONE_col TIMESTAMP(6) WITH TIME ZONE) |
UNIQUE PRIMARY INDEX (TIMEZONE_col) ;
It takes 6 bytes to store Time_col internally.
CREATE SET TABLE TIMEZONE_table ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL ,
CHECKSUM = DEFAULT
(Date_col |
Date, |
TIME_col |
TIME(6), |
TIMETIMEZONE_col |
TIME(6) WITH TIME ZONE, |
TIMESTAMP_col |
TIMESTAMP(6), |
TIMEZONE_col TIMESTAMP(6) WITH TIME ZONE) |
UNIQUE PRIMARY INDEX (TIMEZONE_col) ;
Time(n) WITH ZONE stored as HHMMSS.nnnnnn+HHMM
TIME(6) WITH TIME ZONE
It takes 8 bytes to store TIMETIMEZONE_col internally.
CREATE SET TABLE TIMEZONE_table ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL ,
CHECKSUM = DEFAULT
(Date_col |
Date, |
TIME_col |
TIME(6), |
TIMETIMEZONE_col |
TIME(6) WITH TIME ZONE, |
TIMESTAMP_col |
TIMESTAMP(6), |
TIMEZONE_col TIMESTAMP(6) WITH TIME ZONE) |
UNIQUE PRIMARY INDEX (TIMEZONE_col) ;
TimeStamp(n) stored as YYMMDDHHMMSS.nnnnnn
It takes 10 bytes to store TIMESTAMP_col internally.
CREATE SET TABLE TIMEZONE_table ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL ,
CHECKSUM = DEFAULT
(Date_col |
Date, |
TIME_col |
TIME(6), |
TIMETIMEZONE_col |
TIME(6) WITH TIME ZONE, |
TIMESTAMP_col |
TIMESTAMP(6), |
TIMEZONE_col TIMESTAMP(6) WITH TIME ZONE) |
UNIQUE PRIMARY INDEX ( TIMEZONE_col );
TimeStamp(n) With Zone stored as YYMMDDHHMMSS.nnnnnn+HHMM
It will take 12 bytes to store TIMEZONE_col internally.
CREATE SET TABLE TIMEZONE_table ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL ,
CHECKSUM = DEFAULT
(Date_col |
Date, |
TIME_col |
TIME(6), |
TIMETIMEZONE_col |
TIME(6) WITH TIME ZONE, |
TIMESTAMP_col |
TIMESTAMP(6), |
TIMEZONE_col TIMESTAMP(6) WITH TIME ZONE) |
UNIQUE PRIMARY INDEX (TIMEZONE_col) ;
Each data type increase its internal storage by 2 bytes.
Time zones are set either at the system level (DBS
Control), the user level (when user is created or
modified), or at the session level as an override.
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.
A Time Zone should be established for the system
and every user in each different time zone.
Setting the system default time zone is done
by the DBA in the DBSControl record:
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.
A Time Zone should be established for the system and every user in each different time zone.
Setting a User’s time zone requires choosing
either LOCAL, NULL, or an explicit value:
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 a Session’s time zone
A Teradata session can modify the time zone without requiring a logoff and logon.
Help Session ;
Not all output is displayed
above from the HELP Session
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.
CREATE TABLE Tstamp_Test
(
TS_Zone CHAR(3)
,TS_with_Zone TIMESTAMP(6) WITH TIME ZONE
,TS_Without_Zone TIMESTAMP(6)
)
UNIQUE PRIMARY INDEX (TS_Zone);
We have created the table above to run some experiments. Turn the page and we will insert data into it.
CREATE TABLE Tstamp_Test
( TS_Zone CHAR(3)
,TS_with_Zone TIMESTAMP(6) WITH TIME ZONE
,TS_Without_Zone TIMESTAMP(6)
) UNIQUE PRIMARY INDEX ( TS_Zone );
INSERT INTO Tstamp_Test ('EST', timestamp '2000-10-01 08:12:00',
timestamp '2000-10-01 08:12:00'),
SET TIME ZONE INTERVAL '05:00' HOUR TO MINUTE ;
INSERT INTO Tstamp_Test ('UTC', timestamp '2000-10-01 08:12:00',
timestamp '2000-10-01 08:12:00'),
SET TIME ZONE INTERVAL –'03:00' HOUR TO MINUTE ;
INSERT INTO Tstamp_Test ('PST', timestamp '2000-10-01 08:12:00',
timestamp '2000-10-01 08:12:00'),
SET TIME ZONE INTERVAL –'11:00' HOUR TO MINUTE ;
INSERT INTO Tstamp_Test ('HKT', timestamp '2000-10-01 08:12:00',
timestamp '2000-10-01 08:12:00'),
We have inserted the data inside our table named Tstamp_Test. It has four rows in it.
We have inserted the data inside our table named Tstamp_Test. It has four rows in it.
SELECT TS_Zone, TS_with_Zone
,CAST(TS_with_Zone AS TIMESTAMP(6))
AS T_Normal
FROM Tstamp_Test ORDER BY 3 ;
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.
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.
Above are the interval data types and the bytes to store them.
SELECT Current_Date as Our_Date
,Current_Date + Interval '1' Day as Plus_1_Day
,Current_Date + Interval '3' Month as Plus_3_Months
,Current_Date + Interval '5' Yearas Plus_5_Years
“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.
SELECT Date '2012-01-29' as Our_Date
,Date '2012-01-29' + INTERVAL '1' Month as Leap_Year
Our_Date |
Leap_Year |
01/29/2012 |
02/29/2012 |
SELECT Date '2011-01-29' as Our_Date
,Date '2011-01-29' + INTERVAL '1' Month as Leap_Year
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 29th in 2011, so the query fails.
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.
SELECT (DATE '1999-10-01' - DATE '1988-10-01') DAY AS Actual_Days ;
ERROR – Interval Field Overflow
The Error occurred because the default
for all intervals is 2 digits.
Actual_Days
4017
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).
SELECT
(TIME '12:45:01' - TIME '10:10:01') HOUR |
AS Actual_Hours |
,(TIME '12:45:01' - TIME '10:10:01') MINUTE(3) |
AS Actual_Minutes |
,(TIME '12:45:01' - TIME '10:10:01') SECOND(4) |
AS Actual_Seconds |
,(TIME '12:45:01' - TIME '10:10:01') SECOND(4,4) |
AS Actual_Seconds4 |
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.
SELECT
Current_Date,
INTERVAL -'2' YEAR + CURRENT_DATE as Two_years_Ago;
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.
Below is the syntax for using the CAST with a date:
SELECT CAST (<interval> AS INTERVAL <interval> )
FROM <table-name> ;
The following converts an INTERVAL of 6 years
and 2 months to an INTERVAL number of months:
SELECT
CAST( (INTERVAL '6-02' YEAR TO MONTH) AS INTERVAL MONTH )
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.
This request attempts to convert 1300 months to show
the number of years and months. Why does it fail?
SELECT
CAST(INTERVAL '1300' MONTH AS INTERVAL YEAR TO MONTH)
AS "Years & Months";
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.
Compatibility: Teradata Extension
The syntax of the OVERLAPS is:
SELECT <literal>
WHERE (<start-date-time>, <end-date-time>) OVERLAPS
(<start-date-time>, <end-date-time>) ;
SELECT 'The Dates Overlap' as Dater
WHERE (DATE '2001-01-01', DATE '2001-11-30') OVERLAPS
(DATE '2001-10-15', DATE '2001-12-31'),
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.
SELECT 'The dates overlap' AS OverlapAnswer
WHERE (DATE '2001-01-01', DATE '2001-11-30') OVERLAPS
(DATE '2001-11-30', DATE '2001-12-31') ;
“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.
SELECT 'The Times Overlap' As DoThey
WHERE (TIME '08:00:00', TIME '02:00:00') OVERLAPS
(TIME '02:01:00', TIME '04:15:00') ;
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.
SELECT 'The Times Overlap' As Time1
WHERE
(TIME '10:00:00', NULL) OVERLAPS (TIME '01:01:00', TIME '04:15:00')
The above SELECT example tests two literal dates and uses the OVERLAPS to determine whether or not to display the character literal:
When using the OVERLAPS function, there are a couple of situations to keep in mind:
1. A single point in time, i.e. the same date, does not constitute an overlap. There must be at least one second of time in common for TIME or one day when using DATE.
2. Using a NULL as one of the parameters, the other DATE or TIME constitutes a single point in time versus a range.