Pay attention to the problem of calculating the time value of a date caused by a month or a month.

Pay attention to one issue when calculating the value of a DATE caused by a month or a problem */SQL> www.2cto.com SQL> SELECT date' 2012- 30th day of last month, DATE '2017-11-30 'Today, add_months
(DATE '1970-10-30 ', 1) increased by 2012 last month, 2 add_months (DATE '1970-11-30',-1) decreased by 2012 FROM dual this month; in August, the previous month was increased, and in August, this month was reduced. In August, ------------------ 2012-10-30 2012-11-30 2012-11-30 2012-11-30 2012-10-31, The results showed that the value of 10-30 plus 1 month was 11-30, the result of a one-month reduction of 11-30 is 10-31, that is, the 30 day of the big month.
The result of adding one month is the 30 day of the small month, and the result of reducing one month is the 31 day of the large month. It is reasonable to calculate the date of oracle, but it is worth noting that indicators such as year-on-year computing are being developed,
See the following example: SQL> SELECT id, pub_date FROM mm; ID PUB_DATE begin ----------- 1 2012-11-30 2 2012-10-31 3 2012-10-30 SQL> SELECT m1.pub _ date m1date, m2.pub _ date m2date, m1.id/m2.id 2 FROM mm m1, mm m2 3 WHERE add_months (m2.pub _ date,-1) = m1.pub _ date; m1DATE M2DATE M1.ID/M2.ID ----------------- ----------- 2012-10-31 2012-11-30 2 only has one record, compared with SQL> SELECT region _ date m1date, Region _ date m2date, m1.id/m2.id 2 FROM mm m1, mm m2 3 WHERE partition (m1.pub _ date, 1) = m2.pub _ date; M1DATE M2DATE M1.ID/M2.ID ----------- ----------------- 2012-10-30 2012-11-30 32012-10-31 2012-11-30 2 generates two records, 11-30 corresponds to 10-30, 10-31, since the size of the month will cause this problem, then encounter
This is also the case for the Ping-leap month in February. Www.2cto.com -- insert some test data SQL> SELECT id, pub_date FROM mm into the table; ID PUB_DATE hour ----------- 1 2012-11-30 2 2012-10-31 4 2012-1-28 5 2012-1-29 6 2012-1-30 7 2012-1-31 8 2012-2-28 9 2012-2-29 3 2012-10-30 9 rows selected SQL> SELECT region _ date m1date, Region _ date m2date, m1.id/m2.id 2 FROM mm m1, mm m2 3 WHERE add_months (m2.pub _ date,-1) = m1.pub _ date AND m2.id = 8; M 1 DATE M2DATE M1.ID/M2.ID ----------- --------------- 2012-1-28 2012-2-28 0.5 SQL> SELECT m1.pub _ date m1date, m2.pub _ date m2date, m1.id/m2.id 2 FROM mm m1, mm m2 3 WHERE add_months (m1.pub _ date, 1) = m2.pub _ date AND m2.id = 8; m1DATE M2DATE M1.ID/M2.ID ----------------- ----------- 2012-1-28 2012-2-28 0.5 date only one entry is used for calculation on January 28, regardless of the size of the month. Then, let's look at the 29-day situation: SQL> SELECT m1.pub _ date m1date, m2.pub _ date m2 Date, m1.id/m2.id 2 FROM mm m1, mm m2 3 WHERE add_months (m2.pub _ date,-1) = m1.pub _ date AND m2.id = 9; m1DATE M2DATE M1.ID/M2.ID ----------------- ----------- 2012-1-31 2012-2-29 0.777777777 2012-2-29 the previous month is, and there is only one record, which is consistent with the above test results. SQL> SELECT m1.pub _ date m1date, m2.pub _ date m2date, m1.id/m2.id 2 FROM mm m1, mm m2 3 WHERE add_months (m1.pub _ date, 1) = m2.pub _ date AND m2.id = 9; m1DATE M2DATE M1.ID/M2.ID ----------------- ----------- 2012-1-29 2012-2-29 0.5555555552012-1-30 2012-2-29 0.6666666662012-1-31 2012-2-29 0.777777777 date 2012-1-29 pushed backward for one month, three records were generated. Therefore, when calculating indicators such as the year-on-year ratio during development, you must note that, if not strictly required, you can use a time difference of 30 days or use
The current date is reduced by one month to eliminate this phenomenon.