Calculate a date range according to the current date

Question

I have searched this site for what i need but i coulldnt find anything to my specific. I have a slight difficulty about pulling data between two dates from mysql. I know the system how to write the code to filter data between two dates but the issue i am having is these two dates are in different months. Let me explain further;

I have working code to pull the date between the two dates - currently i have to set them manually.

what i need is to calculate the two dates ($from & $till) according to the current date. $from should always be the 25th of one month (00:00) and $till should be the 24th of the next month (23:59), while the current date should always be in between.

Examples: on january 7th 2020, $from should be december 25th 2019, 00:00 and $till should be january 24th 2020 (23:59) on january 25th 2020, $from should be january 25th 2020, 00:00 and $till should be february 24th 2020 (23:59)

Currently i have the following code and i set the dates manually:

<?php 

    $from = strtotime('25/12/2019 00:00');
    $till = strtotime('24/01/2020 23:59');

    $stmt = $con -> prepare("SELECT SUM(`amount`) as `total` 
                            FROM `income` 
                            WHERE user_id = ? 
                            && time >= ? && time <= ?"); 
    $stmt -> bind_param('iii', $user_id, $from, $till); 
    $stmt -> execute(); 
    $stmt -> store_result(); 
    $stmt -> bind_result($total_income); 
    $stmt -> fetch();

?>

So is there any way to set these dates automatically according to the current time?

Does this answer your question? [How do I query between two dates using MySQL?](https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql) — Adam, Jan 07 '20 at 12:14
Are you actually asking how to automate the generation of Start Date and End Date based on the current date? — RiggsFolly, Jan 07 '20 at 12:16
I have searched this website and i couldnt find answer to my question. Thats why i explained my situation — Yedek, Jan 07 '20 at 12:18
Then you could have made that the topic of the question :) So now you have to tell us what the rules are for calculating the 2 dates based on todays date — RiggsFolly, Jan 07 '20 at 12:19
If you use slashes in the date, keep in mind that the format ist month / day / year, not day / month / year — Ste Bächler, Jan 07 '20 at 12:19
'So once we reach 25th Januray and date should be set to 24th of February.' - not with you, when does start date get set to 25th feb? — P.Salmon, Jan 07 '20 at 12:20

score 0 · Accepted Answer · edited Jan 07 '20 at 13:52

0

To set the date automatically (according to the current date), you could do something like this:

//default: from 25th of last month to 24th of current month
$from = strtotime('-1 month',strtotime(date('m/25/Y 00:00:00')));
$till = strtotime(date('m/24/Y 23:59:59'));

if (intval(date('d')) > 24) { //current day is bigger than 25 - shift it one month
    $from = strtotime('+1 month',$from);
    $till = strtotime('+1 month',$till);
}

this would use the 25th of last month as default start date and the 24th of the current month as default end date.

if the current day is already the 25th of the month (or later), it will shift both dates 1 month.

Also: If you use slashes in the date, keep in mind that the format ist month / day / year, not day / month / year

edited Jan 07 '20 at 13:52

Dharman

30,962
25
85
135

answered Jan 07 '20 at 12:17

Ste Bächler

478
3
10

In the context of this question, I just don't see that this is useful – Strawberry Jan 07 '20 at 12:22
@Strawberry The question is a little confused, this may well be the answer. [See this comment](https://stackoverflow.com/questions/59628119/pull-mysql-data-between-2-dates#comment105418488_59628119) and [this answer](https://stackoverflow.com/questions/59628119/pull-mysql-data-between-2-dates#comment105418541_59628119) – RiggsFolly Jan 07 '20 at 12:24
Right - the question (last sentence) is: So is there any way to set these dates automatically according to the current time? – Ste Bächler Jan 07 '20 at 12:24
Thanks Bre. Did you mean from and till instead of start and end inside the if clause? – Yedek Jan 07 '20 at 12:27
@yedek: right - i had only $start & $end, then changed it to $from & $till to reflect your code - missed those two. – Ste Bächler Jan 07 '20 at 12:29

Calculate a date range according to the current date

1 Answers1