I have this certain problem about mysql date functions.
I'm trying to compare the value of THIS MONTH to the given timestamp in database.
For example, month today is june, and the timestamp is 1369967316
And I'm trying to determine if that timestamp is in month of june.
$query = db_query("SELECT * FROM users WHERE MONTH(FROM_UNIXTIME(CURDATE())) = MONTH(1369967316)");
//count total members this mont
$members_month = $query->rowCount();
so if I used the rowCount, the $members_month should have the value of 1.
Unfortunately it doesn't work.
Any help would be appreciated.
Well I saw some answers that some kind of relevant to mine but it doesn't hit the spot or I didn't applied it well.
解决方案
This works for me:
mysql> SELECT MONTH(FROM_UNIXTIME(1369967316));
+----------------------------------+
| MONTH(FROM_UNIXTIME(1369967316)) |
+----------------------------------+
| 5 |
+----------------------------------+
Your issue is likely coming from the fact that 1369967316 is May 30th, not June (as you expect), thus resulting in an inequality with MONTH(CURDATE()).
mysql> SELECT FROM_UNIXTIME(1369967316);
+---------------------------+
| FROM_UNIXTIME(1369967316) |
+---------------------------+
| 2013-05-30 22:28:36 |
+---------------------------+