Page 1 of 1

Compare two date columns in mysql query

Posted: Fri Mar 31, 2006 4:18 pm
by curseofthe8ball
I have 2 date columns in my table. One grabs the date that the article was created. The 2nd is user-defined and is used to tell the site when to "auto-archive" the article.

I need to compare the two date columns to each other in a query so that I know if there are any current articles that have not been auto-archived.

I thought I could use something like:

Code: Select all

$query = "SELECT * FROM publications where date_full >= auto_archive";
But this doesn't work. Anyone got any ideas?

Posted: Fri Mar 31, 2006 4:26 pm
by feyd
Wouldn't you want all records that auto_archive is in the past?

Posted: Fri Mar 31, 2006 4:32 pm
by curseofthe8ball
feyd,

Yes, but the problem with that is what if someone posts an article today and sets the auto-archive to 90 days from today. I couldn't just use a query that looks for articles with an auto_archive date that has passed.

See what I mean?

Posted: Fri Mar 31, 2006 4:35 pm
by feyd
curseofthe8ball wrote:See what I mean?
I don't see how a future date affects archival of stuff "now."

Posted: Fri Mar 31, 2006 4:43 pm
by curseofthe8ball
I didn't explain this query's purpose well enough...

What I'm trying to do with this query is figure out if there are any articles that are "active" meaning they haven't reached their auto_archive date.

If no articles are "active", then a default message appears stating to check back again soon.

Perhaps I'm not doing this with the most logic, now that I think about it.

I guess I should really just be comparing todays date to the auto_archive date. If I wanted to do that, what would be the best way to do that in terms of getting today's date and comparing it to the date column of auto_archive?

Posted: Fri Mar 31, 2006 5:15 pm
by feyd
MySQL's NOW() function.

might be you're looking for this code

Posted: Tue Apr 04, 2006 2:31 am
by dibyendrah
feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I think this what you're looking for

Code: Select all

<?php
    // format a date for the begining of this month.
        $monthStart = date('Y-m-d', mktime(0,0,0,$month,1,$year));
        // format a date for the end of this month.
        $monthEnd = date('Y-m-d', mktime(0,0,0,$month+1,0,$year));

    $MySQL_db = mysql_connect($host,$user,$password)
          or die("Unable to connect to SQL server");
    mysql_select_db($db)
          or die("Unable to select database");

    // Get the timestamps of the start_date's and end_date's that fall within this
month
    $SQL = "SELECT UNIX_TIMESTAMP(start_date), UNIX_TIMESTAMP(end_date)
        FROM   hall_book
        WHERE  ((start_date BETWEEN '$monthStart' AND '$monthEnd') OR
               (end_date BETWEEN '$monthStart' AND '$monthEnd'))
        ORDER BY start_date ASC";

        $result = mysql_query($SQL, $MySQL_db);

?>

feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Apr 04, 2006 2:57 am
by feyd
read your PM's dibyendrah.