Select last 7 days from unix timestamp mysql

Question

I have never really messed with this type of query so I need some assistance. I have a table with a timestamp column that contains the following

I am using the following mysql statement

SELECT * FROM table WHERE (`timestamp` > DATE_SUB(now(), INTERVAL 7 DAY))

From what I have seen this should return the last 7 days of records that are within the last 7 days from the timestamps I listed, but no records are being returned. What am I doing wrong?

You got any solution ? I have also faced the same where no records are being returned!! — Bandana Sahu, Oct 15 '18 at 13:46

score 13 · Accepted Answer · edited Mar 06 '17 at 14:30

You should use from_unixtime function to convert the date either to the where condition and to see a readable date format:

SELECT from_unixtime(`timestamp`) 
  FROM table 
 WHERE from_unixtime(`timestamp`) > date_sub(now(), interval 7 day);

It will show you:

March, 05 2017 05:58:26
March, 03 2017 21:31:16
March, 03 2017 21:18:37

See it working here: http://sqlfiddle.com/#!9/d60e91/1

EDIT: Assuming the timestamp is indexed, a more optimal way of writing the same thing might be as follows:

SELECT FROM_UNIXTIME(`timestamp`) x
  FROM test_t 
 WHERE timestamp > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY));

Select last 7 days from unix timestamp mysql

1 Answers1