Guidelines

This site is for tech Q&A. Please keep your posts focused on the subject at hand.

Ask one question at a time. Don't conflate multiple problems into a single question.

Make sure to include all relevant information in your posts. Try to avoid linking to external sites.

Links to documentation are fine, but in addition you should also quote the relevant parts in your posts.

1 vote
27 views
27 views

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.

in Scripting by (25) 2
edited by

Your answer

Preview

Privacy: Your email address will only be used for sending these notifications.
Anti-spam verification:
By submitting this post you agree to our Terms & Conditions.
To avoid this verification in future, please log in or register.

1 Answer

0 votes
 
Best answer

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)"
));
by (245) 7
selected by
 
Thank you for your effort.
...