Want to run a query which gives me results between two dates. I have timestamp in unix epoch format

I have got timestamps in epoch UNIX format. I want to run a query by directly giving date and not timestamp. How is that possible?

SELECT FROM_UNIXTIME(timestamp)
FROM report_data
WHERE timestamp = '1399376713'

I used this to convert to human readable format.

My database is something like this

timestamp event_type flags 1399357862 701 null

I want to give a particular date in my query and get the result.

Solution

It's possible using the FROM_UNIXTIME function.

This assumes that your table contains columns in DATETIME or TIMESTAMP, and you are wanting to supply 32-bit integer values in the query.

For example:

SELECT ...
  FROM mytable t
 WHERE t.datetime_col >= FROM_UNIXTIME( ? )
   AND t.datetime_col <  FROM_UNIXTIME( ? )

The integer values supplied as arguments to the FROM_UNIXTIME function will be interpreted as unix-style "seconds since epoch" integer values, and be converted to a DATETIME value using the current timezone setting of the client connection.

This approach will enable MySQL to use a range scan operation using an index with a leading column of datetime_col.

What's not at all clear is what the datatype of your column is, and what values you want to supply in the query. If the columns is datatype DATE, DATETIME or TIMESTAMP (which would be the normative pattern for storing date/time data), then you can specify date literals in standard MySQL format, 'YYYY-MM-DD HH:MM:SS'.

 WHERE t.timestamp_col >= '2015-02-11 07:00' 
   AND t.timestamp_col <  '2015-02-11 23:30:00'

If you are storing the "timestamp" as an integer value, then you will need the right side of the predicates to return an integer value, e.g.

 WHERE t.integer_col  >= UNIX_TIMESTAMP('2015-02-10')
   AND t.integer_col  <  UNIX_TIMESTAMP('2015-02-10' + INTERVAL 24 HOUR)