Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Mar 2005
    Location
    Spokane, WA
    Posts
    148
    Thanks
    4
    Thanked 4 Times in 4 Posts

    Selecting withing a range of timestamps

    I am trying to select records within a range of timestamps and I cannot get my query to work and was hoping someone could enlighten me as to why

    Code:
    SELECT * FROM database WHERE timestamp BETWEEN '2012-09-27 03:58:54' AND '2012-09-26 22:58:54'
    Thanks in advance for your help.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    It looks right, except that since timestamp is a keyword in MySQL you need to enclose it in backticks. Oh...and database is also a keyword, so same problem.

    I assume that your column named timestamp is indeed of data type timestamp?

    Code:
    SELECT * FROM `database` 
    WHERE `timestamp` BETWEEN '2012-09-27 03:58:54' AND '2012-09-26 22:58:54'
    *If* your timestamp column is actually a UNIX (Linux) timestamp--and therefore just an INT data type in your schema--then you have to use a function to convert it...or convert the two BETWEEN times to UNIX timestamps (which will be much more efficient if that field is indexed).

    In the future, make life simpler for yourself: Don't use MySQL keywords for column names.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Mar 2005
    Location
    Spokane, WA
    Posts
    148
    Thanks
    4
    Thanked 4 Times in 4 Posts
    I already though of that but renaming it to time_stamp did not solve the problem, neither did the `` for that matter.

    The field is a timestamp type.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    Silly me! I didn't notice before. Your two BETWEEN values are *BACKWARDS*!!!

    The lower one MUST come first! (Which means earlier one in the case of timestamps.)

    http://dev.mysql.com/doc/refman/5.5/...erator_between
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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