How to subtract dates?

Anon

Hi!

Here's what I'd like to do:

I'm going to start a newsletter which will be sent out every day. Subscribers can choose how often they'd like to receive the newsletter (e.g. every day, once a week etc...). The subscribers' details will be stored in a MySQL database. The last date a subscriber received the newsletter will also be stored in the database (in a date field).

Every day my script should check to see how long ago a subscriber received his last newsletter, and if it's time to send him a new one. And here comes my problem:

How can I subtract two dates to see how many days have passed? The dates are stored in the following format in the MySQL database: 2001-03-31.

I tried this, but it didn't work:
$days = date("Y-m-d") - $date_from_database

I hope someone can help!

Thanks!

Anon

I believe mktime() is the function you are looking for.

http://www.php.net/manual/en/function.mktime.php

Anon

I hate date formats. PHP just blows chunks when it comes to dates.

Rather than store anything in a "date" format, I store everything as an integer.

mktime() returns the # of seconds since Jan 1, 1970. If I want a day, I multiply 86400 by the number of days.

To find out if something happened more than three days ago...

$event_date=integer, mktime() when "something" happened.

$now=mktime();
$then=$now-(86400*3);

if ($event_date<$then) {
// more than three days ago
}

else { // less than three days ago
}

Trying to get PHP, MySQL, AND PostgreSQL to work together on dates was an exercise in futility.

But integers work well. (until 2038)

-Ben

Anon

Hi! You can solve your problem with date using the mysql function to_days.
try something similar to:

select to_days(now()) - to_days('2001-04-03');
this query returns the number of days between now and the date you specify ...
Hope this helps ....

P.S.
mysql provides a wide collection of date and time function, ... take a look here for mere details:
http://www.mysql.com/doc/D/a/Date_and_time_functions.html

Anon

Thanks for your replies!

The MySQL method worked great, and it was very easy to use. I think I will stick with that.

--
Id4ever

Anon

To make your job with storing mysql dates easier, try this (actually it's DATETIME format, but you can change it pretty easily):

// Format of MySQL DATETIME timestamp.
date("Y-m-d H:i:s")

// Format to display date.
date("l F j, Y, g:i a")

To store today's date it's:

$time = date("Y-m-d H:i:s");

To display it use:

$display_time = date("l F j, Y, g:i a", $time);

echo $time . "<br>";
echo $display_time . "<br>";

I'm not entirely sure how PostGRE stores dates, but by using the date() function you can format it pretty easily.

I also thought date/datetime sucked in MySQL, but once you get it working it's not too bad.