Substract a date compared to current date and show actual remaining days

Question

This is my function below:

function Active()
{
    ............

$num_rows = $db->doQuery('SELECT PremiumDays, PremiumStartTime FROM Premium WHERE AccountID = ?', $_SESSION['AccountID']);
if ($num_rows == -1)
{
$this->Error('ERROR');
$db->getError();
return;
}

$data = $db->doRead();
$data['Status'] = $num_rows == 0 ? '<:SHOW_PREMIUM_STATUS:>' : '<b><font size="2" color="red">Premium is active - <%Days_Remaining%> days remaining.</font></b>';

$replace = array
(
'account_status'        => $data['Status'],
'days_remaining'        => number_format($data['PremiumDays'])
);

$this->content = Template::Load('account-template', $replace);
}

PremiumDays column contains numbers like 10,15,30 etc.

PremiumStartTime contains a date in this format 2018-12-17 21:13:00

What I am trying to achieve is to show the actual days of premium remaining with days_remaining. So, I believe I need to substract from PremiumDays the days that passed since the premium started based on the second column PremiumStartTime.

Something like that I believe, however, I am not sure how to implement it correctly in PHP. Any help is greatly appreciated. Thank you in advance!

days_remaining = PremiumDays - (NumberOfDaysSincePremiumStarted(DateToday - PremiumStartTime))

Possible duplicate of [Compare a date in php and show the result](https://stackoverflow.com/questions/53858355/compare-a-date-in-php-and-show-the-result) — , Dec 19 '18 at 21:44

lufc · Answer 1 · 2018-12-20T01:46:53.203

0

Do it in the DB as follows:

//SQL Server
$num_rows = $db->doQuery('SELECT PremiumDays, PremiumStartTime, 
             (PremiumDays - datediff(day,PremiumStartTime,getdate())) DaysRemaining 
              FROM Premium WHERE AccountID = ?', $_SESSION['AccountID']);


//MySQL
$num_rows = $db->doQuery('SELECT PremiumDays, PremiumStartTime, 
             (PremiumDays - datediff(now(),PremiumStartTime)) DaysRemaining 
              FROM Premium WHERE AccountID = ?', $_SESSION['AccountID']);

Then:

$replace = array
(
'account_status'        => $data['Status'],
'days_remaining'        => $data['DaysRemaining']
);

Proof here: https://www.db-fiddle.com/f/xzuc89C9gQUHpdTTy9M4vf/0

Or if you really want to do it in PHP:

$replace = array
(
'account_status'        => $data['Status'],
'days_remaining'        => $data['PremiumDays'] - date_diff(date_create(), date_create($data['PremiumStartTime']))->days
);

edited Dec 20 '18 at 01:46

answered Dec 19 '18 at 21:36

lufc

1,965
2
15
19

Thanks for your answer. As I am using an SQL server, is this `datediff(now()...)` good for that? Also, now I'll have to edit this as well? `'days_remaining' => number_format($data['DaysRemaining'])` is that correct? Thank you! – Plar625 Dec 19 '18 at 21:40
Amended above to work with SQL Server instead of MySQL – lufc Dec 19 '18 at 21:47
Thank you a lot. Before I accept it, could you please let me know which way - SQL or PHP is better in terms of overall performance? – Plar625 Dec 19 '18 at 21:48
1

There's basically no difference when you're doing it one row at a time like this. Personally I would do it in the SQL to keep the code simple. – lufc Dec 19 '18 at 21:52
I just tested the SQL code and it gives a 500 HTTP error. I changed `now()` to `getdate()` for a test and the website loads but shows the remaining days incorrectly. So, the problem is in `now()` ? – Plar625 Dec 19 '18 at 22:20
@Plar625 `now()` doesn't exist in sql-server https://stackoverflow.com/questions/385042/sql-server-equivalent-of-mysqls-now. – user3783243 Dec 19 '18 at 22:21
@user3783243 - So the above code is correct except the `now()` ? If I change it to `getdate()` it loads but shows the remaining days incorrectly. – Plar625 Dec 19 '18 at 22:23
apologies, I originally wrote for MySQL and then hastily changed to SQL server. It is fixed now. SQL Server uses `getdate()` instead of `now()` and you have to switch around the arguments inside `date_diff` – lufc Dec 19 '18 at 22:30
Thanks, it works but lastly, it's not accurate? I mean it's not checking actual clock right? For example, I have in database `PremiumStartTime: 2018-12-17 21:13:00` and `PremiumDays: 28` and it shows remaining `25` when it should be `26` because server time is 12:40 AM at this moment, right? – Plar625 Dec 19 '18 at 22:39
@lufc - The PHP method shows it correctly as `26` days remaining. Is it safe to use the PHP method you provided above, I mean there shouldn't be any big difference compared to the SQL one? Thanks! – Plar625 Dec 19 '18 at 23:02
It is safe to use it. If your database server is in a different timezone to your web server you will get different results. If you use PHP to create the PremiumStartTime, use the PHP. If you use SQL to create the PremiumStartTime, use the SQL. – lufc Dec 19 '18 at 23:03
(PS if server time is 2018-12-20 00:40:00 then 25 is the correct result as you're now 3 days after the start date) – lufc Dec 19 '18 at 23:05
@lufc - Thanks, and also lastly, I tested the php code you provided and if I change the `PremiumStartTime` from last month November like that: `2018-11-17 21:13:00` it still shows `26` days. Same for `2018-12-17 21:13:00` which is December. Do you know why is that happening? I believe it will happen the same thing with the SQL code too? – Plar625 Dec 19 '18 at 23:06
@lufc - I believe the part `->format('%d')` is doing the trouble. Is there any way to edit it, so it won't cause the trouble mentioned above? – Plar625 Dec 19 '18 at 23:22
You're right, sorry it should be `->days` or `->format('%r%a')` – lufc Dec 20 '18 at 01:48

score 0 · Accepted Answer · answered Dec 19 '18 at 23:48

0

To get the difference in PHP, you can create a DateInterval object using DateTime::diff between the current time (output of date_create()) and a DateTime object created from your PremiumStartTime variable. You can then access the days value of this object to get the total number of days from the PremiumStartTime to the current time. For example:

$data['PremiumStartTime'] = '2018-12-12 21:13:00';
$data['PremiumDays'] = 20;
$days_remaining = $data['PremiumDays'] - date_create($data['PremiumStartTime'])->diff(date_create())->days;
echo number_format($days_remaining);

Output:

Demo on 3v4l.org

answered Dec 19 '18 at 23:48

Nick

138,499
22
57
95

Thanks! So, I won't touch my other code but only this line: `'days_remaining' => $data['PremiumDays'] - date_create($data['PremiumStartTime'])->diff(date_create())->days` It works as I can see. Is it safe? – Plar625 Dec 19 '18 at 23:58
Yes you would just change that one line. What do you mean by "is it safe?" – Nick Dec 20 '18 at 00:42
It's perfectly fine. Ignore the safe part. ;-) Now, I will have to figure it out how to make non negative numbers to display. Because currently If `PremiumDays` is `20` and there are more than 20 days passed since the `PremiumStartTime` it will display it with a negative number like `Remaining days -5` for example. But that's for another question I believe because you already answered me in a perfect way. Thank you a lot! – Plar625 Dec 20 '18 at 01:28

Substract a date compared to current date and show actual remaining days

2 Answers2