SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot NevadaSam's Avatar
    Join Date
    May 2006
    Location
    Georgia
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT records updated in last 12 hours.

    I want to select all records that have been updated in the last 12 hours.
    My database contains this field to record the time of update: lastdatetime, type = datetime
    so the time updated would be recorded as: 2010-05-11 21:54:03
    This is my current WHERE claus

    WHERE TO_DAYS( NOW( ) ) - TO_DAYS( lastdatetime ) <=1 AND DATE_FORMAT( CURRENT_TIMESTAMP( ) , '&#37;H%i' ) - DATE_FORMAT( lastdatetime, '%H%i' ) <=720

    I got this ideal from a post on dev.mysql.com. It is suppose to "filter for 1 day or less since updated. After that, it filters for 720 minutes (12 hurs) or less since updated".

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try:

    WHERE lastdatetime >= CURRENT_TIMESTAMP - INTERVAL '12' HOUR
    Sam Hastings

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hold on, that will only work for MySQL...
    Sam Hastings

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SJH View Post
    Hold on, that will only work for MySQL...
    luckily that's what he's using -- TO_DAYS and DATE_FORMAT are mysql functions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    True dat
    Sam Hastings


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
  •