Results 1 to 4 of 4

Thread: mySQL Date

  1. #1
    Join Date
    Aug 2005
    Posts
    174
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default mySQL Date

    Hello,

    I'm using the Date format in my database in the format e.g.

    2008-02-19
    YYYY-MM-DD

    And i want to do something like this:

    If date is less than 1 month old, echo "NEW".

    Thanks for your help

  2. #2
    Join Date
    Jul 2006
    Location
    just north of Boston, MA
    Posts
    1,806
    Thanks
    13
    Thanked 72 Times in 72 Posts

    Default

    why not use unix time as an integer field, then check that against the current time minus 1 month

    Code:
    $now = time();
    
    $lastMonth = strtotime("-1 Month", $now);
    
    $thread = THREAD_TIME_IN_UNIX_TIMESTAMP;
    
    if($thread < $lastMonth)
    {
         $status = "NEW";
    }

  3. #3
    Join Date
    Dec 2007
    Location
    Ankara, Turkey
    Posts
    160
    Thanks
    2
    Thanked 2 Times in 2 Posts

    Default

    I think there was a "DATE_DIFF" function in MySQL. Please have a look at the "Date and Time Functions" section in the manual. You can find it via any search engine

  4. #4
    Join Date
    May 2007
    Location
    By the beach
    Posts
    23
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Default

    Please use the MySQL DATE_SUB() module.

    Example SQL:
    -- returns NEW or OLD in `age` column as requested
    SELECT IF('2008-11-19'>DATE_SUB(NOW(), INTERVAL 1 MONTH),'NEW','OLD') as `age`;

    Example SQL in PHP:

    $sql = sprintf("SELECT IF('%s'>DATE_SUB(NOW(), INTERVAL 1 MONTH),'NEW','OLD') as `age`;", $date);


    Reference:
    http://dev.mysql.com/doc/refman/5.0/...ction_date-add

Bookmarks

Posting Permissions

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