Compare two date columns in mysql query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Compare two date columns in mysql query

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Wouldn't you want all records that auto_archive is in the past?
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

curseofthe8ball wrote:See what I mean?
I don't see how a future date affects archival of stuff "now."
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

MySQL's NOW() function.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

might be you're looking for this code

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

read your PM's dibyendrah.
Post Reply