Search code examples
phpmysqlunix-timestamp

select rows from last month using unix

I'm trying to select all rows from a table from last month basically like this guy's asking: MySQL select all rows from last month until (now() - 1 month), for comparative purposes

But I'm using unix timespamp in the date column, so the date is like this: 1538330400

So I started to use (select rows with date equal to or earlier than today):

SELECT * FROM ns_trainingame t WHERE t.date <= UNIX_TIMESTAMP(NOW());

It worked just fine.

Then, after some research I found some people using a few codes to NOT UNIX TIMESTAMPS and tried to adapt it to solve my problem.

SELECT * 
FROM ns_trainingame t 
WHERE t.date >= UNIX_TIMESTAMP(DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY)) 
  AND t.date <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MONTH))");

But is giving me a wrong number of rows in those conditions.

What can I do?

Solution

  • If you are trying to get all results from the last month (as of posting, August 30-September 30), this is probably the easiest way. Use FROM_UNIXTIME to convert your timestamp to a date and then you can compare it directly to other dates:

    SELECT * 
FROM ns_trainingame t 
WHERE FROM_UNIXTIME(t.date) BETWEEN CURDATE() - INTERVAL 1 MONTH AND CURDATE()