SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Time Stamp - Between

    Hi,

    How do i do a query where i can only get records where the timestamp is between 2 date/time ranges. I only need to do it between dates but i thought doing it with time too would be easier. But my query below is not working and keeps showing 0 results when there are rows of data between the date range. Any help would be great please.

    Code:
    SELECT * 
    FROM  `leads` 
    WHERE  `ProgramID` LIKE  '1' AND `TimeStamp` BETWEEN '2013-01-22 00:00:00' AND '2013-01-22 23:59:99'
    ORDER BY  `leads`.`LeadID` DESC
    I've also tried

    Code:
    SELECT * 
    FROM  `leads` 
    WHERE  `ProgramID` LIKE  '1' AND `TimeStamp` BETWEEN '%2013-01-22%' AND '%2013-01-22%'
    ORDER BY  `leads`.`LeadID` DESC
    Any help would be much appreciated.

    Thank you.
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    first, may i suggest you stop immediately using those horrid backticks*

    this is what you want, instead of BETWEEN --
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star
      FROM leads 
     WHERE ProgramID = 1 
       AND `TimeStamp` >= '2013-01-22' 
       AND `TimeStamp`  < '2013-01-23'
    ORDER 
        BY LeadID DESC
    * except where you have to, when the column or table name is a reserved word like TIMESTAMP, in which case it would be far far better to rename the column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    first, may i suggest you stop immediately using those horrid backticks*

    this is what you want, instead of BETWEEN --
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star
      FROM leads 
     WHERE ProgramID = 1 
       AND `TimeStamp` >= '2013-01-22' 
       AND `TimeStamp`  < '2013-01-23'
    ORDER 
        BY LeadID DESC
    * except where you have to, when the column or table name is a reserved word like TIMESTAMP, in which case it would be far far better to rename the column
    Thank you for the help, i used your Query but it didnt work, it only worked if i did it like this but thank you for the help

    (btw i needed to use the * as im expoerting everything to a CSV file)

    Code:
    SELECT * 
    FROM  `leads` 
    WHERE  `ProgramID` LIKE  '$programid' AND `TimeStamp` >= '$yearfrom-$monthfrom-$dayfrom 00:00:00' 
       AND `TimeStamp`  < '$yearto-$monthto-$dayto 23:59:99'
    ORDER 
        BY LeadID DESC
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    if your query worked but mine didn't, then you did something wrong when testing my query

    i think you overlooked the point that i was trying to make --

    don't use <= 23:59:59, use < the next day
    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
  •