Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

DATE_ADD MySQL Function?

Quote Reply
DATE_ADD MySQL Function?
Okay...I am making progress with my forum script/add-on. I am trying to show posts in the last 24, 48, 72 hours...I've been playing around with the DATE_ADD MySQL function, which is referenced here:

http://www.mysql.com/..._time_functions.html

(Although the examples given in this page are not that great since they simply use the SELECT statement within a table, which does not include JOINS or FROM.)

I am using the following codes in my forum script:

In Reply To:

my $postdays = $in->param('newposts');
if ($postdays = '24') {
$sth = $FORUMPOSTSDB->prepare ("SELECT * FROM Forum_Posts WHERE DATE_ADD(Add_Post, INTERVAL 1 DAY)");
}
elsif ($postdays = '48') {
$sth = $FORUMPOSTSDB->prepare ("SELECT * FROM Forum_Posts WHERE DATE_ADD(Add_Post, INTERVAL 2 DAY)");
}
elsif ($postdays = '72') {
$sth = $FORUMPOSTSDB->prepare ("SELECT * FROM Forum_Posts WHERE DATE_ADD(Add_Post, INTERVAL 3 DAY)");
}
else {
$sth = $FORUMPOSTSDB->prepare ("SELECT * FROM Forum_Posts WHERE DATE_ADD(Add_Post, INTERVAL 1 DAY)");
}


I have also tried using the MySQL example of "-1, $postdays", before the INTERVAL attribute.

But no matter what parameter value I add, like 144 or any other integer/numerical value, all posts show up.

Any ideas or directions would be helpful!

Thanks in advance! Wink

Regards,

Regards,

Eliot Lee
Quote Reply
Re: DATE_ADD MySQL Function? In reply to
Your where clause is missing an operator and operand. You are basically saying SELECT * FROM Posts WHERE 1.

What you really want is:

SELECT * FROM Posts WHERE Add_Post > DATE_SUB(NOW(), INTERVAL 1 DAY)

I think. I may have the syntax a bit off.

However, you may want to rethink using dates, we've found just storing unix timestamps to be a lot faster and more efficent. Also, you are tying yourself to MySQL by using those functions.

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: DATE_ADD MySQL Function? In reply to
THANKS, ALEX!

The codes you posted make a lot of sense. Also, about using unix timestamps...my concern was translating those values all the time into date time format that makes sense to users...I just thought that using DATETIME would make better sense...But I'll give the TIMESTAMP type a try.

And very good point about data types...learning now about Access -> SQL Server data types...and it is a bit tricky. Better to use portable types than tying oneself to one database application...although I don't really anticipate moving to something like Oracle,Postgres, etc. anytime in the near future.

Regards,

Eliot Lee Wink
http://anthrotech.com/
Quote Reply
Re: DATE_ADD MySQL Function? In reply to
Hi,

Sorry, I don't mean to use a TIMESTAMP column type, but rather use an INT. Then to store the current time you just do:

my $now = time();
my $sql = 'INSERT INTO foo VALUES($now)';

To display it you can just use the localtime function.

my $display_time = localtime ($time);

or you can break it apart into whatever format you like. It's actually a lot less headaches if you want anything other then mysql yyyy-mm-dd format.

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: DATE_ADD MySQL Function? In reply to
Even better is to look at the iCal format which can store much more information (date, month, year, timezone, etc)...

tom

Quote Reply
Re: DATE_ADD MySQL Function? In reply to
Thanks, Alex and tom_lee...I'll check it out!

Regards,

Eliot Lee Wink
http://anthrotech.com/