SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: Time/Date Query

  1. #1
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Time/Date Query

    Hi Chaps,

    I have a MySQL table with a timestamp and an interval column:

    Code:
    CREATE TABLE IF NOT EXISTS `tbl_checks` (
      `check_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `check_title` varchar(50) NOT NULL,
      `check_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `check_file` varchar(50) DEFAULT NULL,
      `check_interval` varchar(3) DEFAULT NULL,
      PRIMARY KEY (`check_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;
    
    --
    -- Dumping data for table `tbl_checks`
    --
    
    INSERT INTO `tbl_checks` (`check_id`, `check_title`, `check_time`, `check_file`, `check_interval`) VALUES
    (1, 'check_1', '2011-04-20 00:00:07', 'check_1.php', '1'),
    (2, 'check_2', '2011-03-28 00:03:33', 'check_2.php', '28'),
    (3, 'check_3', '2011-04-20 00:00:03', 'check_3.php', '1'),
    (4, 'check_4', '2011-04-16 00:00:08', 'check_4.php', '5');
    I need a select query that will give me the 'checks' that are overdue. Something like: select if NOW is after timestamp+interval?

    ?

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Interval is measured in...?

  3. #3
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Apologies, Interval in DAYS

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Should be something along the lines of...
    SELECT * FROM table WHERE NOW() < DATE_ADD(check_time, INTERVAL check_interval DAYS)

    Note: This function (NOW()) is time-specific. So it will only return results that occured before the current time on their expected due date.

  5. #5
    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 StarLion View Post
    Should be something along the lines of...
    you were close, you just had the wrong inequality

    for overdue, i think you want WHERE NOW() > check_time + INTERVAL check_interval DAYS
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guys,

    I've tried both variations, but I'm getting a syntax
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DAYS' at line 1
    Any ideas?

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Er... might need to be DAY instead of DAYS. *is not quite awake yet*

  8. #8
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, it's day.

  9. #9
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good stuff, thanks a lot guys,

    SELECT * FROM tbl_checks WHERE NOW() > check_time + INTERVAL check_interval DAY;
    Sorted

  10. #10
    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)
    i hate it when i propagate an error

    some lousy data integrity filter i am, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •