1.
Oracle内在的时间存储格式:
Oracle's DATE datatype holds date as well as time information. Regardless of the date format we use, Oracle stores dates internally in one standard format. Internal to the database a date is a fixed-length, seven-byte field. The seven bytes represent the following pieces of information:
.The Century
.The Year
.The Month
.The Day
.The Hour
.The Minute
.The Second
2.在数据库中获取或输出日期数据:
A
.to_data函数:TO_DATE(string [,format])
Specifying a date format is optional. When we don't specify a format, the input string is assumed to be in the default date format (
specified by the NLS_DATE_FORMAT parameter setting).
We can convert a number to a DATE using TO_DATE. When we supply a number to the TO_DATE function, Oracle implicitly converts the input number into a string, and then the resulting string gets passed as input to TO_DATE
If we wish to specify a date format, there are at least two approaches we can take:
1). Specify the format at the session level, in which case it applies to all implicit
conversions, and to all
TO_DATE conversions for which we do not explicitly specify some other format.
TO_DATE conversions for which we do not explicitly specify some other format.
2). Specify the format as a parameter to a TO_DATE call.
如果我们想指定日期的格式,我们至少可以有两个方式实现这个目的:
1).在会话级别指定日期的格式,这种情况下,它会对所有隐式的转换起作用,对于没有明确指
定格式的
to_date函数调用也会起作用。
2).在 to_date函数调用中指定日期格式
The following example changes the default date format for the session, and then uses TO_DATE to convert a number to date.
例
1:
ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYY';
INSERT INTO EMPLOYEE(EMP_ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE)
VALUES (2304, 'John', 'Smith', 20, 1258, 20000, TO_DATE(102299));
Since the default date format has been changed prior to the conversion, the conversion function TO_DATE doesn't need the date format as an input parameter.(
因为默认的日期格式已经被设置成'MMDDYY',所以to_date函数无需包含指定日期格式的参数)
例
2:
If we attempt this insert without setting the default date format to match the format of the date in the input string, we get an error when Oracle tries to convert the date:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY';
INSERT INTO EMPLOYEE (EMP_ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE)
VALUES (2304, 'John', 'Smith', 20, 1258, 20000, TO_DATE('102299'));
(2304, 'John', 'Smith', 20, 1258, 20000, TO_DATE('102299'))
ERROR at line 4:
ORA-01861: literal does not match format string(
因为to_date函数的日期格式和会话级别的默认格式不同,所以不能省略指定日期格式的参数)
例
3:
In such situations, if we do not wish to change our session's default date format, we must specify the date format as the second input parameter to the TO_DATE function:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY';
INSERT INTO EMPLOYEE (EMP_ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE)
VALUES (2304, 'John', 'Smith', 20, 1258, 20000,
TO_DATE('102299','MMDDYY'));
SELECT * FROM EMPLOYEE;
EMP_ID FNAME
LNAME DEPT_ID MANAGER_EMP_ID SALARY HIRE_DATE
------- ------- ------- ---------- -------------- ---------- ---------
2304 John
Smith 20 1258 20000 22-OCT-99
SELECT SENT_TO, REPORT_ID,
TO_DATE(REPORT_ID,'MMDDYYYY') DATE_GENERATED
FROM REPORT;
SENT_TO
REPORT_I DATE_GENE
---------------------------- ---------
Manager
01011999 01-JAN-99
Note how TO_DATE interprets the string '102299' as being in the format 'MMDDYY'. Also note that in the result of the SELECT, the date is displayed using the default date format of the session, not the format in which it was inserted.
(
因为to_date函数的日期和会话级别的默认格式不同,所以在to_date函数必须显式地指定日期的格式。但是在SELECT语句中,日期的输出是会话级别的默认格式输出的,如果要想按照之前插入数据时的格式原样输出,只能在to_date函数显式地指定输出格式)
B
.to_char函数:TO_CHAR(date [,format])
The TO_CHAR function is the opposite of the TO_DATE function, and converts a date into a string of characters。
There are situations when we may need to combine TO_CHAR with TO_DATE. For example, if we want to know on what day of the week January 1, 2000 fell, we can use the following query:
SELECT TO_CHAR(TO_DATE('01-JAN-2000','DD-MON-YYYY'),'Day') AS DAY FROM DUAL;
DAY
---------
Saturday
Oracle 的时间格式
Day
|
DD(Day of the month)
|
MM/DD/YY
|
10/03/01
|
|
DDD(Day of the year)
|
DDD/YY
|
276/01
|
|
D(Day of the week)
|
D MM/YY
|
4 10/01
|
|
DAY(Name of the day)
|
DAY MM/YY
|
WEDNESDAY 10/01
|
Month
|
MM(Two digit month)
|
MM/DD/YY
|
10/03/01
|
Year
|
YYYY(Four digits of year)
|
MM YYYY
|
10 2001
|
Quarter
|
Q(Quarter of the year)
|
Q
|
4
|
If we have used the AM format in our query, and the time we are applying this format to comes out to be a PM time, Oracle is intelligent enough to print PM instead of AM, and vice versa.
(
假如我们在时间格式中指定了AM格式,但给出的时间实际上是PM类型的,Oracle会自动将输出的时间中转成PM,反过来也是)
For example:
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS AM'),
TO_CHAR(SYSDATE, 'HH:MI:SS PM'),
TO_CHAR(SYSDATE - 8/24, 'HH:MI:SS AM'), --当前时间减去
8小时
TO_CHAR(SYSDATE - 8/24, 'HH:MI:SS PM')
FROM DUAL;
TO_CHAR(SYS TO_CHAR(SYS TO_CHAR(SYS TO_CHAR(SYS
----------- ----------- ----------- -----------
06:58:07 PM 06:58:07 PM 10:58:07 AM 10:58:07 AM
可以看到,由于当前的时间是下午时间,所以即使在
TO_CHAR中使用了AM,Oracle也会自动地转换成PM
3.日期计算:
Date functions
Function
|
Use
|
ADD_MONTHS
|
Adds months to a date
|
LAST_DAY
|
Computes the last day of the month
|
MONTHS_BETWEEN
|
Determines the number of months between two dates
|
NEW_TIME
|
Translates a time to a new time zone
|
NEXT_DAY
|
Returns the date of the next specified weekday
|
ROUND
|
Rounds a date/time value to a specified element
|
SYSDATE
|
Returns the current date and time
|
TO_CHAR
|
Converts dates to strings
|
TO_DATE
|
Converts strings and numbers to dates
|
TRUNC
|
Truncates a date/time value to a specific element
|
A
.日期的加运算:
Adding two dates doesn't make sense. However, we can add days, months, years, hours, minutes, and seconds to a date to generate a future date and time. The "+" operator allows us 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, we can add 1 to SYSDATE(
对于日期的加操作默认是已日为单位的,为了获取明天的日期,我们可以向SYSDATE加上1)
The following 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:
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
SELECT ADD_MONTHS('31-DEC-99',6) FROM DUAL;
ADD_MONTH
---------
30-JUN-00
B
.日期的减运算:
Subtracting one date from another date returns the number of days between those two dates. Subtracting a number from a date returns a date that number of days in the past.
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
MONTHS_BETWEEN (date1, date2)
The syntax elements are:
date1
Specifies the end of the time period in question. This should be either a DATE value or a string in the default date format.
date2
Specifies the beginning of the time period in question. Like date1, this should also be a DATE value or a string in the default date format.
MONTHS_BETWEEN subtracts date2 from date1. So, if date2 comes later than date1 in the chronology, then MONTHS_BETWEEN will return a negative value.
There is no YEARS_BETWEEN function. To find the number of years between two dates, we 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. 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.
C
.每月的最后一天和第一天:
LAST_DAY returns the last day of the month containing the input date.(Last_day函数返回包含参数
date所在月份的最后一天) For example, to find the last date of the current month, we 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 following expression:
ADD_MONTHS((LAST_DAY(date)+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 we started. The result is the first day of the month in which the given date falls.
(
该表达式首先返回date所在月份的最后一天,然后通过加1得到下一个月份的第一天,然后使用Add_month函数向后倒推一个月,就得到了date所在月份的第一天)
D
.下一个星期×:
Oracle provides a built-in function to get the date of the next occurrence of a specified day of the week. The function is NEXT_DAY, and it's called as follows:
NEXT_DAY (date, string),
The syntax elements are:
date
Specifies a DATE value, or a string with a date in the default date format.
string
Specifies the name of a weekday.
To find the date of the next Friday, we can use the following SQL statement:
SELECT NEXT_DAY(SYSDATE, 'Friday') "Vacation Start Date" FROM DUAL;
Vacation
---------
12-OCT-01
E
.对日期进行舍入和截取:
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 depends upon the specified format, which is an optional parameter. If we don't specify a format in the call to ROUND, the function returns a date by rounding the input to the nearest day. If we don't specify a format in the call to TRUNC, that function returns a date by removing the fractional part of the day.
(
这两个函数的返回值取决于指定的日期格式,这个日期格式参数是一个可选参数。如果我们不向Round函数指定返回的日期格式,该函数返回一个和当前日期最接近的日期。如果我们不向Trunc函数指定返回的日期格式,该函数返回一个被截取了时间部分的日期)
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).
(当使用Round和Trunc函数对日期进行舍入和截取时,这两个函数会将时间字段的值设置成返回值时间的起始点,例如12:00:00 AM或者24小时制形式的00:00:00)
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 AM
Notice 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 AM
Since 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.
(因为时间已经超过中午的12点,距离第二天的时间比距离当天起始时间要近,所以Round函数返回第二天的起始时间,但是Trunc函数依然返回输入时间的起始部分,这一点和对小数进行四舍五入和截取相似。对4.7进行Round操作返回5,对4.7进行Trunc操作返回4)
When we 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 we specify. For example, if we specify the format as YYYY, the input date will be truncated based on the year, which means that if the input date is before the middle of the year (July 1st), both ROUND and TRUNC will return the first day of the year. If the input date is after July 1st, 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:00
F
.New_Time函数:
you need to deal with time zones in the database, Oracle's built-in NEW_TIME function comes to your rescue. It converts a date and time in a given time zone into a date and time in another time zone. Call NEW_TIME as follows:
NEW_TIME (date, input_time_zone, output_time_zone),The syntax elements are:
date
Specifies a literal, PL/SQL DATE variable, or a database column of DATE datatype.
input_time_zone
Specifies the name of the input time zone (as a string).
output_time_zone
Specifies the name of the output time zone (as a string).
SELECT NEW_TIME('11-NOV-01 09:00:00 AM','EST','PST') FROM DUAL;
NEW_TIME('11-NOV-0109
---------------------
11-NOV-01 06:00:00 AM
In this example, EST and PST correspond to Eastern Standard Time and Pacific Standard Time, respectively.
G
.在日期范围内选择日期:
There are times when we need to SELECT data from a table based on a given date range. Let's say you have been asked to print all orders placed on a given date, say 22-MAY-01. Probably, your immediate response would be a query such as the following:
SELECT * FROM CUST_ORDER
WHERE ORDER_DT = '22-MAY-01';
no rows selected
There's no output. Surprised? Although you know there are orders on 22-MAY-01, this query didn't return any rows. T
he 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-MAY-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 CUST_ORDER 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-MAY-01 is:
SELECT * FROM CUST_ORDER
WHERE ORDER_DT BETWEEN TO_DATE('22-MAY-01 00:00:00','DD-MON-YY HH24:MI:SS')
AND TO_DATE('22-MAY-01 23:59:59','DD-MON-YY HH24:MI:SS');
ORDER_NBR CUST SALES_EMP SALE_PRICE ORDER_DT EXPECTED_ CANCELLED SHIP STATUS
---------- ---- --------- ---------- --------- --------- --------- ---- ---------
1001 1 3 99 22-MAY-01 23-MAY-01 DELIVERED
1005 8 3 99 22-MAY-01 24-MAY-01 DELIVERED
1021 8 7 99 22-MAY-01 24-MAY-01 DELIVERED
The query treats the one day as a range: 22-MAY-01 00:00:00 to 22-MAY-01 23:59:59. Thus, the query returns any order placed at any time during 22-MAY-01.
Another way to solve this problem of needing to ignore the time components in a DATE column would be to truncate the date, and then compare the truncated result with the input literal:
SELECT * FROM CUST_ORDER
WHERE TRUNC(ORDER_DT) = '22-MAY-01';
ORDER_NBR CUST SALES_EMP SALE_PRICE ORDER_DT EXPECTED_ CANCELLED SHIP STATUS
---------- ---- --------- ---------- --------- --------- --------- ---- ---------
1001 1 3 99 22-MAY-01 23-MAY-01 DELIVERED
1005 8 3 99 22-MAY-01 24-MAY-01 DELIVERED
1021 8 7 99 22-MAY-01 24-MAY-01 DELIVERED
The TRUNC function sets the time portion to the beginning of the day. Therefore, the equality comparison with the date literal '22-MAY-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 CUST_ORDER
WHERE TO_CHAR(ORDER_DT,'DD-MON-YY') = '22-MAY-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.(现在使用
Trunc和To_char方法有下降趋势的一个原因就是假如查询列上刚好建有索引的话,使用这两个函数,其查询的结果集不能使用任何索引)
Oracle8i and higher support the use of function-based indexes, which, if created correctly, allow for the use of indexes even when functions are applied to columns.
(Oracle8i
或更高的版本支持基于函数的索引,如果创建正确的话,当函数作用与列的时候通用也可以使用索引)
H
.筛选日期:
实例:假设每月有两天为支付薪水的时间,一天为
15号,一天为月底。现在要算出在某段时间内所有的支付薪水的时间:
解决方案:
1).创建环境:
CREATE TABLE DATES_OF_YEAR (ONE_DAY DATE);
2).插入数据:
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.
这段代码的作用是向表中插入当年的所有日期。首先使用
Trunc(sysdate,’yy’)截取当年的年份,接着使用TRUNC(ADD_MONTHS(SYSDATE,12),'YY')求出下一年的年份,减去1则得到今年的最后一天。再减去TRUNC(SYSDATE,'YY')则得到今年的日期数
INSERT INTO DATES_OF_YEAR VALUES (START_DAY+I)用于向数据库中插入每一天的日期
3).初步筛选:
SELECT ONE_DAY PAYDAY FROM DATES_OF_YEAR
WHERE TO_CHAR(ONE_DAY,'DD') = '15'
--筛选每月
15号和每月月底的记录
OR ONE_DAY = LAST_DAY(ONE_DAY);
4).过滤特别日期:
考虑到所查询的结果中有可能包含了周六、日,所以我们必须把结果中的这些日期过滤掉:
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(*)
----------
19
This 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, with blank padded to the right. RTRIM eliminates those extra spaces. (
注意在To_Char函数的外面我们使用了Rtrim函数,为什么呢?因为To_Char函数对日期转换的结果是一个带有空格字符填充的9位字符串,Rtrim对这些空格进行了过滤)
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.
I
.按日期进行统计:
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.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'), COUNT(*), SUM(NVL(SALE_PRICE,0))
FROM CUST_ORDER
GROUP BY 'Q'||TO_CHAR(ORDER_DT, 'Q');
To_char(ORDER_DT,’Q’)
:获得订购日期的季度值
QU
COUNT(*) SUM(NVL(SALE_PRICE,0))
-- ---------- ----------------------
Q1
4 158
Q2
7 490
Q3
2 0
Q4
7 140
Using this same technique, you can summarize data by week, month, year, hour, minute, or any other date/time unit that you choose.