#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    35
    Rep Power
    2

    Within: INTERVAL, BETWEEN, DATESUB Urgh!


    I should know this, but I am having a hell of a time with a query. Am I crazy?

    Got a few records with these timestamps:
    2013-02-04 20:12:49
    2013-02-03 21:15:25
    2013-02-03 20:25:11
    2013-02-03 17:08:13
    2013-02-03 16:31:18

    I am trying to count how many records came in 20 minutes prior to 2013-02-03 21:15:25 (which should be zero)

    SELECT COUNT(*) from xferleads where timestamp > '2013-02-03 21:15:25' - INTERVAL 20 MINUTE AND customer = 1

    The interval just won't give me the right count. Even at one minute I get 2 counts on the above dataset.
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,993
    Rep Power
    9397
    Your query only looks for stuff where the timestamp is after 20:55. It says nothing about the timestamp also being before 21:15.

    Code:
    SELECT COUNT(1) FROM xferleads WHERE timestamp >= '2013-02-03 21:15:25' - INTERVAL 20 MINUTE AND timestamp < '2013-02-03 21:15:25' AND customer = 1
    Note you can't (easily) use BETWEEN because you don't want to include the record at exactly timestamp=21:15.

IMN logo majestic logo threadwatch logo seochat tools logo