CODEWITHSUNDEEP
mysqltimestamp
Cesar Bielich
Cesar Bielich

Reputation: 4945

Select last 7 days from unix timestamp mysql

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

1488693506
1488576676
1488575917
1487563577
1487563170
1487352348
1487352291
1487207322

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?

Upvotes: 4

Views: 12399

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23361

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));

Upvotes: 13

Related Questions