Fetch records in range of DATE or DATE AND TIME from unix timestamp field

I am saving date and time in unix timestamp in mysql database. I want to fetch records in particular range of DATE only (Example 1) or DATE AND TIME both (Example 2).

Example 1: Fetch records from date = 20/10/2010 to = 22/10/2010

Example 2: Fetch records from date = 20/10/2010 to = 22/10/2010, from time = 13:2开发者_开发问答0:00 to = 01:10:00

What is the query for this?

Thanks alot for help

Use the FROM_UNIXTIME function to convert a Unix timestamp into a DATETIME data type:

WHERE FROM_UNIXTIME(your_column) BETWEEN '2010-10-20' 
                                     AND '2010-10-22'

The BETWEEN operator is inclusive, and you need to use a standard date format (YYYY-MM-DD) for MySQL to implicitly convert a string into a DATETIME, or you'll have to use STR_TO_DATE to convert a string into a DATETIME. If the time portion is not included in the string, it will default to midnight. Meaning '2010-10-20' == '2010-10-20 00:00:00'.

Your second requirement isn't clear about when the times are applied. If 13:20 as of the start date, and 01:10 as of the end - use:

WHERE FROM_UNIXTIME(your_column) BETWEEN '2010-10-20 13:20:00' 
                                     AND '2010-10-22 01:10:00'

Last, using the FROM_UNIXTIME function on a column will render an index on the column useless.

To fetch records based on TIME only:

WHERE TIME(FROM_UNIXTIME(your_column)) BETWEEN '01:15:00' 
                                           AND '01:15:59' 

Regards