Oracle Date function

Common Date-type functions
1.Sysdate Current date and time
Sql> Select sysdate from dual;

2.last_day last day of the month
Sql> Select Last_day (sysdate) from dual;

3.add_months (d,n) Current date d pushes n months
Used to increase or decrease some months from a date value

Sql> Select add_months (sysdate,2) from dual;

4.months_between (f,s) Date F and S difference between number of months
Sql> Select Months_between (sysdate,to_date (' 2005-11-12 ', ' Yyyy-mm-dd ')) from dual;

5.next_day (d, Day_of_week)
Returns the date of the first business day after the date specified by the variable "D", named after "Day_of_week". The parameter "Day_of_week" must be one of the day of the week.
Sql> SELECT next_day (to_date (' 20050620 ', ' YYYYMMDD '), 1) from dual;

6.current_date () returns the current date in the current session's time zone
Sql> select Sessiontimezone,current_date from dual;

Sql> alter session set Time_zone= ' -11:00 ' 2/
The session has changed.

Sql> select Sessiontimezone,current_timestamp from dual;


7.current_timestamp () returns the current date in the current session timezone with the timestamp with time zone data type
Sql> select Current_timestamp from dual;


8. Dbtimezone () return time zone
Sql> select Dbtimezone from dual;

9.extract () Find field values for date or interval values
Sql> Select Extract (month from sysdate) ' this month ' from dual;


Sql> Select Extract (Year from add_months (sysdate,*)) "Years" from dual;


10.localtimestamp () returns the date and time in the session
Sql> select Localtimestamp from dual;

Common Date data format (this paragraph is excerpt)

Y or yy or YYY last, two-bit or three-bit Select to_char (sysdate, ' YYY ') from dual; 002 = 2002
Syear or year syear to add a minus sign to the BC years Select to_char (sysdate, ' Syear ') from dual;-1112 = 111 2 BC
Q quarter, January-March for the first quarter Select to_char (sysdate, ' Q ') from dual; 2 for the second quarter ①
MM number of months Select to_char (sysdate, ' MM ') from dual; 12 = December
The Roman representation of the RM month is Select to_char (sysdate, ' RM ') from dual; IV = April
Month name in 9-character length for month Select to_char (sysdate, ' month ') from dual, and may followed by 6 spaces for May
WW the first few weeks of the year Select To_char (sysdate, ' WW ') from dual; 24 = June 13, 2002 is the 24th week
W this month the first few weeks Select to_char (sysdate, ' W ') from dual; October 1, 2002 is the 1th week
DDD year, January 1 is 001, February 1 for 032 Select to_char (sysdate, ' DDD ') from dual; 363 2002 1 February 2 9th for No. 363 Day
DD the day of the month Select To_char (sysdate, ' DD ') from dual; 04 October 4 is the 4th day
D Week the day of the first Select to_char (sysdate, ' D ') from dual; 5 March 14, 2002 for Monday
DY Week the first day abbreviation Select to_char (sysdate, ' DY ') from dual; SUN March 24, 2002 for Sunday
HH or HH12 12 input hours Select to_char (sysdate, ' HH ') from dual; 02 Midnight 2 O'Clock 8 02
HH24 24-hour Select to_char (sysdate, ' HH24 ') from dual; 14 2:08 P.M. to 14
MI minutes (0~59) Select to_char (sysdate, ' MI ') from dual; 17 4:17 P.M.
SS seconds (0~59) Select to_char (sysdate, ' SS ') from dual; 22 11 points 3 minutes 22 seconds
Note Do not use mm format for minutes (MI should be used in minutes). MM is the format used for the month, and it works for minutes, but the result is wrong.


Now give some practical uses:

1. Last month Day:
Sql> Select To_char (add_months (Last_day (sysdate), -1), ' Yyyy-mm-dd ') Lastday from
Dual

Lastday
----------
2012-11-30

2. Last month today
Sql> Select To_char (add_months (sysdate,-1), ' Yyyy-mm-dd ') pretoday from dual;


Pretoday
----------
2012-11-19
3. First day of last month
Sql> Select To_char (add_months (Last_day (sysdate) +1,-2), ' Yyyy-mm-dd ') FirstDay from dual;

FirstDay
----------
2012-11-01

4. According to weekly statistics
Sql> Select To_char (sysdate, ' WW ') from dual Group by To_char (Sysdate, ' ww ');


5. According to monthly statistics
Sql> Select To_char (sysdate, ' mm ') from the dual group by To_char (sysdate, ' mm ');


6. Statistics per quarter
Sql> Select To_char (sysdate, ' Q ') from the dual group by To_char (sysdate, ' Q ');

7. According to annual statistics
Sql> Select To_char (sysdate, ' yyyy ') from dual Group by To_char (Sysdate, ' yyyy ');


8. To find a specific date for all Friday of the month
Select To_char (T.D, ' Yy-mm-dd ') from (
Select Trunc (sysdate, ' MM ') +rownum-1 as D
From Dba_objects
where RowNum <) t
where To_char (T.D, ' mm ') = To_char (sysdate, ' mm ')--Find the Friday date of the current month

