Oracle date Plus and minus operations

--Start

We all know that numbers can be added, minus, multiply, divide, and so on. So, could the date be? The answer is that the date can only be added, minus operations.

Before starting the operation date, let's look at what date data types are supported by Oracle, as follows:

[SQL]View PlainCopy

1. DATE
2. TIMESTAMP
3. TIMESTAMP with time ZONE
4. TIMESTAMP with LOCAL time ZONE
5. INTERVAL Day to SECOND
6. INTERVAL year to MONTH

Next, let's look at a simple example, as follows:

[SQL]View PlainCopy

1. Select Sysdate + INTERVAL ' 1 ' Day , Sysdate-interval ' 2 ' Day from dual;
2. Select Systimestamp + INTERVAL ' 1 ' Day , Systimestamp-interval ' 2 ' Day from dual;

As shown above, we can calculate the date by adding a time interval to the date, and here are more examples.

[SQL]View PlainCopy

2. Date + 1 sysdate + INTERVAL ' 1 ' year
4. Date + January Sysdate + INTERVAL ' 1 ' MONTH
6. Date + 1st Sysdate + INTERVAL ' 1 ' Day
8. Date + 1 o'clock sysdate + INTERVAL ' 1 ' HOUR
10. Date + 1 minutes sysdate + INTERVAL ' 1 ' MINUTE
12. Date + 1 seconds sysdate + INTERVAL ' 1 ' SECOND
14. Date + 1st 1:1 sysdate + INTERVAL ' 1 1:1 ' Day to MINUTE

If the DATE type is calculated, we can also use the following method.

[SQL]View PlainCopy

1. date+ 1st Sysdate + 1
2. Date+ 1 O'Clock Sysdate + 1/(1*24)
3. date+ 1 min sysdate + 1/(1*24*60)
4. Date+ 1 sec sysdate + 1/(1*24*60*60)
5. date+ 1st 1:1 sysdate + 1 + 1/(1*24) + 1/(1*24*60)

Sometimes we need to know how many days apart between two dates, that is, the time interval between dates, what should we do? It is natural for us to think of subtracting the two dates as follows:

[SQL]View PlainCopy

2. Select (date ' 2015-04-10 '- date ' 2014-03-11 ') from dual
4. Select (date ' 2015-04-10 '- date ' 2014-03-11 ') year to MONTH from dual
6. Select (date ' 2015-04-10 '- date ' 2014-03-11 ') Day (3) to SECOND from dual
10. Select (timestamp ' 2015-04-10 14:52:19.000 '- timestamp ' 2014-03-11 13:55:29.000 ') from dual
12. Select (timestamp ' 2015-04-10 14:52:19.000 '- timestamp ' 2014-03-11 13:55:29.000 ') year to MONTH from dual
14. Select (timestamp ' 2015-04-10 14:52:19.000 '- timestamp ' 2014-03-11 13:55:29.000 ') Day (3) To SECOND from dual

Once we have the time interval, we can extract the components of the time interval through the EXTRACT function.

[SQL]View PlainCopy

2. Select EXTRACT (Year from (date ' 2015-04-10 ' – date ' 2014-03-11 ') year to MONTH) from dual
4. Select EXTRACT ( date ' 2015-04-10 '- date ' 2014-03-11 ') year to MONTH) from dual
6. Select EXTRACT ( date ' 2015-04-10 '- date ' 2014-03-11 ') Day (3) to SECOND) from dual

We can also pull out the following sections.

[SQL]View PlainCopy

1. Year
2. MONTH
3. Day
4. HOUR
5. MINUTE
6. SECOND
7. Timezone_hour
8. Timezone_minute
9. Timezone_region
10. Timezone_abbr

--See also: Oracle SQL extract

-- declaration: Reprint please indicate the source

--Last edited on 2015-06-19

--Created by Shangbo on 2015-03-10

--End

Oracle date Plus and minus operations