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?
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));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With