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

Question

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 1

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.

Answer 2

The issue is you arent checking a range for the week on your third condition. Meaning...

DATE(FROM_UNIXTIME(date_time)) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() - INTERVAL 1 DAY

FIDDLE

Currently your code is saying WHEN CURDATE() - INTERVAL 7 DAY THEN 'in last week' if the date is equal to 7 days ago ONLY. the 25th is not the 22nd so it fails that check. You need to specify a range in order to get it to work.