MySQL Forums
Forum List  »  Newbie

Date subtraction (difference) returns bizarre results on month boundary
Posted by: Dmitry Katsubo
Date: September 05, 2011 06:20AM

Dear MySQL community,

On some reason for the query below the the date difference is returned in days for current month, for previous months it looks really odd. Can somebody explain me what is the semantics of e.g. 74 (does not look like a difference in hours)?

Thanks in advance.

mysql> desc document;
+-----------------------+------------------+------+-----+
| Field                 | Type             | Null | Key |
+-----------------------+------------------+------+-----+
| id                    | int(10) unsigned | NO   | PRI |
| image_indexing_date   | datetime         | YES  |     |
+-----------------------+------------------+------+-----+

mysql> select image_indexing_date, current_date - cast(image_indexing_date as date) from document where current_date - cast(image_indexing_date as date) > 2 order by 1;

+---------------------+--------------------------------------------------+
| image_indexing_date | current_date - cast(image_indexing_date as date) |
+---------------------+--------------------------------------------------+
| 2011-08-29 15:33:30 |                                               76 |
| 2011-08-29 18:04:24 |                                               76 |
| 2011-08-29 18:22:53 |                                               76 |
| 2011-08-29 23:25:27 |                                               76 |
| 2011-08-30 04:13:47 |                                               75 |
| 2011-08-30 08:27:33 |                                               75 |
| 2011-08-30 15:31:08 |                                               75 |
| 2011-08-30 15:33:43 |                                               75 |
| 2011-08-31 12:13:42 |                                               74 |
| 2011-08-31 14:18:31 |                                               74 |
| 2011-08-31 14:30:51 |                                               74 |
| 2011-08-31 15:45:27 |                                               74 |
| 2011-08-31 17:42:41 |                                               74 |
| 2011-09-01 11:29:42 |                                                4 |
| 2011-09-01 11:34:07 |                                                4 |
| 2011-09-02 16:39:30 |                                                3 |
| 2011-09-02 18:56:41 |                                                3 |
| 2011-09-02 23:38:47 |                                                3 |
+---------------------+--------------------------------------------------+

mysql> select current_date;
+--------------+
| current_date |
+--------------+
| 2011-09-05   |
+--------------+

Additional info:
MySQL v5.1.36 SUSE RPM

Linux kernel 2.6.32.19

Options: ReplyQuote


Subject
Written By
Posted
Date subtraction (difference) returns bizarre results on month boundary
September 05, 2011 06:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.