mysql - 时间戳如何分类？

Question

这是我的表格:

+----+-----------------+
| id |    timestamp    |
+----+-----------------+
| 1  | 1452001861      |    -- yesterday
| 2  | 1452088272      |    -- today
| 3  | 1452088283      |    -- today
| 4  | 1451915461      |    -- last week
| 5  | 1452001861      |    -- yesterday
| 6  | 1452088263      |    -- today
| 7  | 1252388263      |    -- out of {today, yesterday, last week}
| 8  | 1452088312      |    -- today
| 9  | 1452001762      |    -- yesterday
| 10 | 1222388263      |    -- out of {today, yesterday, last week}
| 12 | 1451915459      |    -- last week
+----+-----------------+

现在我想这样选择:

+----+-----------------+---------------+
| id |    timestamp    |     range     |
+----+-----------------+---------------+
| 1  | 1452001861      |  yesterday    |
| 2  | 1452088272      |  today        |
| 3  | 1452088283      |  today        |
| 4  | 1451915461      |  last week    |
| 5  | 1452001861      |  yesterday    |
| 6  | 1452088263      |  today        |
| 7  | 1252388263      |  out          |
| 8  | 1452088312      |  today        |
| 9  | 1452001762      |  yesterday    |
| 10 | 1222388263      |  out          |
| 12 | 1451915459      |  last week    |
+----+-----------------+---------------+
//                             ^ this column isn't a real column

好吧，我所能做的就是选择今天:

SELECT
  id,
  date_time
FROM
  viewed
WHERE
DATE_FORMAT(FROM_UNIXTIME(`date_time`), '%Y-%m-%d') >= (DATE_FORMAT(NOW(), '%y-%m-%d') - INTERVAL 1 DAY)

正如我所说，此查询 ^ 仅选择今天的时间戳。

<小时/>

我的问题:现在我想知道，如何将查询扩展到{今天​​、昨天 - 上周}？

Answer 1

帮助您入门的不完整答案...

SELECT CASE DATE(FROM_UNIXTIME(1452001861)) 
       WHEN CURDATE() - INTERVAL 1 DAY THEN 'yesterday'
       WHEN CURDATE() THEN 'today' END x;