I have exhausted my searches looking for a solution to this MySQL date calculation. My client wants to know how many deals were done in the past week, month and year.
It is a SQL based script (but in MySQL language) I am busy with, so don't bother with the Select, From etc.
The datetime_created field (in the script) is already in UnixTime, while the timestamp(now) is used to calculate current date minus 7 days.
As the formula result can be negative as well, I only require the result between 0.01 and 7.
My formula for the past week is listed below. Any ideas?
(registrations.datetime_created -unix_timestamp(now())-604800)/86400
Simon at The Access Group :
How about using UNIX_TIMESTAMP / ADDDATE() / between?\n\nEdit slight expansion to clarify\n\nSELECT\n COUNT(*)\nFROM registrations\nWHERE registrations.datetime_created\n -- will go back to now() - a month INCLUDING time. -1 WEEK/MONTH/YEAR etc all works here\n BETWEEN UNIX_TIMESTAMP(ADDDATE(NOW(),INTERVAL -1 WEEK)) \n AND UNIX_TIMESTAMP(NOW()) \n -- will start at now() including time\n\n\nBy specifying the start and end timestamp within the WHERE criteria, MySQL is able to use an index on the datetime_created field (if one is present) to do this very efficiently. If, instead, you have a WHERE registrations.datetime_created*1235/12515 (an operation on the field) within the WHERE then you will see it returning slower as MySQL will need to scan the whole table to determine which rows to return.",
2012-04-18T15:30:08
Travesty3 :
This isn't tested, but I think it will do what you want:\n\nSELECT\n q1.dealsInLastWeek,\n q2.dealsInLastMonth,\n q3.dealsInLastYear\nFROM\n (SELECT COUNT(*) AS dealsInLastWeek FROM registrations WHERE FROM_UNIXTIME(datetime_created) > DATE_ADD(NOW(), INTERVAL -1 WEEK)) q1,\n (SELECT COUNT(*) AS dealsInLastMonth FROM registrations WHERE FROM_UNIXTIME(datetime_created) > DATE_ADD(NOW(), INTERVAL -1 MONTH)) q2,\n (SELECT COUNT(*) AS dealsInLastYear FROM registrations WHERE FROM_UNIXTIME(datetime_created) > DATE_ADD(NOW(), INTERVAL -1 YEAR)) q3\n",
2012-04-18T15:41:25
Joan Vidal :
Test with INTERVAL\n\nSELECT (unix_timestamp(now()-INTERVAL '7' DAY)),(unix_timestamp(now()-INTERVAL '1' MONTH)),(unix_timestamp(now()-INTERVAL '1' YEAR));\n\nWHERE registrations.datetime_created>(unix_timestamp(now()-INTERVAL '7' DAY)) \n",
2012-04-18T15:53:14