MYSQL - Retrieve Timestamps between dates

Question

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

Answer 1

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

Answer 2

SELECT timestamp
 FROM  myTable
 WHERE timestamp BETWEEN startDate AND endDate

For best results when using BETWEEN with date or time values, you should use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.

- http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between

Answer 3

The userInput dates will not have timestamps. You can convert user input to timestamp with:

mysql> SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');
        -> 1196440219

Your query can become:

select timestamp from tablename 
where timestamp >= UNIX_TIMESTAMP(userStartDate)
and timestamp <= UNIX_TIMESTAMP(userEndDate)