how to return exactly rows between two dates with timestamps
this code didn't return all row between 01-04 and 07-05
so what is the problem and why it didn't work correctly
and how to select rows between two date with timestamp when i use date
format like this 01-04-2015
SELECT d_send_items.si_id ,
FROM d_send_items WHERE
DATE_FORMAT(FROM_UNIXTIME(d_send_items.si_send_date), '%d-%m-%Y') BETWEEN '01-04-2015' AND '07-05-2015'
Best How To :
date_format returns a string, so
between is using string comparisons to figure out whether the values are between those two you provide.
So, unless your date format is something like
between is not going to work as you expect.
For example, the date
08-04-2015 is between the two dates
07-05-2015 but the string
08-04-2015 is not between the two strings
07-05-2015, because the most significant portion
08 is beyond the range which terminates at
So you could use:
between '2015-04-01' and '2015-05-07'
but per-row functions never scale well in relational databases.
If they're proper
timestamp fields, I think you can also bypass the conversion and use something like:
where d_send_items.si_send_date >= '01-04-2015'
and d_send_items.si_send_date < '08-05-2015'
< day following bit for the second conditional since
08-05-2015 is the same as
08-05-2015 00:00:00) assuming MySQL will recognise those date formats as
However, even if it doesn't and you have to use some function to turn those string into
timestamp values, this is something that would be done once for the whole query rather than (most likely) for every single row.