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

User · Answer

I can see people giving lots of comments on this question. But I think, simple use of LIKE could be easier to get the data from the table.

SELECT * FROM table WHERE COLUMN LIKE '2013-05-11%'

Use LIKE and post data wild character search. Hopefully this will solve your problem.

User · Answer

SELECT   FROM table WHERE col  gt    2010-10-01  AND col  lt    2010-10-31

User · Answer

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  gt   and  lt  operators as per Wouter s answer     Here is an example of temporal fields defined with fractional second precision  maximum precision in use    mysql gt  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 gt  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 gt  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

User · Answer

Whenever possible  avoid applying functions to a column in the where clause   SELECT     FROM table name  WHERE timestamp  gt   UNIX TIMESTAMP  2010-10-01 00 00 00       AND timestamp  lt   UNIX TIMESTAMP  2010-11-01 00 00 00      Applying a function to the timestamp column  e g   FROM UNIXTIME timestamp         makes indexing much harder

User · Answer

Usually it would be this   SELECT      FROM yourtable  WHERE yourtimetimefield gt   2010-10-01     AND yourtimetimefield lt   2010-11-01    But because you have a unix timestamps  you ll need something like this   SELECT      FROM yourtable  WHERE yourtimetimefield gt  unix timestamp  2010-10-01      AND yourtimetimefield lt  unix timestamp  2010-11-01

User · Answer

If you have a mysql timestamp  something like 2013-09-29 22 27 10 you can do this    select   from table WHERE MONTH FROM UNIXTIME UNIX TIMESTAMP time    9    Convert to unix  then use the unix time functions to extract the month  in this case 9 for september

User · Answer

This SQL query will extract the data for you  It is easy and fast   SELECT     FROM table name   WHERE extract  YEAR MONTH from timestamp   201010

[mysql] MySQL timestamp select date range

Examples related to mysql

Examples related to timestamp