6.6 Manipulating Temporal Data
Date arithmetic is an important aspect of our day-to-day life. We find the age of a person by subtracting his date of birth from today's date. We compute the date a warranty expires by adding the warranty period to the purchase date. Drivers' license expirations, bank interest calculation, and a host of other things all depend on date arithmetic. It is extremely important for any database to support such common date arithmetic operations.
6.6.1 Using the Built-in Temporal Functions
Oracle provides a number of helpful functions, some of which you've seen used earlier in this chapter, that you can use to manipulate temporal values:
- ADD_MONTHS( date_value, months)
-
Adds months to a date. Add negative values to subtract months. If the initial date represents the last day of a month, the result is forced to the final day of the result month. Section 6.6.2 discusses this function in detail.
- CURRENT_DATE
-
Returns the current date in the session time zone, as a DATE value.
- CURRENT_TIMESTAMP
-
Returns the current date and time in the session time zone, as a TIMESTAMP WITH TIME ZONE value.
- DBTIMEZONE
-
Returns the database time zone.
- EXTRACT( element FROM temporal_value)
-
Returns the specified element from a date, timestamp, or interval. Valid elements, which are SQL keywords and not string values, are: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, and TIMEZONE_ABBR. The temporal value may be any of Oracle's date, timestamp, or interval types.
- FROM_TZ( timestamp, time_zone)
-
Converts a TIMESTAMP into a TIMESTAMP WITH TIME ZONE, essentially merging the two values you provide into one. The time_zone argument must be a string in the form [+|-]hh:mi.
- LAST_DAY( date_value)
-
Computes the last day of the month in which the given DATE value falls.
- LOCALTIMESTAMP
-
Returns the current date and time in the session timezone, as a TIMESTAMP value.
- MONTHS_BETWEEN( later_date, earlier_date)
-
Determines the number of months between two dates. The calculation is performed as: later_date - earlier_date. If later_date is actually earlier, than you'll get a negative result. See Section 6.6.3 for a detailed look at this function.
- NEW_TIME( date, source_time_zone, target_time_zone)
-
Translates the time component of date from the source_time_zone to the target_time_zone. The time zone arguments must be strings containing time zone abbreviations such as PST, EST, CST. The list of time zones supported for use with NEW_TIME is shorter, and distinct from, the list of time zones supported for the timestamp types.
- NEXT_DAY( date, weekday)
-
Returns the date of the next specified weekday following the given date. The weekday argument must be a valid weekday name or abbreviation in the current language?e.g., "Monday," "Tuesday," "Wed," "Thu."
- ROUND( temporal_value, format_element)
-
Rounds a date or timestamp value to the specified element. See Section 6.6.5.
- SESSIONTIMEZONE
-
Returns the session timezone.
- SYSDATE
-
Returns the current date and time for the operating system on which the database resides.
- SYSTIMESTAMP
-
Returns the current date and timestamp time for the operating system on which the database resides as a TIMESTAMP WITH TIME ZONE value.
- SYS_EXTRACT_UTC ( timestamp_with timezone_value)
-
Returns the UTC data and time value with respect to the input TIMESTAMP WITH TIME ZONE value.
- TRUNC( temporal_value, format_element)
-
Truncates a date/time value to a specific element. See Section 6.6.5.
- TZ_OFFSET([ tz_name| tz_offset])
-
Returns the time zone offset with respect to UTC. Input may be a time zone name from V$TIMEZONE_NAMES or a time zone offset in the form [+|-]hh:mi.
SYSDATE is one of the most commonly used functions, and returns the current date and time as a DATE value:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH:MI:SS'; Session altered. SELECT SYSDATE FROM DUAL; SYSDATE ------------------ 11-NOV-01 01:00:10
The following is an example of a function that takes arguments. The FROM_TZ function is used to add time zone information to the timestamp returned by a call to SYSTIMESTAMP. You can see that LOCALTIMESTAMP by itself returns no time zone information. FROM_TZ combines the TIMESTAMP with the time zone we specified, and returns a TIMESTAMP WITH TIME ZONE:
SELECT LOCALTIMESTAMP FROM dual; LOCALTIMESTAMP -------------------------------------------------------- 18-DEC-03 03.31.24.974000 PM SELECT FROM_TZ(LOCALTIMESTAMP,'-5:00') FROM dual; FROM_TZ(LOCALTIMESTAMP,'-5:00') -------------------------------------------------------- 18-DEC-03 03.31.25.024000 PM -05:00
The EXTRACT function is unusual in that its first argument is actually a SQL keyword, and the delimiter between arguments is also a keyword:
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;
EXTRACT(YEARFROMSYSDATE)
------------------------
2003A more useful and interesting example of EXTRACT is shown at the end of Section 6.6.3.
Many of Oracle's temporal functions take only DATE values as inputs. This harks back to the day when DATE was the only temporal type. You have to be careful about this, because Oracle will implicitly convert timestamp types to DATEs, leading you to inadvertently write erroneous code. For example:
SELECT ADD_MONTHS(SYSTIMESTAMP,1) FROM dual; ADD_MONTH --------- 18-JAN-04
The problem here isn't terribly obvious, but what's happened is that SYSTIMESTAMP has returned a TIMESTAMP WITH TIME ZONE value, which has been implicitly cast to a DATE, and thus both fractional seconds and the time zone have been lost. The results are the same as if you'd executed:
SELECT ADD_MONTHS(CAST(SYSTIMESTAMP AS DATE),1) FROM dual;
Be careful about passing TIMESTAMP values to functions that expect DATEs. If your code depends on fractional seconds or time zone information, you'll lose that information, and your code won't work as you expect.
|
6.6.2 Addition
Adding two datetime values doesn't make sense. However, you can add days, months, years, hours, minutes, and seconds to a datetime to generate a future date and time. How you go about adding time intervals to datetime values depends on whether you are working with a DATE or one of the TIMESTAMP values.
6.6.2.1 Adding numbers to a DATE
The + operator allows you to add numbers to a DATE. The unit of a number added to a DATE is assumed to be days. Therefore, to find tomorrow's date, you can add 1 to SYSDATE:
SELECT SYSDATE, SYSDATE+1 FROM DUAL; SYSDATE SYSDATE+1 --------- --------- 05-OCT-01 06-OCT-01
Any time you add a number to a DATE, Oracle assumes that the number represents a number of days. Therefore, if you want to add multiples of a day (week, month, year, etc.) to a DATE, you first need to multiply by a conversion factor. For example, to add one week to today's date, you add 7 (7 days in a week times 1 day) to SYSDATE:
SELECT SYSDATE+7 FROM DUAL; SYSDATE+7 --------- 12-OCT-01
Similarly, if you want to add fractions of a day (hour, minute, second) to a DATE, you first need to convert such fractions into a fractional number of days. Do this by dividing by a conversion factor. For example, to add 20 minutes to the current date and time, you need to add (20 minutes/1,440 minutes in a day) to SYSDATE:
SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH:MI:SS'), TO_CHAR(SYSDATE+(20/1440),'DD-MON-YY HH:MI:SS') FROM DUAL; TO_CHAR(SYSDATE,'D TO_CHAR(SYSDATE+(2 ------------------ ------------------ 05-OCT-01 01:22:03 05-OCT-01 01:42:03
|
6.6.2.2 Adding months to a DATE
Adding months to a DATE is not as easy as adding weeks, because all months don't have the same number of days?some have 30, some 31, some 28, and at times even 29. To add one month to a DATE, you need to know how many days that calendar month will have. Therefore, adding months to a DATE by converting those months to a number of days involves lots of homework, which is error-prone. Fortunately, Oracle does all the homework for us, and provides a built-in SQL function to add months to DATE values. This function is called ADD_MONTHS, and you call it as follows:
SELECT fname, hire_date, ADD_MONTHS(hire_date, 6) review_date FROM employee; FNAME HIRE_DATE REVIEW_DA -------------------- --------- --------- John 22-OCT-99 22-APR-00
This example shows the computation of an employee's biannual review date by using ADD_MONTHS to add six months to the employee's hire_date. The input DATE and the result DATE both fall on the 22nd of the month. This would not have happened if we had added 180 days to the input DATE. ADD_MONTHS is "smart" in one other way, too. The following example adds 6 months to 31 December 1999:
SELECT ADD_MONTHS('31-DEC-99',6) FROM DUAL;
ADD_MONTH
---------
30-JUN-00The ADD_MONTHS function is intelligent enough to know that adding 6 months to 31 December should result in the last day of June. And since the last day of June is the 30th (not 31st), it returns 30 June, 2000.
|
6.6.2.3 Adding true INTERVAL values rather than numbers
You can use the + operator to add INTERVALs to DATE or TIMESTAMP values. For example, assume the flight time between New York and Los Angeles is 6 hours and 27 minutes. To find the arrival time of the flight, leaving New York now (3:39 PM in the example), at Los Angeles, you add the flight time to the current time:
select sysdate, sysdate + INTERVAL '0 6:27:00' DAY TO SECOND from dual; SYSDATE SYSDATE+INTERVAL'06 ------------------- ------------------- 12/29/2003 15:39:00 12/29/2003 22:06:00
The preceding example adds an interval literal INTERVAL '0 6:27:00' DAY TO SECOND (6 hours and 27 minutes) to the DATE value returned by SYSDATE. The result of the addition is a DATE value and is in the New York (EST) time zone. However, since the destination is Los Angeles, you would like the output to be in the local time zone of the destination (PST). To achieve this, you can use the NEW_TIME function, as shown in the following example:
select sysdate,
new_time(sysdate + INTERVAL '0 6:27:00' DAY TO SECOND, 'EST','PST')
from dual;
SYSDATE NEW_TIME(SYSDATE+IN
------------------- -------------------
12/29/2003 15:39:00 12/29/2003 19:06:00Therefore, a flight with flight time of 6 hours and 27 minutes that leaves New York at 3:39 PM will reach Los Angeles at 7:06 PM local time.
Similarly to adding intervals to DATE values, you can add intervals to TIMESTAMP values. For example:
SELECT LOCALTIMESTAMP, LOCALTIMESTAMP + INTERVAL '0 3:16:23' DAY TO SECOND FROM DUAL; LOCALTIMESTAMP --------------------------------------------- LOCALTIMESTAMP+INTERVAL'03:16:23'DAYTOSECOND --------------------------------------------- 28-MAR-04 04.30.19.208000 PM 28-MAR-04 07.46.42.208000000 PM
If you need to add some number of days to a TIMESTAMP value, you shouldn't just directly add the number to the TIMESTAMP. In doing so, the TIMESTAMP will be implicitly converted to a DATE, which results in the loss of information. Specifically, you'll lose your fractional seconds. Rather, you should convert the number to an interval, and then add the interval to the TIMESTAMP. The result will be a TIMESTAMP value, and no information will be lost. For example:
SELECT LOCALTIMESTAMP + INTERVAL '1 0:00:00' DAY TO SECOND FROM DUAL; LOCALTIMESTAMP+INTERVAL'10:00:00'DAYTOSECOND --------------------------------------------- 29-MAR-04 04.36.46.211000000 PM
As an alternative to using an INTERVAL literal as in the preceding example, you can use the NUMTODSINTERVAL function to convert a number to an interval, as shown in the following example:
SELECT LOCALTIMESTAMP + NUMTODSINTERVAL(1,'DAY') FROM DUAL; LOCALTIMESTAMP+NUMTODSINTERVAL(1,'DAY') ----------------------------------------- 29-MAR-04 04.37.16.077000000 PM
6.6.3 Subtraction
Even though no other arithmetic operation (addition, multiplication, division) between two DATEs makes any sense, subtracting one DATE from another DATE is a very common and useful operation. The - operator allows you to subtract a DATE from a DATE, or a number from a DATE, a TIMESTAMP from a TIMESTAMP, an interval from a DATE and an interval from a TIMESTAMP.
6.6.3.1 Subtracting one DATE from another
Subtracting one DATE from another DATE returns the number of days between those two DATEs. The following example displays the lead time of a set of orders by subtracting the date on which the order was placed (order_dt) from the expected ship date (expected_ship_dt):
SELECT order_nbr, expected_ship_dt - order_dt lead_time
FROM cust_order;
ORDER_NBR LEAD_TIME
---------- ----------
1001 1
1000 5
1002 13
1003 10
1004 9
1005 2
1006 6
1007 2
1008 2
1009 4
1012 1
1011 5
1015 13
1017 10
1019 9
1021 2
1023 6
1025 2
1027 2
1029 46.6.3.2 Subtracting one TIMESTAMP from another
Subtracting one TIMESTAMP from another TIMESTAMP returns an interval of type INTERVAL DAY TO SECOND. For example:
SELECT LOCALTIMESTAMP - transaction_timestamp FROM transaction; SYSTIMESTAMP-TRANSACTION_TIMESTAMP ----------------------------------- +000000453 07:04:39.086000
6.6.3.3 Subtracting a number from a DATE
Along with subtracting one DATE from another, you can also subtract a number from a DATE. Subtracting a number from a DATE returns a DATE that number of days in the past. For example, subtracting 1 from SYSDATE gives yesterday, and subtracting 7 from SYSDATE yields the same day last week:
SELECT SYSDATE, SYSDATE - 1, SYSDATE - 7 FROM DUAL; SYSDATE SYSDATE-1 SYSDATE-7 --------- --------- --------- 05-OCT-01 04-OCT-01 28-SEP-01
|
6.6.3.4 Subtracting months from a DATE
Unlike ADD_MONTHS, Oracle doesn't provide a SUBTRACT_MONTHS function. To subtract months from a DATE, use the ADD_MONTHS function, and pass a negative number as the second parameter:
SELECT SYSDATE, ADD_MONTHS(SYSDATE, -6) FROM DUAL; SYSDATE ADD_MONTH --------- --------- 05-OCT-01 05-APR-01
6.6.3.5 Number of months between two DATEs
Earlier in this section you saw that subtracting a DATE from another DATE returns the number of days between the two dates. There are times when you may want to know the number of months between two DATEs. Consider that subtracting an employee's hire_date from SYSDATE yields the number of days of experience the employee has with her employer:
SELECT SYSDATE-hire_date FROM employee;
SYSDATE-HIRE_DATE
-----------------
714.0786It's better, in most cases, to find the number of months of experience rather than the number of days. You know that dividing the number of days between two DATEs by 30 won't accurately calculate the number of months between those two DATEs. Therefore, Oracle provides the built-in SQL function MONTHS_BETWEEN for finding the number of months between two DATEs. MONTHS_BETWEEN is called as follows:
SELECT MONTHS_BETWEEN(SYSDATE,hire_date),
MONTHS_BETWEEN(hire_date, SYSDATE)
FROM employee;
MONTHS_BETWEEN(SYSDATE,HIRE_DATE) MONTHS_BETWEEN(HIRE_DATE,SYSDATE)
--------------------------------- ---------------------------------
267.83499 -267.83499MONTHS_BETWEEN subtracts the second DATE from the first. So, if the second DATE comes later than the first, then MONTHS_BETWEEN will return a negative value. You can see that behavior in this example. Both calls use the same two DATEs, but in different orders, and the difference in results is that one result is negative while the other is positive.
There is no YEARS_BETWEEN function. To find the number of years between two DATEs, you can either subtract the two DATEs to find the number of days and then divide by 365, or use MONTHS_BETWEEN to find the number of months and then divide by 12. All years don't have the same number of days?some have 365 days and others have 366 days. Therefore, it is not accurate to divide the number of days by 365 to get the number of years. On the other hand, all years have 12 months, whether a leap year or not. Therefore, the most accurate way to calculate the number of years between two DATEs is to use the MONTHS_BETWEEN function to find the number of months and then divide by 12 to get the number of years.
6.6.3.6 Time interval between two DATEs
As you saw in the preceding examples, subtracting one DATE from another returns the number of days. However, at times, if the difference is a fractional day, you would like to find the number of hours, minutes, and seconds between two points in time. In the next example, a pizza delivery center keeps track of the order_receive_time and the delivery_time of the orders it receives:
CREATE TABLE pizza_delivery (
order_id NUMBER(10),
order_receive_time DATE,
delivery_time DATE);
Table created.
INSERT INTO pizza_delivery VALUES
(1, TO_DATE('12/20/03 08:47:53','MM/DD/YY HH:MI:SS'),
TO_DATE ('12/20/03 10:30:34','MM/DD/YY HH:MI:SS'));
1 row created.
COMMIT;
Commit complete.The manager of the pizza delivery center wants to know the time difference between the order_receive_time and the delivery_time. You can use the date subtraction operation as in the following query:
SELECT delivery_time - order_receive_time FROM pizza_delivery;
DELIVERY_TIME-ORDER_RECEIVE_TIME
--------------------------------
.07130787The fractional days returned by the preceding query doesn't make much sense. The manager wants to know the time difference in hours, minutes, and seconds. One way to find this would be to convert the number returned by the date subtraction into an interval using the NUMTODSINTERVAL function, and then extract the hour, minute, and second components of this interval using the EXTRACT function:
SELECT EXTRACT(HOUR FROM
NUMTODSINTERVAL(delivery_time - order_receive_time, 'DAY'))
||':'||
EXTRACT(MINUTE FROM
NUMTODSINTERVAL(delivery_time - order_receive_time, 'DAY'))
||':'||
EXTRACT(SECOND FROM
NUMTODSINTERVAL(delivery_time - order_receive_time, 'DAY'))
"Lead Time"
FROM pizza_delivery;
Lead Time
---------------------------------------------------------------------------
1:42:41This example uses three expressions to extract hour, minute, and second, respectively, and then concatenates those values using the : delimiter to return the result in the HH:MI:SS format, which is much easier to comprehend compared to a fractional day.
6.6.3.7 Subtracting an INTERVAL from a DATE or TIMESTAMP
You can use the - operator to subtract an INTERVAL from a DATE or TIMESTAMP value. For example, if you need to reach your office at 8:00 AM, and it takes 30 minutes to reach from your home to office, you can use the following example to back calculate the start time:
SELECT TO_DATE('12/29/2003 08:00:00') - INTERVAL '0 0:30:00' DAY TO SECOND
FROM DUAL;
TO_DATE('12/29/2003
-------------------
12/29/2003 07:30:00The preceding example subtracts an interval literal (INTERVAL '0 0:30:00' DAY TO SECOND) from a DATE value, and returns a DATE value. Similarly, you can subtract an interval from a TIMESTAMP value, using the "-" operator, as shown in the following example:
SELECT LOCALTIMESTAMP - INTERVAL '0 0:30:00' DAY TO SECOND FROM DUAL; LOCALTIMESTAMP-INTERVAL'00:30:00'DAYTOSECOND --------------------------------------------- 28-MAR-04 03.42.59.819000000 PM
The preceding example subtracts an interval literal (INTERVAL '0 0:30:00' DAY TO SECOND) from a TIMESTAMP value, and returns a TIMESTAMP value.
If you need to subtract some number of days from a TIMESTAMP value, you shouldn't just directly subtract the number from the TIMESTAMP. In doing so, the TIMESTAMP will be implicitly converted to a DATE and result in loss of information. Rather, you should convert the number to an interval, and then subtract the interval from the TIMESTAMP. This way, the result will be a TIMESTAMP value, and no information will be lost, as illustrated in the following example:
SELECT LOCALTIMESTAMP - INTERVAL '1 0:00:00' DAY TO SECOND FROM DUAL; LOCALTIMESTAMP-INTERVAL'10:00:00'DAYTOSECOND ---------------------------------------------- 27-MAR-04 04.23.09.248000000 PM
As an alternative to the preceding example, you can use the NUMTODSINTERVAL function to convert a number to an interval, as shown in the following example:
SELECT LOCALTIMESTAMP - NUMTODSINTERVAL(1,'DAY') FROM DUAL; LOCALTIMESTAMP-NUMTODSINTERVAL(1,'DAY') ----------------------------------------- 27-MAR-04 04.27.41.052000000 PM
6.6.4 Determining the First Day of the Month
Oracle provides a built-in function to get the last day of a month. The function returns the last day of the month containing the input date. For example, to find the last date of the current month, you can use the following SQL statement:
SELECT LAST_DAY(SYSDATE) "Next Payment Date" FROM DUAL; Next Paym --------- 31-OCT-01
Sometimes it's useful to be able to determine the first day of a given month; it would be nice if Oracle would provide a FIRST_DAY function. One approach to getting the first day of the month for a given date is to use the TRUNC function:
TRUNC(SYSDATE,'MM')
A side-effect of this approach is that any time-of-day component of the input value is eliminated; the result will always have a time of midnight at the beginning of the day. Such a truncation of time may be good, especially if you are doing a range comparison. For example, to find all employees hired in the current month, without applying any sort of function to the hire_date column:
SELECT * FROM employee WHERE hire_date >= TRUNC(SYSDATE,'MM') AND hire_date < TRUNC(LAST_DAY(SYSDATE)+1);
This SELECT statement works because TRUNC(SYSDATE,'MM') sets the time-of-day to the very beginning of the first day of the month. The second TRUNC expression resolves to the very beginning of the first day of the following month, which is why less-than rather than less-than-or-equal-to is used when comparing hire_date to that value.
In many cases, TRUNC(date,'MM') will work just fine for getting to the first day of a month. However, if you need to determine the first day of the month in which a given DATE value falls while also preserving the time-of-day, you can use the following expression:
ADD_MONTHS((LAST_DAY(SYSDATE)+1), -1)
This expression finds the last day of the month represented by date. It then adds 1 to get to the first day of the subsequent month, and finally uses ADD_MONTHS with an argument of -1 to go back to the beginning of the month in which you started. The result is the first day of the month in which the given date falls. Other approaches to this problem are possible; this is just one that works well for us. This approach has the advantage of preserving the time component of the date in question.
6.6.5 Rounding and Truncating Dates
Rounding and truncating dates is similar in concept to the rounding and truncating of numbers, but more involved because an Oracle DATE contains date as well as time information. Use the ROUND function to round a date/time value to a specific element; use the TRUNC function to truncate a date/time value to a specific element.
|
The return value from ROUND or TRUNC depends upon the specified format, which is an optional parameter. If you don't specify a format in the call to ROUND, the function returns a date by rounding the input to the nearest day. If you don't specify a format in the call to TRUNC, that function returns the input date by setting the time component to the beginning of the day.
When using ROUND and TRUNC to round to the nearest day, or to truncate a date, the functions set the time fields of the return value to the beginning of the returned day?i.e., 12:00:00 AM (00:00:00 in HH24 format). For example:
SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH:MI:SS AM'),
TO_CHAR(ROUND(SYSDATE), 'DD-MON-YY HH:MI:SS AM'),
TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YY HH:MI:SS AM')
FROM DUAL;
TO_CHAR(SYSDATE,'DD-M TO_CHAR(ROUND(SYSDATE TO_CHAR(TRUNC(SYSDATE
--------------------- --------------------- ---------------------
06-OCT-01 07:35:48 AM 06-OCT-01 12:00:00 AM 06-OCT-01 12:00:00 AMNotice that since the input time (SYSDATE) is before 12 noon, the output of ROUND and TRUNC are the same. However, if the input time were after 12 noon, the output of ROUND and TRUNC would be different, as in the following example:
SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH:MI:SS AM'),
TO_CHAR(ROUND(SYSDATE), 'DD-MON-YY HH:MI:SS AM'),
TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YY HH:MI:SS AM')
FROM DUAL;
TO_CHAR(SYSDATE,'DD-M TO_CHAR(ROUND(SYSDATE TO_CHAR(TRUNC(SYSDATE
--------------------- --------------------- ---------------------
06-OCT-01 05:35:48 PM 07-OCT-01 12:00:00 AM 06-OCT-01 12:00:00 AMSince the input time is past 12 noon, ROUND returns the beginning of the next day. However, TRUNC still returns the beginning of the input date. This is similar to the rounding and truncating of numbers.
When you specify a format as an input to the ROUND and TRUNC functions, things become a bit more involved, but the concepts of rounding and truncating still remain the same. The difference is that the rounding and truncating are now based on the format you specify. For example, if you specify the format as YYYY, the input date will be truncated or rounded based on the year, which means that if the input date is before the middle of the year (July 1), both ROUND and TRUNC will return the first day of the year. If the input date is after July 1, ROUND will return the first day of the next year, whereas TRUNC will return the first day of the input year. For example:
SELECT TO_CHAR(SYSDATE-180, 'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(ROUND(SYSDATE-180,'YYYY'),'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(TRUNC(SYSDATE-180,'YYYY'),'DD-MON-YYYY HH24:MI:SS')
FROM DUAL;
TO_CHAR(SYSDATE-180, TO_CHAR(ROUND(SYSDAT TO_CHAR(TRUNC(SYSDAT
-------------------- -------------------- --------------------
09-APR-2001 20:58:33 01-JAN-2001 00:00:00 01-JAN-2001 00:00:00
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(ROUND(SYSDATE,'YYYY'),'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(TRUNC(SYSDATE,'YYYY'),'DD-MON-YYYY HH24:MI:SS')
FROM DUAL;
TO_CHAR(SYSDATE,'DD- TO_CHAR(ROUND(SYSDAT TO_CHAR(TRUNC(SYSDAT
-------------------- -------------------- --------------------
06-OCT-2001 20:58:49 01-JAN-2002 00:00:00 01-JAN-2001 00:00:00Similarly, you can round or truncate a date to a specific month, quarter, week, century, hour, minute, and so forth by using the appropriate format. Table 6-2 lists the formats (and their meanings) that can be used with the ROUND and TRUNC functions.
|
Rounding unit |
Format |
Remarks |
|---|---|---|
|
Century |
CC |
TRUNC returns the first date of the century. |
|
SCC |
ROUND returns the first date of the century if the input date is before the middle of the century (01-JAN-xx51); otherwise, ROUND returns the first date of the next century. | |
|
Year |
SYYYY | TRUNC returns the first date of the year. |
|
YYYY YEAR SYEAR YYY YY Y |
ROUND returns the first date of the year if the input date is before the middle of the year (01-JUL); otherwise, ROUND returns the first date of the next year. | |
|
ISO |
IYYY |
TRUNC returns the first date of the ISO year. |
IYY IY I | ROUND returns the first date of the ISO year if the input date is before the middle of the ISO year; otherwise, ROUND returns the first date of the next ISO year. | |
|
Quarter |
Q |
TRUNC returns the first date of the quarter. ROUND returns the first date of the quarter if the input date is before the middle of the quarter (the 16th day of the second month of the quarter); otherwise, ROUND returns the first date of the next quarter. |
|
Month |
MONTH |
TRUNC returns the first date of the month. |
MON MM RM | ROUND returns the first date of the month if the input date is before the middle of the month (the 16th day of the month); otherwise, ROUND returns the first date of the next month. | |
|
Week |
WW |
TRUNC returns the first date of the week. ROUND returns the first date of the week if the input date is on or before the middle of the week (based on the first day of the year); otherwise, the first date of the next week. |
|
ISO Week |
IW |
TRUNC returns the first date of the ISO week. ROUND returns the first date of the week if the input date is before the middle of the week (based on the first day of the ISO year); otherwise, ROUND returns the first date of the next week. |
|
Week |
W |
TRUNC returns the first date of the week. ROUND returns the first date of the week if the input date is before the middle of the week (based on the first day of the month); otherwise, ROUND returns the first date of the next week. |
|
Day |
DDD |
TRUNC returns the beginning of the day. |
DD J | ROUND returns the beginning of the day if the input time is before the middle of the day (12:00 noon); otherwise, ROUND returns the beginning of the next day. | |
|
Day of the week |
DAY |
TRUNC returns the first date of the week. |
DY D |
ROUND returns the first date of the week if the input date is before the middle of the week (based on the first day of the month); otherwise, ROUND returns the first date of the next week. | |
|
Hour |
HH |
TRUNC returns the beginning of the hour. |
HH12 HH24 |
ROUND returns the beginning of the hour if the input time is before the middle of the hour (00:30); otherwise, ROUND returns the beginning of the next hour. | |
|
Minute |
MI |
TRUNC returns the beginning of the minute. ROUND returns the beginning of the minute if the input time is before the middle of the minute (00:00:30); otherwise, ROUND returns the beginning of the next minute. |
6.6.6 SELECTing Data Based on Date Ranges
There are times when you need to SELECT data from a table based on a given date range. Let's say you have been asked to print all disputed orders placed on a given date, say 22-JUL-01. Most likely, your immediate response would be a query such as the following:
SELECT * FROM disputed_orders WHERE order_dt = '22-JUL-01'; no rows selected
There's no output. Surprised? Although you know there are orders on 22-JUL-01, this query didn't return any rows. The reason is that order_dt is a DATE column, and contains time as well as date information. On the other hand, the date literal '22-JUL-01' doesn't contain any time information. When you don't specify the time portion in a date literal, the time portion is assumed to be beginning of the day?i.e., 12:00:00 A.M. (or 00:00:00 in 24 hour format). In the disputed_orders table, the time components in the order_dt column are other than 12:00:00 A.M. In this case, the correct query to print orders placed on 22-JUL-01 is:
SELECT order_nbr, cust_nbr, order_dt, expected_ship_dt
FROM disputed_orders
WHERE order_dt BETWEEN
TO_DATE('22-JUL-01 00:00:00','DD-MON-YY HH24:MI:SS') AND
TO_DATE('22-JUL-01 23:59:59','DD-MON-YY HH24:MI:SS');
ORDER_NBR CUST_NBR ORDER_DT EXPECTED_
--------- ---------- --------- ---------
1001 1 22-JUL-01 23-JUL-01
1005 8 22-JUL-01 24-JUL-01
1006 1 22-JUL-01 28-JUL-01
1012 1 22-JUL-01 23-JUL-01
1021 8 22-JUL-01 24-JUL-01
1023 1 22-JUL-01 28-JUL-01The query treats the one day as a range: 22-JUL-01 00:00:00 through 22-JUL-01 23:59:59. Thus, the query returns any order placed at any time during 22-JUL-01.
Another way to solve this problem of needing to ignore the time components in a DATE column is to truncate the date, and then compare the truncated result with the input literal:
SELECT order_nbr, cust_nbr, order_dt, expected_ship_dt
FROM disputed_orders
WHERE TRUNC(order_dt) = '22-JUL-01';
ORDER_NBR CUST_NBR ORDER_DT EXPECTED_
--------- ---------- --------- ---------
1001 1 22-JUL-01 23-JUL-01
1005 8 22-JUL-01 24-JUL-01
1006 1 22-JUL-01 28-JUL-01
1012 1 22-JUL-01 23-JUL-01
1021 8 22-JUL-01 24-JUL-01
1023 1 22-JUL-01 28-JUL-01The TRUNC function sets the time portion to the beginning of the day. Therefore, the equality comparison with the date literal '22-JUL-01' returns the expected output. The same result can be achieved by converting order_dt to a character string in a format matching that of the input data:
SELECT * FROM disputed_orders WHERE TO_CHAR(order_dt,'DD-MON-YY') = '22-JUL-01';
The downside to the approach of using the TRUNC and TO_CHAR functions is that the resulting query cannot make use of any index that happens to be on the order_dt column. This can have significant performance implications. On the other hand, the date range solution, while more complex to code, does not preclude the use of any index on the column in question.
|
You can use the same techniques shown in this section to SELECT data based on any given date range, even if that range spans more than just one day.
6.6.7 Creating a Date Pivot Table
For certain types of queries, it's helpful to have a table with one row for each date over a period of time. For example, you might wish to have one row for each date in the current year. You can use the TRUNC function in conjunction with some PL/SQL code to create such a table:
CREATE TABLE dates_of_year (one_day DATE);
Table created.
DECLARE
i NUMBER;
start_day DATE := TRUNC(SYSDATE,'YY');
BEGIN
FOR i IN 0 .. (TRUNC(ADD_MONTHS(SYSDATE,12),'YY') - 1) - (TRUNC(SYSDATE,'YY'))
LOOP
INSERT INTO dates_of_year VALUES (start_day+i);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
SELECT COUNT(*) FROM dates_of_year;
COUNT(*)
----------
365The dates_of_year table is now populated with the 365 days of the year 2001. You can now use this table to generate various useful lists of dates.
Let's say there are two paydays where you work?the 15th of each month and the last day of each month. Use the following query against the dates_of_year table to generate a list of all paydays in the year 2001:
SELECT one_day payday FROM dates_of_year WHERE TO_CHAR(one_day,'DD') = '15' OR one_day = LAST_DAY(one_day); PAYDAY --------- 15-JAN-01 31-JAN-01 15-FEB-01 28-FEB-01 15-MAR-01 31-MAR-01 15-APR-01 30-APR-01 15-MAY-01 31-MAY-01 15-JUN-01 30-JUN-01 15-JUL-01 31-JUL-01 15-AUG-01 31-AUG-01 15-SEP-01 30-SEP-01 15-OCT-01 31-OCT-01 15-NOV-01 30-NOV-01 15-DEC-01 31-DEC-01 24 rows selected.
Quite often you are told by a government organization that the processing of a document will take "x" number of days. When someone says something like that, they usually mean "x" number of working days. Therefore, to calculate the expected completion date, you need to count "x" days from the current date, skipping Saturdays and Sundays. Obviously, you can't use simple date arithmetic, because simple date subtraction doesn't exclude weekend days. What you can do is use the dates_of_year table. For example:
SELECT COUNT(*) FROM dates_of_year
WHERE RTRIM(TO_CHAR(one_day,'DAY')) NOT IN ('SATURDAY', 'SUNDAY')
AND one_day BETWEEN '&d1' AND '&d2';
Enter value for d1: 18-FEB-01
Enter value for d2: 15-MAR-01
old 3: AND one_day BETWEEN '&d1' AND '&d2'
new 3: AND one_day BETWEEN '18-FEB-01' AND '15-MAR-01'
COUNT(*)
----------
19This query counts the number of days between the two dates you enter, excluding Saturdays and the Sundays. The TO_CHAR function with the `DAY' format converts each candidate date (from the dates_of_year table) to a day of the week, and the NOT IN operator excludes the days that are Saturdays and Sundays. Notice the use of the RTRIM function with TO_CHAR. We used RTRIM because TO_CHAR produces the DAY as a nine-character string, blank padded to the right. RTRIM eliminates those extra spaces.
There could be holidays between two dates, and the queries shown in this section don't deal with that possibility. To take holidays into account, you need another table (perhaps named holidays) that lists all the holidays in the year. You can then modify the previous query to exclude days listed in the holidays table. Try this as an exercise.
6.6.8 Summarizing by a Date/Time Element
Let's say you want to print a quarterly summary of all your orders. You want to print the total number of orders and total sale price for each quarter. The order table is as follows:
SELECT * FROM cust_order; ORDER CUST SALES PRICE ORDER_DT EXPECTED_ CANCELLED SHIP STATUS ----- ---- ------ ------ --------- --------- --------- ---- ----------- 1001 1 7354 99 22-JUL-01 23-JUL-01 DELIVERED 1000 1 7354 19-JUL-01 24-JUL-01 21-JUL-01 CANCELLED 1002 5 7368 12-JUL-01 25-JUL-01 14-JUL-01 CANCELLED 1003 4 7654 56 16-JUL-01 26-JUL-01 DELIVERED 1004 4 7654 34 18-JUL-01 27-JUL-01 PENDING 1005 8 7654 99 22-JUL-01 24-JUL-01 DELIVERED 1006 1 7354 22-JUL-01 28-JUL-01 24-JUL-01 CANCELLED 1007 5 7368 25 20-JUL-01 22-JUL-01 PENDING 1008 5 7368 25 21-JUL-01 23-JUL-01 PENDING 1009 1 7354 56 18-JUL-01 22-JUL-01 DELIVERED 1012 1 7354 99 22-JUL-01 23-JUL-01 DELIVERED 1011 1 7354 19-JUL-01 24-JUL-01 21-JUL-01 CANCELLED 1015 5 7368 12-JUL-01 25-JUL-01 14-JUL-01 CANCELLED 1017 4 7654 56 16-JUL-01 26-JUL-01 DELIVERED 1019 4 7654 34 18-JUL-01 27-JUL-01 PENDING 1021 8 7654 99 22-JUL-01 24-JUL-01 DELIVERED 1023 1 7354 22-JUL-01 28-JUL-01 24-JUL-01 CANCELLED 1025 5 7368 25 20-JUL-01 22-JUL-01 PENDING 1027 5 7368 25 21-JUL-01 23-JUL-01 PENDING 1029 1 7354 56 18-JUL-01 22-JUL-01 DELIVERED 20 rows selected.
There is no quarter column in the cust_order table. You have to manipulate the order_dt column to generate the quarter. The following SQL statement does this using the TO_CHAR function along with a date format. In addition to being used in the SELECT list, notice that TO_CHAR is used in the GROUP BY clause to group the results by quarter:
SELECT 'Q'||TO_CHAR(order_dt, 'Q') quarter, COUNT(*),
SUM(NVL(sale_price,0))
FROM cust_order
GROUP BY 'Q'||TO_CHAR(order_dt, 'Q');
QU COUNT(*) SUM(NVL(SALE_PRICE,0))
-- ---------- ----------------------
Q3 20 788Using this same technique, you can summarize data by week, month, year, hour, minute, or any other date/time unit that you choose.
