Oracle Database date + 1 reprint

Http://blog.csdn.net/yjp198713/article/details/18131871oracle two minutes, hours of direct time.

1, obtain the time difference millisecond number:

Select Ceil ((to_date (' 2008-05-02 00:00:00 ', ' yyyy-mm-dd hh24-mi-ss ')-to_date (' 2008-04-30 23:59:59 ', ' Yyyy-mm-dd hh24- Mi-ss ') * 24 * 60 * 60 * 1000) difference between Hao seconds from DUAL;
Number of seconds of difference
----------
86401000

2, the number of seconds to obtain the difference:

Select Ceil ((to_date (' 2008-05-02 00:00:00 ', ' yyyy-mm-dd hh24-mi-ss ')-to_date (' 2008-04-30 23:59:59 ', ' Yyyy-mm-dd hh24- Mi-ss ') * 24 * 60 * 60) difference in seconds from DUAL;
Number of seconds difference
----------
86401

3, get the difference in minutes, hours, and so on, the default time subtraction to obtain the difference in the number of days

4, the number of months to obtain a difference

Select (EXTRACT (to_date (' 2009-05-01 ', ' yyyy-mm-dd '))-EXTRACT (year from to_date (' 2008-04-30 ', ' yyyy-mm-dd ')) ) * 12 +
EXTRACT (Month from to_date (' 2008-05-01 ', ' yyyy-mm-dd '))-EXTRACT (Month from to_date (' 2008-04-30 ', ' yyyy-mm-dd ')) Months
from dual;
MONTHS
----------
13

You can use the Months_between function directly here

5. Year of difference

Select EXTRACT (Year from to_date (' 2009-05-01 ', ' yyyy-mm-dd '))-EXTRACT (year from to_date (' 2008-04-30 ', ' yyyy-mm-dd ')) years from dual;
Years
----------
1

6, the method of obtaining time:

Select Sysdate,add_months (sysdate,12) from dual; --plus 1 years
Select Sysdate,add_months (sysdate,1) from dual; --Add January
Select Sysdate,to_char (sysdate+7, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 weeks
Select Sysdate,to_char (sysdate+1, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 days
Select Sysdate,to_char (sysdate+1/24, ' Yyyy-mm-dd HH24:MI:SS ') from dual; --plus 1 hours
Select Sysdate,to_char (sysdate+1/24/60, ' Yyyy-mm-dd HH23:MI:SS ') from dual; --plus 1 minutes
Select Sysdate,to_char (sysdate+1/24/60/60, ' Yyyy-mm-dd HH23:MI:SS ') from dual; --plus 1 seconds

Select sysdate+7 from dual; --Plus 7 days

Convert current date to last one months

SELECT To_char (Add_months (Sysdate,-1), ' yyyymm ')--Gets the first one months of the current time
From DUAL;

Select Sysdate from dual; /** get current time to seconds **/
Select sysdate-3 from dual;/** get current 2 day **/
Select Round (sysdate) as format date from dual;
Select To_date (' 2008-9-2 ', ' YYYY_MM_DD ') as formatted as date from dual;
SELECT To_char (Add_months (Sysdate,-1), ' yyyymm ') from DUAL; /**--gets the first one months of the current time and is backward **/
Select Last_day (sysdate) from dual;/** one day this month **/
/*** the year, month and day of the time respectively ***/
Select to_char (sysdate, ' YYYY ') from dual;
Select To_char (sysdate, ' mm ') from dual;
Select To_char (sysdate, ' DD ') from dual;

A moment is reproduced jenry-Yunfei Yang:

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
/*------------------------------------------------------------------------------------------
Function Name: get_workingdays
How many working days does the CPC have in a certain month?
Author Name: xingping
Write Time: 2004-05-22
Input parameters: NY: The number of days of the day, the format of yyyymm, such as 200405
Return value: Integer value that contains the number of working days.
Algorithm Description:
1). Enumerate the parameters given each day of the month. A table is used here (LJRQ is a table in my library.) This table can be any table or view that has access, at least 31 records, to construct each day of the month.
2). Subtract these dates from the date of a known day of the week (2001-12-30 is Sunday), and the resulting difference is 7 modulo. If the date of the year before 2001-12-30, then the difference is not only negative, the value of the modulus after the range is greater than-6, less than 0, such as-1 for the Saturday, it is first to find the results of the Model plus 7, and then ask for 7 modulo.
3). Filter out the elements of the result set with values of 0 and 6, and then count, which is the number of workdays.
-------------------------------------------------------------------------------------------------*/
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
/*-----------------------------------------------------------------------------------------
Function Name: get_workingdays
How many working days does the CPC have in a certain month?
Author Name: xingping
Write Time: 2004-05-23
Input parameters: NY: The number of days of the day, the format of yyyymm, such as 200405
Return value: Integer value that contains the number of working days.
Algorithm Description: Use the Last_day function to calculate the number of days a parameter is given, and construct a loop based on this value. In this cycle, the difference between each day of the month and a date that is known to be Sunday (2001-12-30 is Sunday) is first calculated, and then 7 is modeled. If the date of the 2001-12-30, then the difference is not only negative, the value of the modulus after the range is greater than 6, less than 0, such as 1 for Saturday, it is first to find the results of the Model plus 7, and then ask for 7 modulo. If the earned value is not equal to 0 and 6 (that is, not Saturday and Sunday), then one working day is counted.
----------------------------------------------------------------------------------------*/
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.

Oracle Database date + 1 reprint