mysql select timestamps between a and b returning

问题:

Tried this

select * from table where timestamp_field between 1330560000 and 1336170420

and this

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

both returning empty result set.

But this

select * from table where timestamp_field >= 1330560000

returns all the rows

To make things more absurd

select * from table where timestamp_field <= 1336170420

returns empty result set.

Of course, there exists timestamp values before, between and after 1336170420=4.may 2012. and 1330560000=1.march 2012.

Timestamp values are ok, at least phpmyadmin shows correct (human-readable) date-time values. I created timestamps by parsing strings, with

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

Guess I'm missing something, but can't find what!?

回答1:

MySQL expects date literals, not integer ones:

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

To use integers (assuming that they are seconds since the UNIX epoch), first convert them using MySQL's FROM_UNIXTIME() function:

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

Or else use UNIX_TIMESTAMP() to convert your column to its UNIX representation:

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

回答2:

why not convert the timestamps from_unixtime(timestamp/1000) as dateTime while using your sql code? have you tried that?