Results 1 to 2 of 2

Thread: Selecting New Entried added today

  1. #1

    Thread Starter
    Lively Member rocket0612's Avatar
    Join Date
    Feb 2005
    Location
    Belfast, Northern Ireland
    Posts
    95

    Selecting New Entried added today

    I have some code which selects rows of data added within the last 12 hours, using:

    Code:
    $result1 = mysql_query("SELECT date_format(`ItemPubDate_t`,
    '%H:%i %d %b %y'),ItemTitle,ItemLink,ItemSourceURL 
    FROM feedItems WHERE `ItemPubDate_t` > DATE_SUB(CURRENT_TIMESTAMP, 
    INTERVAL 1320 MINUTE) 
    ORDER BY `ItemPubDate_t` DESC", $connection);
    The field ItemPubDate is the same as ItemPubDate_t but ItemPubDate is VARCHAR field while ItemPubDate_t is a DATETIME field.

    I would like to add in to the query an argument to say only select rows where the ItemPubDate_t is not before todays date, so so it was 6am and it displays the new items within the past 12 hours, it would only display those added today 14 April, nothing from 13 April would display.

    I have tried varuous things but nothing will work. Any help would be appreciated.
    The Box Said: "You need Windows Vista or better" ... So I Installed LiNUX

  2. #2
    PowerPoster
    Join Date
    Sep 2003
    Location
    Edmonton, AB, Canada
    Posts
    2,629

    Re: Selecting New Entried added today

    well, it looks like you're subtracting 22 hours from the date, not 12. also, I don't know how you're storing your dates, but if you're storing it as a unix timestamp you could use mktime(). even if you aren't, you could use a mktime() call and then format it correctly using date(). unless MySQL can differ between two different types of date formats and still work correctly, which I'm not sure of.

    anyway:
    Code:
    $today = mktime(0, 0, 0); //0h, 0m, 0s of today
    $sql = "SELECT date_format(`ItemPubDate_t`,
    '%H:%i %d %b %y'),ItemTitle,ItemLink,ItemSourceURL 
    FROM feedItems WHERE `ItemPubDate_t` > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1320 MINUTE)
    AND ItemPubDate_t > $today 
    ORDER BY `ItemPubDate_t` DESC";

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width