MySQL timestamp select date range

Question

Not sure really where to start with this one. Can anyone help/point me in the right direction.

I have a timestamp column in MySQL and I want to select a date range for example, all timestamps which are in Oct 2010.

Thanks.

Answer 1

Usually it would be this:

SELECT *    FROM yourtable  WHERE yourtimetimefield>='2010-10-01'    AND yourtimetimefield< '2010-11-01' 

But because you have a unix timestamps, you'll need something like this:

SELECT *    FROM yourtable  WHERE yourtimetimefield>=unix_timestamp('2010-10-01')    AND yourtimetimefield< unix_timestamp('2010-11-01') 

Answer 2

A compact, flexible method for timestamps without fractional seconds would be:

SELECT * FROM table_name  WHERE field_name  BETWEEN UNIX_TIMESTAMP('2010-10-01') AND UNIX_TIMESTAMP('2010-10-31 23:59:59') 

If you are using fractional seconds and a recent version of MySQL then you would be better to take the approach of using the >= and < operators as per Wouter's answer.

Here is an example of temporal fields defined with fractional second precision (maximum precision in use):

mysql> create table time_info (t_time time(6), t_datetime datetime(6), t_timestamp timestamp(6), t_short timestamp null); Query OK, 0 rows affected (0.02 sec)  mysql> insert into time_info set t_time = curtime(6), t_datetime = now(6), t_short = t_datetime; Query OK, 1 row affected (0.01 sec)  mysql> select * from time_info; +-----------------+----------------------------+----------------------------+---------------------+ | 22:05:34.378453 | 2016-01-11 22:05:34.378453 | 2016-01-11 22:05:34.378453 | 2016-01-11 22:05:34 | +-----------------+----------------------------+----------------------------+---------------------+ 1 row in set (0.00 sec)