Chapter 8 – Time and Date
“To have everything is to possess nothing.”
-Buddha
Date, Time, and Timestamp Keywords
SELECT Current_Date AS ANSI_Date
,Current_Time AS ANSI_Time
,Current_Timestamp AS ANSI_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. Aster has a variety of date, time, and timestamp functions. Many of these are ANSI standard, and there are also some extensions that are unique to Aster.
Along with the functions, there are corresponding DATE, TIME, and TIMESTAMP data types that allow these references to be stored in tables. The SQL above also works on Teradata.
Add or Subtract Days from a date
SELECT Order_Date + 60 AS "Due Date"
,Order_Date
,to_char(Order_total,'$99,999.99') as Total_Due
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. Also, notice the to_char command which will format the amount.
The to_char command
SELECT Order_Date
,Order_Date + 60 as "Due Date"
,to_char(Order_Total, '$99,999.99') As Order_Total
,Order_Date + 50 as "Discount Date"
,to_char(Order_Total *.98, '$99,999.99') as Discounted
FROM Order_Table
ORDER BY 1 ;
The to_char command will take a value and convert it to a character string.
A Summary of Math Operations on Dates
DATE – DATE = Interval (days between dates)
DATE + or - Integer = Date
Let’s find the number of days Tera-Tom has been alive since his last birthday.
SELECT (date '2013-01-10') - (date '1959-01-10') AS "Toms Age In Days";
Tera-Toms Age In Days
19724
A DATE – DATE is an interval of days between dates. A DATE + or – Integer = Date. The query above uses the dates the traditional way to deliver the Interval. The SQL above also works on Teradata.
Using a Math Operation to find your Age in Years
DATE – DATE = Interval (days between dates)
DATE + or - Integer = Date
Let’s find the number of days Tera-Tom has been alive since his last birthday.
SELECT (date '2013-01-10') - (date '1959-01-10') AS "Toms Age In Days";
Tera-Toms Age In Days
19724
Let’s find the number of years Tera-Tom has been alive since his last birthday.
SELECT ((date '2013-01-10') - (date '1959-01-10'))/365 "Toms Age In Years";
Tera-Toms Age In Years
54
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 date functions to find “Days”, and the query on the bottom finds “Years”. The SQL above also works on Teradata.
Find What Day of the week you were Born
Let’s find the actual day of the week Tera-Tom was born
SELECT 'Tera-Tom born day ' || mod(((date '1959-01-10') - (date '1900-01-01')),7) AS " "
Tera-Tom was born on day 5
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 SQL above also works on Teradata.
Date Related Functions
TO_CHAR - Receives a date and, based on the template characters, displays portions of the date.
TO_DATE - Receives a character string and converts it to a date based on the template provided.
SELECT to_char(Order_Date, 'Day - dddd, Mon yy')
,Order_Date +365 "Year Later Date"
,to_char(Order_Total,'$99,999.99') Order_Total
,to_date('Dec 31, 2005','mon dd, yyyy') as "Due Date"
,date '2005-11-30' as "Discount Date"
FROM Order_Table ORDER BY 2 ;
Answer Set
The EXTRACT Command
SELECT Order_Date
,Order_Total
FROM Order_Table
WHERE EXTRACT(Month from Order_Date) = 09
ORDER BY 1 ;
The EXTRACT command extracts portions of Date, Time, and Timestamp.
This is the Extract command. It extracts a portion of the date, and it can be used in the SELECT list or the WHERE Clause, or the ORDER BY Clause! The SQL above also works on Teradata.
EXTRACT from DATES and TIME
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
Answer Set
The EXTRACT Command is a Temporal Function or a Time-Based Function. The above SQL also works with Teradata.
EXTRACT with DATE and TIME Literals
SELECT EXTRACT(YEAR FROM DATE '2000-10-01') AS Yr
,EXTRACT(MONTH FROM DATE '2000-10-01') AS Mth
,EXTRACT(DAY FROM DATE '2000-10-01') AS Da
,EXTRACT(HOUR FROM TIME '10:01:30') AS Hr
,EXTRACT(MINUTE FROM TIME '10:01:30') AS Min
,EXTRACT(SECOND FROM TIME '10:01:30') AS Sec
,EXTRACT(MONTH FROM current_timestamp) AS ts_Mth
,EXTRACT(SECOND FROM current_timestamp) AS ts_Sec2 ;
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 SQL above also works on Teradata.
EXTRACT of the Month on Aggregate Queries
SELECT EXTRACT(Month FROM Order_date)
,COUNT(*) AS Nbr_of_rows
,AVG(Order_Total)
FROM Order_Table
GROUP BY 1
ORDER BY 1 ;
Answer Set
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 SQL above also works on Teradata.
A Side Title example with Reserved Words as an Alias
SELECT 'Due Date:’ AS " " /* title as no title */
,EXTRACT(Month FROM Order_date+64) AS "Month"
,EXTRACT(Day FROM Order_date+64) AS "Day"
,EXTRACT(Year FROM Order_date+64) AS "Year"
,to_char(Order_Date, 'Mon-dd, yyyy') as "To_Char"
,Order_Total
FROM Order_Table
ORDER BY 2,3 ;
The next SELECT operation uses entirely ANSI compliant code to show the month and day of the payment due date in 2 months and 4 days. Notice it uses double quotes to allow reserved words as alias names.
Implied Extract of Day, Month and Year
SELECT to_char(<date-data>,’DD’) /* extracts the day */
,to_char(<date-data>,’MM’) /* extracts the month */
,to_char(<date-data>,’YYYY’) /* extracts the year */
FROM <table-name> ;
The following SELECT uses math to extract the three portions of Tom’s literal birthday:
SELECT to_char(date '2013-01-10','DD') AS Day_portion
,to_char(date '2013-01-10','MM') AS Month_portion
,to_char(date '2013-01-10','YYYY') AS Year_portion ;
It was mentioned earlier that Aster stores a date as an integer and therefore allows math operations to be performed on a date. Although the EXTRACT works great and it is ANSI compliant, it is a function. Therefore, it must be executed and the parameters passed to it to identify the desired portion as data. Then, it must pass back the answer. As a result, there is additional overhead processing required to use it.
DATE_PART Function
DATE_PART(‘<text’,<date-time-timestamp>)
Where <text> can be: ‘YEAR’, ‘MONTH’, ‘DAY’ for a date or a time stamp and (‘HOUR’, ‘MINUTE’, ‘SECOND’ for time and time stamp.
The following SELECT will show DATE_PART with a Date, Time, and Timestamp.
SELECT CURRENT_DATE
,DATE_PART('MONTH',CURRENT_DATE) as Mo
,CURRENT_TIME
,DATE_PART('MINUTE',CURRENT_TIME) Min
,CURRENT_TIMESTAMP
,DATE_PART('SECOND',CURRENT_TIMESTAMP) as Sec ;
The DATE_PART function works exactly like EXTRACT. Although the name contains DATE, it also works with time and time stamp data.
DATE_TRUNC Function
DATE_TRUNC(‘<text’,<date>)
Where <text> can be: ‘YEAR’, ‘MONTH’, ‘DAY’ for a date or a time stamp and (‘HOUR’, ‘MINUTE’, ‘SECOND’ for time and time stamp. Although DAY and SECOND are allowed, they have no impact on the output data, see below.
SELECT CURRENT_TIMESTAMP
,DATE_TRUNC('YEAR',CURRENT_TIMESTAMP) AS Yr_Trunc
,DATE_TRUNC('MONTH',CURRENT_TIMESTAMP) AS Mo_Trunc
,DATE_TRUNC('DAY',CURRENT_TIMESTAMP) AS Da_Trunc;
The DATE_TRUNC function has an interesting capability in that it truncates the portion of a date back to the first. Notice that the year portion becomes January 1, the month portion becomes October 1 and the day portion does not change. However, the entire time portion is set back to 00:00:00. When DATE data is used, the time portion is set to 00:00:00 just like in the above.
DATE_TRUNC Function using TIME
DATE_TRUNC('<text',<date>)
Where <text> can be: 'YEAR', 'MONTH', 'DAY' for a date or a time stamp and ('HOUR', 'MINUTE', 'SECOND' for time and time stamp. Although DAY and SECOND are allowed, they have no impact on the output data, see below.
SELECT CURRENT_TIMESTAMP
,DATE_TRUNC('HOUR',CURRENT_TIMESTAMP) hr_trunc
,DATE_TRUNC('MINUTE', CURRENT_TIMESTAMP) min_trunc
,DATE_TRUNC('SECOND',CURRENT_TIMESTAMP) sec_trunc;
Notice that the hour portion becomes 14:00:00, the minute portion becomes 14:06:00 and the second and date portions do not change. If TIME was used, there would be no DATE portion as in a TIME STAMP.
Aster NOW() Function
The timestamp can also be obtained using the NOW() function:
SELECT NOW() ;
SELECT NOW() ;
Answer Set
now()
2013-10-19 14:14:16.634703-04
Aster allows you to see the date and time with the NOW() function. The next time someone asks for the time, tell them NOW.