Gossamer Forum
Home : General : Perl Programming :

mysql date functions

Quote Reply
mysql date functions
I have 2 fields, date_added which is the date the record was added using the now() function, the other num_days which is the number of days before it gets deleted, values can be 30 or 180 days.
To get the days left before deletion i've got -
$query = qq!
SELECT UNIX_TIMESTAMP(DATE_ADD(date_added,INTERVAL num_days +1 DAY)) FROM table WHERE id=$user
$sth = $dbh->prepare($query) or &cgierr("Unable to query. Reason: $DBI::errstr.");
$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");;
while (($u_date) = $sth->fetchrow_array) {
$diff=int((($u_date - time())/86400));
print "$diff";}

works fine, but can any one think of a way to do it all within the select statement. I've tried everything I can think of, maybe there isn't a way.

Quote Reply
Re: [lanerj] mysql date functions In reply to
You could run:

FROM table
WHERE id = $user AND

to get the number of days old it is.


Gossamer Threads Inc.
Quote Reply
Re: [Alex] mysql date functions In reply to
Alex thanks for the reply.
That won't work the way I currently have things setup as num_days is a numeric field.
What I'll to do is change the num_days field to a date field and add values using date_add(). This will give me a future deletion date, and then I can use to_days() to calculate the remaining days.
I hate working with dates, they are my pet hate.