MYSQL query between two timestamps

I have the following entry in my db table

eventName(varchar 100) -> myEvent
date(timestamp) -> 2013-03-26 09:00:00


and I am trying to use the following query;

SELECT * FROM eventList WHERE `date` BETWEEN UNIX_TIMESTAMP(1364256001) AND UNIX_TIMESTAMP(1364342399)


i.e between 2013-03-26 00:00:01 and 2013-03-26 23:59:59

but it is giving me 0 results.

i have tried expanding the date range with no luck and there are deffinitly results within the range.

any help is appreciated.

6 Answers
6

Try:

SELECT * FROM eventList WHERE `date` BETWEEN FROM_UNIXTIME(1364256001) AND FROM_UNIXTIME(1364342399)


Or

SELECT * FROM eventList WHERE `date` BETWEEN '2013-03-26 00:00:01' AND '2013-03-26 23:59:59'


Try this one. It works for me.

SELECT *
FROM eventList
WHERE DATE(date)
BETWEEN '2013-03-26' AND '2013-03-27'

You just need to convert your dates to UNIX_TIMESTAMP. You can write your query like this:

UNIX_TIMESTAMP

SELECT *
FROM eventList
WHERE
date BETWEEN
UNIX_TIMESTAMP('2013/03/26')
AND
UNIX_TIMESTAMP('2013/03/27 23:59:59');


When you don't specify the time, MySQL will assume 00:00:00 as the time for the given date.

00:00:00

Try the following:

SELECT * FROM eventList WHERE
date BETWEEN
STR_TO_DATE('2013/03/26', '%Y/%m/%d')
AND
STR_TO_DATE('2013/03/27', '%y/%m/%d')


@Amaynut Thanks

SELECT * FROM eventList WHERE date BETWEEN UNIX_TIMESTAMP('2017-08-01') AND UNIX_TIMESTAMP('2017/08/01');


above mention code works and my problem solved.

SELECT * FROM orders WHERE order_date_time BETWEEN 1534809600 AND 1536718364

orders

order_date_time

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.