Bug #78470 | When dates are provided as inputs for period_diff, result is not understandable | ||
---|---|---|---|
Submitted: | 17 Sep 2015 12:20 | Modified: | 18 Sep 2015 10:52 |
Reporter: | Su Dylan | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.6.22 | OS: | Any |
Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[17 Sep 2015 12:20]
Su Dylan
[17 Sep 2015 13:13]
Su Dylan
Another situation is not understandable: mysql> select period_diff(20101201123456,20101001123456); +--------------------------------------------+ | period_diff(20101201123456,20101001123456) | +--------------------------------------------+ | 24000000 | +--------------------------------------------+ 1 row in set (0.00 sec)
[18 Sep 2015 10:52]
MySQL Verification Team
http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_period-diff Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values. select period_diff(20101202,20101001); select period_diff(20101201123456,20101001123456); You entered data that's not in the requested format so the result of the function is unclear to you. What you put in is what you get out. Anyhow, the format is understood as YY.*MM so last two digits are month, the ones before are the year and result is in months. So to understand what is the output of this function: select period_diff(20101202,20101001); -> difference( year 201012 - month 02, year 201010 - month 01) = 2 years + 1month = 25 months same for select period_diff(20101201123456,20101001123456); 201012011234-56 to 201010011234-56 is 2000000 years = 24000000 months so exactly what you got out. You can argue that 201012011234-56 is invalid date but it's just 201012011234 years + 56 months no law forbid you to write date like that kind regards Bogdan Kecman