MYSQL query between two timestamps

mysql unix-timestamp
user2068441 picture user2068441 · Jan 9, 2014 · Viewed 145.6k times · Source

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 definitely results within the range.

any help is appreciated.

Answer

Frank Conry picture Frank Conry · Jan 9, 2014

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'

Related questions

MySQL convert date string to Unix timestamp

How do I convert the following format to unix timestamp? Apr 15 2012 12:00AM The format I get from DB seems to have AM at the end. I've tried using the following but it did not work: CONVERT(DATETIME, Sales.SalesDate, 103) AS …

Read More
Convert Unix timestamp into human readable date using MySQL

Is there a MySQL function which can be used to convert a Unix timestamp into a human readable date? I have one field where I save Unix times and now I want to add another field for human readable dates.

Read More
How should unix timestamps be stored in int columns?

I have a logging table that will contain millions of writes for statistical reasons. All the columns are int foreign keys. I am also going to add a timestamp column for each row. Given that DATETIME takes 8bits - I …

Read More