and Trim (To_char (T.D, ' Day ')) = ' Friday '
--------
11-05-02
11-05-09
11-05-16
11-05-23
11-05-30

If you change where To_char (T.D, ' mm ') = To_char (sysdate, ' mm ') to sysdate-90, you are looking for every Friday date in the first three months of the current month.

9.oracle Medium Time operation

The contents are as follows:
1, Oracle supports the operation of the date
2, the date is calculated in days as a unit
3, when it is necessary to calculate the value of smaller units, such as seconds, time-based conversion can be
4. Pay attention to parentheses when making time conversion, otherwise there will be problems

Sql> alter session set nls_date_format= ' Yyyy-mm-dd hh:mi:ss ';

The session has changed.

Sql> set Serverout on
Sql> Declare
2 DateValue date;
3 begin
4 Select Sysdate into DateValue from dual;
5 Dbms_output.put_line (' Source time: ' | | To_char (DateValue));
6 Dbms_output.put_line (' Source time minus 1 days: ' | | To_char (DateValue-1));
7 Dbms_output.put_line (' Source time minus 1 days 1 hours: ' | | To_char (DATEVALUE-1-1/24));
8 Dbms_output.put_line (' Source time minus 1 days 1 hours 1 minutes: ' | | To_char (datevalue-1-1/24-1/(24*60)));
9 Dbms_output.put_line (' Source time minus 1 days 1 hours 1 minutes 1 seconds: ' | | To_char (datevalue-1-1/24-1/(24*60) -1/(24*60*60)));
Ten end;
11/
SOURCE Time: 2012-11-29 11:53:41
Source time minus 1 days: 2012-11-28 11:53:41
Source time minus 1 days 1 hours: 2012-11-28 10:53:41
Source time minus 1 days 1 hours 1 minutes: 2012-11-28 10:52:41
Source time minus 1 days 1 hours 1 minutes 1 seconds: 2012-11-28 10:52:40

The PL/SQL process has completed successfully.


Implementing time-Additive processing in Oracle

Create or Replace function add_times (D1 in Date,newtime in date) return date
Is
HH number;
MM number;
SS number;
Hours number;
Dresult date;
Begin
--The following is taken out in turn, minutes, seconds
Select To_number (To_char (newtime, ' HH24 ')) into HH from dual;
Select To_number (To_char (newtime, ' MI ')) into mm from dual;
Select To_number (To_char (newtime, ' SS ')) into the SS from dual;
--Calculate the sum of the hours in the NewTime, in a day's hundred minutes
Hours: = (hh + (MM/60) + (ss/3600))/24;
--to get the result after the sum of time
Select D1 + hours into Dresult from dual;
return (Dresult);
End Add_times;


--Test Cases
--Select Add_times (sysdate,to_date (' 2012-12-19 16:23:00 ', ' yyyy-mm-dd HH24:MI:SS ')) from dual


Calculating the time difference in oracle9i
Calculating the time difference is a common problem for Oracle data types. Oracle supports date calculations, and you can create expressions such as "date 1-date 2" to calculate the time difference between the two dates.


Once you have found the time difference, you can use simple techniques to calculate the difference in days, hours, minutes, or seconds. In order to get poor data, you have to select the appropriate time unit of measurement so that you can hide the data format.

Using sophisticated conversion functions to transform dates is a temptation, but you will find that this is not the best solution.

Round (To_number (end-date-start_date))-Elapsed time (in days)

Round (To_number (end-date-start_date) *24)-Elapsed time (in hours)

Round (To_number (end-date-start_date) *1440)-Elapsed time (in minutes)

What is the default mode for displaying the time difference? To find the answer to this question, let's make a simple SQL *plus query.

Sql> Select sysdate-(sysdate-3) from dual;

sysdate-(SYSDATE-3)
-------------------
3

Here, we see Oracle using days as the unit of elapsed time, so we can easily convert it to hours or minutes using a conversion function. However, when the number of minutes is not an integer, we encounter the problem of placing a decimal point.

Select
(sysdate-(sysdate-3.111)) *1440
From
Dual

(sysdate-(SYSDATE-3.111)) *1440
------------------------------
4479.83333

Of course, we can use the round function (that is, the rounding function) to solve this problem, but remember that we must first convert the date data type to the number data type.

Select
Round (To_number (sysdate-(sysdate-3.111)) *1440)
From
Dual

ROUND (To_number (sysdate-(SYSDATE-3.111)) *1440)
----------------------------------------------
4480

We can use these functions to approximate a elapsed time into minutes and write this value to the Oracle table. In this example, we have an off-line (logoff) system-level trigger mechanism to calculate the session time that has already started and put it into an Oracle statspack user_log extension table.

Update
Perfstat.stats$user_log
Set
Elapsed_minutes =
Round (To_number (logoff_time-logon_time) *1440)
where
user = user_id
and
Elapsed_minutes is NULL;

Oracle Date function