Chapter 7 – Date Functions
"An inch of time cannot be bought with an inch of gold."
Chinese Proverb
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. Netezza 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 Netezza.
Along with the functions, there are corresponding DATE, TIME, and TIMESTAMP data types that allow these references to be stored in tables.
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.
Conversion Functions
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.
Conversion Function Templates
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 |
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). |
Conversion Function Templates Continued
Formatting A Date
The to_char command will take a value and convert it to a character string. This includes formatting a date.
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 '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.
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 '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".
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 ADD_MONTHS Command
This is the Add_Months Command. What you can do with it is add a month or many months your columns date. Can you convert this to one year?
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
In this example, we multiplied 12 months times 5 for a total of 5 years!
Above, you see a great technique for adding multiple years to a date. Can you now SELECT only the orders in September?
Date Related Functions
Compatibility: Netezza Extension to provide some 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 ;
The EXTRACT Command
SELECT Order_Date
,Add_Months (Order_Date,12 * 5) as "Due 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!
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
Just like the Add_Months, the EXTRACT Command is a Temporal Function or a Time-Based Function.
EXTRACT with DATE and TIME Literals
SELECT EXTRACT(YEAR FROM DATE '2000-10-01') AS Yr_Part
,EXTRACT(MONTH FROM DATE '2000-10-01') AS Mth_Part
,EXTRACT(DAY FROM DATE '2000-10-01') AS Day_Part
,EXTRACT(HOUR FROM TIME '10:01:30') AS Hr_Part
,EXTRACT(MINUTE FROM TIME '10:01:30') AS Min_Part
,EXTRACT(SECOND FROM TIME '10:01:30') AS Sec_Part
,EXTRACT(MONTH FROM current_timestamp) AS ts_Mth_Part
,EXTRACT(SECOND FROM current_timestamp) AS ts_Sec_Part ;
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.
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.
A Side Title example with Reserved Words as an Alias
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
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
Compatibility: Netezza 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 Netezza 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
Compatibility: Netezza 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!
DATE_PART Function using an ALIAS
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!
DATE_TRUNC Function
Compatibility: Netezza 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 July 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 example above.
DATE_TRUNC Function using TIME
Compatibility: Netezza 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.
MONTHS_BETWEEN Function
Compatibility: Netezza 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.
MONTHS_BETWEEN Function in Action
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)
FROM Order_Table
ORDER BY 2 ;
The above example uses the order table to demonstrate BETWEEN_MONTHS.
ANSI TIME
Netezza 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
TIME Subtract
17:27:56 17: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.
ANSI TIMESTAMP
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.
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.
Netezza TIMESTAMP Function
Compatibility: Netezza 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') ;
Answer Set
What a wonderful feature. Netezza 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.
Netezza TO_TIMESTAMP Function
Compatibility: Netezza Extension
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> ] )
Answer Set
TO_TIMESTAMP TO_TIMESTAMP
10/01/2005 8:30:05.204331 10/01/2005 8:30:05.204331
Netezza 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.
Netezza NOW() Function
Compatibility: Netezza Extension
The timestamp can also be obtained using the NOW() function:
SELECT NOW() ;
SELECT NOW() ;
Answer Set
NOW
08/03/2012 11:07:10
Netezza allows you to see the date and time with the NOW() function. The next time someone asks for the time tell them NOW.
Netezza TIMEOFDAY Function
Compatibility: Netezza 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
Netezza allows you an extended version of a time stamp that is robust and verbose.
Netezza AGE Function
Compatibility: Netezza 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.
Time Zones
A time zone relative to London (UTC) might be:
LA----------Miami-----------Frankfurt------------Hong Kong
+8:00 +05:00 00:00 -08:00
A time zone relative to New York (EST) might be:
LA----------Miami-----------Frankfurt------------Hong Kong
+3:00 00:00 -05:00 -13:00
Netezza 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 example, 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
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 Netezza 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.
Using Time Zones
The way time zones are implemented in Netezza 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.
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 |
NETEZZA has added INTERVAL processing, however, it is not ANSI compliant. Intervals are used to perform DATE, TIME, and TIMESTAMP arithmetic and conversion.
Using Intervals
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' Year as Plus_5_Years
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
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.
Interval Arithmetic Results
DATE and TIME arithmetic results using intervals:
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
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).
A Time Interval Example
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.
A - DATE Interval Example
SELECT Current_Date,
INTERVAL -'2' YEAR + CURRENT_DATE as Two_years_Ago;
Date |
Two_Year_Ago |
06/18/2012 |
06/18/2010 |
The above Interval example uses a –'2' to go back in time.
A Complex Time Interval Example using CAST
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.
A Complex Time Interval Example using CAST
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
SELECT CAST(INTERVAL '1300' MONTH as interval YEAR(3) TO MONTH)
AS "Years & Months";
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
Compatibility: Netezza 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. Netezza 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
SELECT 'The dates overlap' AS OverlapAnswer
WHERE (DATE '2001-01-01', DATE '2001-11-30') OVERLAPS
(DATE '2001-11-30', DATE '2001-12-31') ;
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
SELECT'The Times Overlap'As DoThe
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.
The OVERLAPS Command using a NULL Value
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.