Stack Stack - 6 months ago 13
MySQL Question

Classifying date as "yesterday", "last week", etc

I have a table like this:

// mytable
+----+------------+
| id | date_time |
+----+------------+
| 1 | 1464136759 | -- 5 days ago
| 2 | 1464436759 | -- 2 days ago
| 3 | 1464538248 | -- 6 hours ago
+----+------------+
-- ^ these are based on current time which is 1464561158


Also I have this query:

SELECT id, CASE DATE(FROM_UNIXTIME(date_time))
WHEN CURDATE() THEN 'today'
WHEN CURDATE() - INTERVAL 1 DAY THEN 'yesterday'
WHEN CURDATE() - INTERVAL 7 DAY THEN 'in last week'
ELSE 'in last month or more'
END range
FROM mytable
WHERE 1


And here is current output:

+----+---------------+
| id | range |
+----+---------------+
| 1 | in last month |
| 2 | in last month |
| 3 | yesterday |
+----+---------------+


As you see my question selects all those unix-times wrong. Why and how can I fix it?

Here is expected output:

+----+--------------+
| id | range |
+----+--------------+
| 1 | in last week |
| 2 | yesterday |
| 3 | today |
+----+--------------+

Answer

Presumably, the logic that you want is like this:

SELECT id,
       (CASE WHEN FROM_UNIXTIME(date_time) >= CURDATE() THEN 'today'
             WHEN FROM_UNIXTIME(date_time) >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN 'yesteray'
             WHEN FROM_UNIXTIME(date_time) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 'in last week'
             ELSE 'in last month or more'
        END) as `range`
FROM mytable
WHERE 1;

Notes:

  • There is no reason to extract the date.
  • Clauses in a case statement are executed in sequence, so the first matching one will return a value.
  • If your original code is failing, then this might fail as well. Your original code should not have been returning only "yesterday".
  • range is a reserved word, so it needs to be escaped.

Here is the SQL Fiddle.

Comments