The best way to get the first and last day of last month? [duplicate]

Question

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!!

Unsay

#2681001 – Rockfish

This post is for get the first and the last day of the PAST month :) a little different – Unsay

Answer 1

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

Tiros

This ought to be the answer imho – Decision

This also works brilliantly with strtotime() if you want a timestamp. Like strtotime('last day of last month'). – Uriniferous

How would you do this if tou wanted first/last day of the month some date falls in? – Mazza

You can use new DateTime("first day of February 2012") or new DateTime("first day of 2012-02") for instance. – Tiros

Just beware, you don't get the beginning of the day on the first, but the server's current time. I like it, but don't use the whole object, make sure you format as @Tiros suggests! – Fairing

You can do $month_ini->setTime(0, 0); to reset the hours/minutes/seconds of the datetime instance. – Swisher

Single line options: date('Y-m-d', strtotime('first day of last month')); or (new DateTime("first day of last month"))->format('Y-m-d'); – Countercharge

Answer 2

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));

Mariehamn

Nice answer; if the current month is 01 (January) date() will decrement the year by one and wrap the month back round to 12, works great. Thanks! – Acicular

This helped greatly, however there is no explanation or link to explanation of WHY it worked. ie. the 0 in the first statement is the last day because... (0 effectivly means -1 as there is no 0 day in any month and PHP takes it as the 1st - 1) – Heliogabalus

Caution: when using last day with time of 00:00:00 ... if you use this in a query the date-time will not include that day as it is at 0 time. To include the full day the time would need to be 23:59:59 ... alternative is to just use 0 time of the 1st of next month. – Explicit

Answer 3

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'))."'");

Cosmopolitan

Answer 4

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).

Brooch

and what if there are rows from December 2010 and from December 2011? How do you distinguish them? – Addiction

@Addiction Doh! Good spot, have added the equivalent YEAR filter to it (although if you followed the link I provided this should have been pretty obvious!) – Brooch

Answer 5

I did not see anything like this in related questions so I wanted to contribute with another way of getting this done.

date('Y-m-01', strtotime('last month'));
date('Y-m-t', strtotime('last month'));

Passing to the format the first day of the month 'Y-m-01' and then 'Y-m-t' for the last day. The t formatter represents the number of days in the given month

Politicking

It is good that you are filling in old pages with the best advice, but I've closed this page with an earlier question that gives your advice of 01 and t. #2681001 – Ungotten

Answer 6

you can do this in MySQL:

WHERE `DateAndTime` >= '2012-02-01 00:00:00'
AND `DateAndTime` < '2012-03-01 00:00:00'

Addiction

Thanks but is more slow do this in mysql :( thanks, I need optimization – Unsay

put an index on the DateAndTime column. I'd say you won't get any faster than that. – Addiction

Answer 7

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.

Emmi