"An inch of time cannot be bought with an inch of gold."
- Chinese Proverb
The Current_Date will return today's date.
TIMEOFDAY() returns a VARCHAR data type and specifies
the weekday, date, and time.
SELECT TIMEOFDAY() ;
timeofday
------------
Mon Oct 6 22:53:50.333525 2014 UTC
“Always remember that you are unique just like everyone else.”
– Anonymous
The TIMEOFDAY function returns the weekday, date and the time.
This example uses the SYSDATE function
to return the full timestamp for the current date.
This example uses the SYSDATE function
inside the TRUNC function to return the
current date without the time included.
SELECT TRUNC(SYSDATE) ;
trunc
------------
2014-10-04
The SYSDATE function returns the current date and time according to the system clock on the leader node. The functions CURRENT_DATE and TRUNC(SYSDATE) produce the same results.
This example uses the GETDATE() function
to return the full timestamp for the current date.
This example uses the GETDATE() function
inside the TRUNC function to return the
current date without the time included.
SELECT TRUNC(GETDATE());
trunc
------------
2014-10-04
GETDATE returns a TIMESTAMP. The parentheses are required.
SELECT Order_Date + 60 AS "Due Date"
,Order_Date
,to_char(Order_total,'$99,999.99') as Total_Due
FROMOrder_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.
SELECT Order_Date
,Add_Months (Order_Date,2) as "Due Date"
,Order_Total
FROM Order_Table ORDER BY 1 ;
The ADD_MONTHS function adds a specified number of months to a date or timestamp value. If date is the last day of the month, or if the resulting month is shorter, the function returns the last day of the month in the result. For other dates, the result contains the same day number as the date expression. A positive or negative integer or any value that implicitly converts to an integer. You can even use a negative number to subtract months from dates. The DATEADD function provides similar functionality.
SELECT Order_Date
,TRUNC(Add_Months (Order_Date,2)) as "Due Date"
,Order_Total
FROM Order_Table ORDER BY 1 ;
Above, we used the TRUNC command to get rid of the time (00:00:00) on the returning answer set. The ADD_MONTHS function adds a specified number of months to a date or timestamp value. If date is the last day of the month, or if the resulting month is shorter, the function returns the last day of the month in the result. For other dates, the result contains the same day number as the date expression. A positive or negative integer or any value that implicitly converts to an integer. You can even use a negative number to subtract months from dates. The DATEADD function provides similar functionality.
There is no Add_Year command, so put in 12 months for 1-year
In this example, we multiplied 12 months times 5 for a total of 5 years!
The Add_Months command adds months to any date. Above, we used a great technique that would give us 1-year. We then showed an even better technique to get 5-years.
DATEADD: If there are fewer days in the date you are adding to than in the result month, the result will be the corresponding day of the result month, not the last day of that month. For example, April 30th + 1 month is May 30th:
SELECT DATEADD (month,1,'2014-04-30');
DATEADD
----------------------
2014-05-30 00:00:00
ADD_MONTHS: If the date you are adding to is the last day of the month, the result is always the last day of the result month, regardless of the length of the month. For example, April 30th + 1 month is May 31st:
SELECT ADD_Months ('2014-04-30',1);
ADD_Months
-------------------------
2014-05-31 00:00:00
The DATEADD and ADD_MONTHS functions handle dates that fall at the ends of months differently.
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
ORDER BY 1 ;
“You miss 100 percent of the shots you never take.”
– Wayne Gretzky
This is the Extract command. It returns a date part, such as a day, month, or year, from a timestamp value or expression.
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
Just like the Add_Months, the EXTRACT Command is a Temporal Function or a Time-Based Function.
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 "Day"
,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_Part ;
Answer Set
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.
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.
This function uses a datepart (day, week, month etc.) and two target expressions. This function returns the difference between the two expressions. The expressions must be date or timestamp expressions and they must both contain the specified datepart. If the second date is later than the first date, the result is positive. If the second date is earlier than the first date, the result is negative.
This function uses a datepart (day, week, month etc.) and two target expressions. This function returns the difference between the two expressions. The expressions must be date or timestamp expressions, and they must both contain the specified datepart. If the second date is later than the first date, the result is positive. If the second date is earlier than the first date, the result is negative.
The specific part of the date value (year, month, or day, for example) that the datepart function operates on. The expression must be a date or timestamp expression that contains the specified date_part.
“Speak in a moment of anger and you’ll deliver the greatest speech you’ll ever regret.”
– Anonymous
The specific part of the date value (year, month, or day, for example) that the DATE _PART function operates on. The expression must be a date or timestamp expression that contains the specified DATE_PART. Notice that the default column name for the DATE_PART function is PGDATE_PART.
Below are dateparts for date or timestamp functions. The following table identifies the datepart and timepart names and abbreviations that are accepted as arguments to the following functions:
Above are the functions for datepart or timepart, their parts, and the acceptable abbreviations.
SELECTOrder_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
FROMOrder_Table
ORDER BY 1 ;
The to_char command will take a value and convert it to a character string.
Function Name |
Conversion Operation |
to_number() |
Character to numeric |
to_date() |
Character or timestamp to date |
to_timestamp() |
Character to timestamp |
to_char() |
Numeric, date or timestamp to character |
The following shows the syntax for using these functions:
,to_number(<character-data>,'<template>')
,to_date(<character-data>,'<template>')
,to_timestamp(<character-data>,'<template>')
,to_char(<numeric-data>)
,to_date(<character-data>,'<template>') ;
The NPS database provides some functions that assist in the conversion of data from one type to another.
HH, HH12 |
Hour of day (01:12). |
HH24 |
Hour of day (00:23). |
MI |
Minute (00:59). |
SS |
Second (00:59). |
SSSS |
Seconds past midnight (0:86399). |
AM, am, A.M., a.m. or PM, pm, P.M., p.m. |
Meridian indicator (uppercase and lowercase). |
Y,YYY |
Year (4 and more digits) with a comma. |
YYYY |
Year (4 and more digits). |
YYY |
Last 3 digits of the year. |
YY |
Last 2 digits of the year. |
Y |
Last digit of the year. |
MONTH, Month, month |
Full month name (blank-padded to 9 chars). |
MON, Mon, mon |
Abbreviated uppercase month name (3 chars). |
MM |
Month number (01:12). |
DAY, Day, day |
Full day name (blank-padded to 9 chars). |
DY, Dy, dy |
Abbreviated uppercase day name (3 chars). |
DDD |
Day of the year (001:366). |
DD |
Day of the month (01:31). |
D |
Day of the week (1:7; SUN=1). |
BC, bc, B.C., b.c or AD, ad, A.D., a.d. |
Era indicator (uppercase and lowercase). |
W |
Week of the month (1:5) where first week start on the first day of the month. |
WW |
Week number of the year (1:53) where the first |
week |
starts on the first day of the year. |
IW |
ISO week number of the year (The first Thursday of the new year is in week 1.) |
CC |
Century (2 digits). |
J |
Julian Day (days since January 1, 4712 BC). |
Q |
Quarter |
RM |
Month in Roman Numerals (I-XII; I=January) — uppercase. |
rm |
Month in Roman Numerals (i-xii; i=January) — lowercase. |
FM prefix |
Fill mode (suppresses padding blanks and zeroes). |
TH, th suffix |
Add uppercase ordinal number suffix. |
FX prefix |
Fixed format global option. |
9 |
Value with the specified number of digits. |
0 |
Value with leading zeros. |
. (period) |
Decimal point. |
, (comma) |
Group (thousand) separator. |
PR |
Negative value in angle brackets. |
S |
Negative value with minus sign (uses locale). |
L |
Currency symbol (uses locale). |
D |
Decimal point (uses locale). |
G |
Group separator (uses locale). |
MI |
Minus sign in the specified position (if number < 0). |
RN |
Roman numeral (input between 1 and 3999). |
V |
Shift n digits (see notes). |
The to_char command will take a value and convert it to a character string. This includes formatting a date.
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 '2012-01-10') - (date '1959-01-10') AS "Tom"s Age In Days";
Tera-Tom's Age In Days
19358
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.
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 '2012-01-10') - (date '1959-01-10') AS "Tom"s Age In Days";
Tera-Tom's Age In Days
19358
Let's find the number of years Tera-Tom has been alive since his last birthday.
SELECT ((date '2012-01-10') - (date '1959-01-10'))/365 "Tom"s Age In Years";
Tera-Tom's Age In Years
53
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".
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"
FROMOrder_Table ORDER BY 2 ;
Answer Set
SELECT 'Due Date:' AS "''" /* title as 2 single quotes for 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')
‚Order_Total
FROMOrder_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.
Compatibility: Matrix Extension.The syntax for implied extract:
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 '2012-01-10','DD')AS Day_portion
‚to_char(date '2012-01-10','MM')AS Month_portion
‚to_char(date '2012-01-10','YYYY') AS Year_portion ;
It was mentioned earlier that Matrix 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.
Compatibility: Matrix Extension. Syntax of DATE_PART:
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)
‚CURRENT_TIME
‚DATE_PART('MINUTE',CURRENT_TIME)
‚CURRENT_TIMESTAMP
‚DATE_PART('SECOND',CURRENT_TIMESTAMP) ;
The DATE_PART function works exactly like EXTRACT. Although the name contains DATE, it also works with time and time stamp data. Notice the column headers!
The following SELECT will show DATE_PART with a Date,
Time, and Timestamp and add an ALIAS for each DATE_PART.
SELECT
CURRENT_DATE
‚DATE_PART('MONTH',CURRENT_DATE) as "MONTH"
‚CURRENT_TIME
‚DATE_PART('MINUTE',CURRENT_TIME) as "MINUTE"
‚CURRENT_TIMESTAMP
‚DATE_PART('SECOND',CURRENT_TIMESTAMP) as "SECOND" ;
The DATE_PART function works exactly like EXTRACT. Although the name contains DATE, it also works with time and time stamp data. Now notice the column headers!
Compatibility:Matrix Extension.Syntax of DATE_TRUNC:
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 August 1, and the day portion does not change. However, the entire time portion is set back to 12:00:00. When DATE data is used, the time portion is set to 12:00:00 just like in the above example.
Compatibility:Matrix Extension.Syntax of DATE_TRUNC:
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 9:00:00, the minute portion becomes 9:05: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.
Compatibility:Matrix Extension
Syntax:MONTHS_BETWEEN (<start_date>,<end_date>)
The following example uses the MONTHS_BETWEEN with some fixed dates
SELECT months_between(date '2004-10-01',date '2004-09-01')
,months_between(date '2004-10-01',date '2004-09-15')
,months_between(date '2004-10-01',date '2002-08-15')
,months_between(date '2003-10-01',date '2004-10-15') ;
The MONTHS_BETWEEN function is handy for doing date subtraction. Unlike normal date subtraction, it returns a fractional portion based on the days in a month.
SELECT ADD_MONTHS(Order_Date, 2) AS"Due Date"
,Order_Date
,to_char(Order_total,'$99,999.99')
,MONTHS_BETWEEN(Add_Months(order_date,2),order_date)
FROMOrder_Table
ORDER BY 2 ;
The above example uses the order table to demonstrate BETWEEN_MONTHS.
Matrix has the ANSI time display and TIME data type.
CURRENT_TIME is the ANSI name of the time function.
SELECT CURRENT_TIME;
TIME
17:27:56
SELECT Current_Time
,CURRENT_TIME - 55 as Subtract
TIMESubtract
17:27:5617:27:01
As well as creating a TIME data type, intelligence has been added to the clock software. It can increment or decrement TIME with the result increasing to the next minute or decreasing from the previous minute based on the addition or subtraction of seconds.
TIMESTAMP is a display format, a reserved name and a new data type. It is a combination of the DATE and TIME data types combined together into a single column data type.
SELECT CURRENT_TIMESTAMP
Notice that there is a space between the DATE and TIME portions of a TIMESTAMP. This is a required element to delimit or separate the day from the hour.
Compatibility: Matrix Extension
The TIMESTAMP function can be used to convert a date or combination of a date and time into a timestamp. Syntax for using TIMESTAMP:
TIMESTAMP(<date> [ , <time> ] )
SELECT TIMESTAMP(CURRENT_DATE)
,TIMESTAMP(CURRENT_DATE, CURRENT_TIME)
,TIMESTAMP(DATE '2005-10-01', TIME '08:30:05');
What a wonderful feature. Matrix allows you to convert a date or a combination of a date and a time into a Timestamp. The example above shows an example of converting a date, a date and time, and a literal date and time. This should be all you need.
The TO_TIMESTAMP function can be used to convert
characters strings to a timestamp.
Syntax for using TO_TIMESTAMP:
TO_TIMESTAMP(<date-string> [ , <time-string> ] )
Matrix allows you to convert character strings into a Timestamp. Notice that both answers are exactly the same. The second parameter is NOT how the data should be output or formatted, but instead it reflects how the string should be interpreted.
Compatibility: Matrix Extension
The timestamp can also be obtained using the NOW() function:
SELECT NOW() ;
SELECT NOW() ;
NOW
08/03/2012 11:07:10
Matrix allows you to see the date and time with the NOW() function. The next time someone asks for the time tell them NOW.
Compatibility:Matrix Extension
To get a bit more extended version of a time stamp use TIMEOFDAY:
SELECT TIMEOFDAY () ;
Answer Set
TIMEOFDAY
Fri Aug 03 11:11:38 2012 EDT
Matrix allows you an extended version of a time stamp that is robust and verbose.
Compatibility: Matrix Extension
The AGE function returns the interval (discussed later in this chapter) between two time stamps. If you use a single time stamp, the age function returns the interval between the current time and the time stamp provided. The interval returned by the age function can include year and month data as well as day and time data.
Syntax of AGE:
AGE(<start-date>,<end-date>)
SELECT CURRENT_TIMESTAMP
,AGE('10-28-2004','7-20-2003')
,AGE(current_timestamp,'7-20-2003')
,AGE('7-20-2003') as AGE2 /* defaults to CURRENT_TIMESTAMP */ ;
Answer Set
To subtract one time stamp from another, use the AGE function.
Matrix 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.
Syntax for changing the session time zone:
SET TIME ZONE { LOCAL | DEFAULT | 'xxx[-]H[H:MI]yyy' ;
Where xxx is the designation for standard time and yyy is for daylight savings time
Setting a Session's time zone:
SET TIME ZONE LOCAL ;/* use system level */
SET TIME ZONE 'PST8PDT' ;/* explicit setting Pacific */
SET TIME ZONE 'HKT-11:00HKT'; /*uses both hours and optional minutes */
A Matrix session can modify the time zone during normal operations without requiring a logoff and logon. At this time, the NPS only recognizes time zone processing stored in a table with data type of TIME WITH TIME ZONE. Hopefully, it will soon also be added to TIMESTAMP when stored in a table.
The way time zones are implemented in Matrix is that the session time zone setting adjusts the value returned by the TIME and TIMESTAMP when referenced in an SQL statement. To make some of the changes more apparent, the following statements "assume" that they are all run at the same time.
Examples above set the time zone and then query Current_Timestamp simultaneously.
Interval Chart
Simple |
More involved |
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
Matrix has added INTERVAL processing, however, it is not ANSI compliant. Intervals are used to perform DATE, TIME and TIMESTAMP arithmetic and conversion.
SELECT Current_Date as Our_Date
,Current_Date+Interval '1' Dayas Plus_1_Day
,Current_Date+Interval '3' Monthas 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/201202/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.
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.
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') DAYAS 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') HOURAS Actual_Hours
,(TIME '12:45:01' - TIME '10:10:01') MINUTEAS 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.
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: Matrix 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. Matrix 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.
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 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 Timel
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.