Plsql Common time functions

<title>Plsql Common time functions</title>

Plsql Common time Functions

One, get time

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
Date_value:=add_months (date_value,number_of_months)
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;
Months_between (sysdate,to_date (' 2005-11-12 ', ' yyyy-mm-dd '))
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
Date_value:=current_date
sql> column Sessiontimezone for A15
Sql> select Sessiontimezone,current_date from dual;
Sessiontimezone Current_da
--------------- ----------
+08:00 1 March-November-03
Sql> alter session set Time_zone= ' -11:00 ' 2/
The session has changed.
Sql> select Sessiontimezone,current_timestamp from dual;
Sessiontimezone Current_timestamp
--------------- ------------------------------------
-11:00 December-November-03 04.59.13.668000 pm -11:00
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;
Current_timestamp
---------------------------------------------------------------------------
2 January-June-05 10.13.08.220589 am +08:00
8. Dbtimezone return time zone
Sql> select Dbtimezone from dual;
DBTIME
------
-08:00
9. Extract () Find field values for date or interval values
Date_value:=extract (Date_field from [Datetime_value|interval_value])
Sql> Select Extract (month from sysdate) ' this month ' from dual;
This Month
----------
6
Sql> Select Extract (Year from add_months (sysdate,36)) "Years" from dual;
Years
----------
2008
10. Localtimestamp () returns the date and time in the session
Sql> select Localtimestamp from dual;
Localtimestamp
---------------------------------------------------------------------------
2 January-June-05 10.18.15.855652 am
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 day of the month:
Sql> Select To_char (add_months (Last_day (sysdate), -1), ' Yyyy-mm-dd ') Lastday from dual;
Lastday
----------
2005-05-31
2. Last month today
Sql> Select To_char (add_months (sysdate,-1), ' Yyyy-mm-dd ') pretoday from dual;
Pretoday
----------
2005-05-21
3. First day of last month
Sql> Select To_char (add_months (Last_day (sysdate) +1,-2), ' Yyyy-mm-dd ') FirstDay from dual;
FirstDay
----------
2005-05-01
4. According to weekly statistics
Sql> Select To_char (sysdate, ' WW ') from dual Group by To_char (Sysdate, ' ww ');
To
--
25
5. According to monthly statistics
Sql> Select To_char (sysdate, ' mm ') from the dual group by To_char (sysdate, ' mm ');
To
--
06
6. Statistics per quarter
Sql> Select To_char (sysdate, ' Q ') from the dual group by To_char (sysdate, ' Q ');
T
-
2
7. According to yearly statistics
Sql> Select To_char (sysdate, ' yyyy ') from dual Group by To_char (Sysdate, ' yyyy ');
To_c
----
2005
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 '
--------
03-05-02
03-05-09
03-05-16
03-05-23
03-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: 2003-12-29 11:53:41
Source time minus 1 days: 2003-12-28 11:53:41
Source time minus 1 days 1 hours: 2003-12-28 10:53:41
Source time minus 1 days 1 hours 1 minutes: 2003-12-28 10:52:41
Source time minus 1 days 1 hours 1 minutes 1 seconds: 2003-12-28 10:52:40
The PL/SQL process has completed successfully.
Implementing time-Additive processing in Oracle
--Name: Add_times
--Function: Returns the result of the addition of D1 and newtime to add time
--Description: No consideration for dates in NewTime
--Date: 2004-12-07
--Version: 1.0
--Kevin
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 (' 2004-12-06 03: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;
Find out which days are included in any month
CREATE OR REPLACE FUNCTION get_workingdays (
NY in VARCHAR2
) RETURN INTEGER is

Result INTEGER;
BEGIN
SELECT COUNT (*) into Result
From (SELECT mod (q.rq-to_date (' 2001-12-30 ', ' yyyy-mm-dd '), 7), 7) weekday
From (SELECT to_date (ny| | T.DD, ' YYYYMMDD ') RQ
From (SELECT substr (100+rownum,2,2) DD
From Ljrq z WHERE rownum<=31
) T
WHERE To_date (ny| | T.DD, ' YYYYMMDD ')
Between To_date (NY, ' yyyymm ')
and Last_day (To_date (NY, ' yyyymm '))
) Q
) A
WHERE A.weekday not in (0,6);
RETURN Result;
END get_workingdays;
______________________________________
There is also a version
CREATE OR REPLACE FUNCTION get_workingdays (
NY in VARCHAR2
) RETURN INTEGER is

Result INTEGER: = 0;
Myts INTEGER; -The number of days of the given month
SCTs INTEGER; -The number of days away from 2001-12-30
RQ DATE;
DJT INTEGER: = 1; --
BEGIN
Myts: = To_char (Last_day (To_date (NY, ' yyyymm ')), ' DD ');
LOOP
RQ: = to_date (ny| | SUBSTR (100+djt,2), ' YYYYMMDD ');
SCTs: = Rq-to_date (' 2001-12-30 ', ' yyyy-mm-dd ');
IF mod (mod (scts,7) +7,7) not in (0,6) then
Result: = result + 1;
END IF;
DJT: = DJT + 1;
EXIT when djt>myts;
END LOOP;
RETURN Result;
END get_workingdays;
Comparison of the above two versions
The first version of a SQL statement can produce results, without programming to achieve the goal. However, you need to use any one table or view that has access to at least 31 of the number of records.
The second version requires programming, but does not require a table or view.
These two versions all still exist need to perfect place, namely did not consider festivals, such as 51, Xi., New Year's Day, Spring Festival These holidays are not removed. These holidays should be maintained as a table, and then through the table to remove these holidays.

From for notes (Wiz)

Plsql Common time functions