How to get the first day and last day of current/last month in PHP language?

Question

I need to get the first day and last day of the current (and last) month. I need them to be in the format that can be used in a MySQL query.

$firstdaycurrentmonth = ??????;
$lastdaycurrentmonth = ??????;

The MySQL query in Q2A CMS:

qa_db_read_all_assoc( qa_db_query_sub(
    "SELECT  COUNT(postid) FROM ^posts WHERE created BETWEEN $ and $",
    $firstdaycurrentmonth, $lastdaycurrentmonth
));

Note:
^posts: the table name
created: a column with a datetime format.

Similarly, I need to get the same things for the previous month programmatically.

Answer 1

Probably the most reliable approach in PHP would be to use a DateTime object:

$today = new DateTime('now');

Clone that object and use the modify() method to get the desired values:

$firstdaycurrentmonth = clone $today;
$firstdaycurrentmonth->modify('first day of this month');

$lastdaycurrentmonth = clone $today;
$lastdaycurrentmonth->modify('last day of this month');

Similarly for the previous month:

$firstdaypreviousmonth = clone $today;
$firstdaypreviousmonth->modify('first day of previous month');

$lastdaypreviousmonth = clone $today;
$lastdaypreviousmonth->modify('last day of previous month');

You could also do clone and modify in one step:

$firstdaycurrentmonth  = (clone $today)->modify('first day of this month');
$lastdaycurrentmonth   = (clone $today)->modify('last day of this month');

$firstdaypreviousmonth = (clone $today)->modify('first day of previous month');
$lastdaypreviousmonth  = (clone $today)->modify('last day of previous month');

Of course you could also create a new object each time

$firstdaycurrentmonth = (new DateTime('now'))->modify('first day of this month');

but I find the code that clones an existing object a bit more readable.

Pass the values as formatted strings to your query:

qa_db_read_all_assoc( qa_db_query_sub(
    "SELECT ... BETWEEN $ and $",
    $firstdaycurrentmonth->format('Y-m-d'),
    $lastdaycurrentmonth->format('Y-m-d')
));

---

Alternatively you could do the calculations in your SQL queries using MySQL date/time functions:

• first day of current month:

  DATE_SUB(CURRENT_DATE, INTERVAL DAY(CURRENT_DATE)-1 DAY)
  

• last day of current month:

  LAST_DAY(CURRENT_DATE)
  

• first day of previous month:

  DATE_SUB(DATE_SUB(CURRENT_DATE, INTERVAL DAY(CURRENT_DATE)-1 DAY), INTERVAL 1 MONTH)
  

• last day of previous month:

  DATE_SUB(CURRENT_DATE, INTERVAL DAY(CURRENT_DATE) DAY)
  

Example:

qa_db_read_all_assoc( qa_db_query_sub(
    "SELECT COUNT(postid) FROM ^posts ".
    "WHERE created BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL DAY(CURRENT_DATE)-1 DAY) AND LAST_DAY(CURRENT_DATE)"
));

Comments

Thank you for your effort.

– teddydoors 2020-09-26