MYSQL - Retrieve Timestamps between dates

datetime, mysql, sql

Solution

SELECT timestamp
FROM   tablename
WHERE  timestamp >= userStartDate
       AND timestamp < userEndDate + INTERVAL 1 DAY

This will select every record having date portion between `userStartDate` and `userEndDate`, provided that these fields have type of `DATE` (without time portion).

If the start and end dates come as strings, use `STR_TO_DATE` to convert from any given format:

SELECT timestamp
FROM   tablename
WHERE  timestamp >= STR_TO_DATE('01/11/2010', '%m/%d/%Y')
       AND timestamp < STR_TO_DATE('01/12/2010', '%m/%d/%Y') + INTERVAL 1 DAY

Problem

All, I have a MYSQL table with a column called timestamp. It is of `DATETIME` datatype and has values like "10/1/2009 3:25:08 PM', "10/1/2009 3:30:05 PM', "10/4/2009 3:40:01 PM', etc.. I want to write a SQL query to select all the values in the timestamp field occuring between two dates.. something like this: ``` select timestamp from tablename where timestamp >= userStartDate and timestamp <= userEndDate ``` The userInput dates will not have time portions. Can you please suggest the correct MySQL Query Syntax for this? Thanks

Original source