The best way to get the first and last day of last month?
PhpMysqlOptimizationDatePhp Problem Overview
I'm looking for the best way to get the first and the last day of a last month. I use them for make SQL queries to get stats of last month.
I think that this is the best way, more optimized but not more comprensive, anyone have another way to do the same?
$month_ini = date("Y-m-d", mktime(0, 0, 0, date("m", strtotime("-1 month")), 1, date("Y", strtotime("-1 month"))));
$month_end = date("Y-m-d", mktime(0, 0, 0, date("m", strtotime("-1 month")), date("t", strtotime("-1 month")), date("Y", strtotime("-1 month"))));
Thanks!!
Php Solutions
Solution 1 - Php
In PHP 5.3, you can use the DateTime class :
<?php
$month_ini = new DateTime("first day of last month");
$month_end = new DateTime("last day of last month");
echo $month_ini->format('Y-m-d'); // 2012-02-01
echo $month_end->format('Y-m-d'); // 2012-02-29
Solution 2 - Php
Last day of the previous month:
date("Y-m-d", mktime(0, 0, 0, date("m"), 0));
First day of the previous month:
date("Y-m-d", mktime(0, 0, 0, date("m")-1, 1));
Solution 3 - Php
I use these in MySQL and PHP scripts, short and simple:
echo date('Y-m-d', strtotime('first day of last month'));
echo date('Y-m-d', strtotime('last day of last month'));
MySQL use example:
$query = $db->query("SELECT * FROM mytable WHERE 1 AND mydate >= '".date('Y-m-d', strtotime('first day of last month'))."'");
Solution 4 - Php
If you're doing this for the purpose of a MySQL query, have you considered using the MONTH function, e.g.
SELECT [whatever stats you're after] FROM table
WHERE MONTH(date_field) = 12 and YEAR(date_field) = 2011
This would get your stats for December. If you start to experience performance problems and the historical data doesn't change, you might want to denormalise the data into an aggregate table (rolled up by the smallest increment you need, e.g. daily/hourly/monthly etc).
Solution 5 - Php
you can do this in MySQL:
WHERE `DateAndTime` >= '2012-02-01 00:00:00'
AND `DateAndTime` < '2012-03-01 00:00:00'
Solution 6 - Php
let mysql deal with dates.
anything that is for the database to do, let it do.
like this:
mysql_query("set @last_day=last_day(now()-interval 1 month),@first_day=(@last_day-interval 1 month)+interval 1 day");
$result=mysql_query("select * from `table` where (`date` between @first_day and @last_day)");
the best is that this will work even if the year changes.
just remember to change the database timezone to match php.