Mysql selects timestamps between a and b, returning all or 0 timestamps

Question

Tried this

select * from table where timestamp_field between 1330560000 and 1336170420 

and this one

 select * from table where timestamp_field >=1330560000 and timestamp_field<=1336170420 

both return an empty result set.

But this

 select * from table where timestamp_field >= 1330560000 

returns all rows

To make things more absurd

 select * from table where timestamp_field <= 1336170420 

returns an empty result set.

Of course, there are timestamps before, between, and after 1336170420 = 4.may 2012. and 1330560000 = 1.march 2012.

Timestamp values ​​in order, at least phpmyadmin shows the correct (human-readable) date and time values. I created timestamps by parsing strings using

 UPDATE table SET timestamp_field = STR_TO_DATE(timestamp_string, '%d.%m.%Y') 

Guess I missed something, but can't find what?

Answer 1

MySQL expects date literals , not integers:

 SELECT * FROM table WHERE DATE(timestamp_field) BETWEEN '2012-03-01' AND '2012-05-04' 

To use integers (assuming they are seconds since the UNIX era), first convert them using MySQL FROM_UNIXTIME() :

 SELECT * FROM table WHERE timestamp_field BETWEEN FROM_UNIXTIME(1330560000) AND FROM_UNIXTIME(1336170420) 

Or use UNIX_TIMESTAMP() to convert a column to its UNIX view:

 SELECT * FROM table WHERE UNIX_TIMESTAMP(timestamp_field) BETWEEN 1330560000 AND 1336170420 

Answer 2

why not convert from_unixtime (timestamp / 1000) timestamps to dateTime when using your sql code? have you tried this?