طبقه بندی تاریخ به «دیروز»، «هفته پیش» و .. در MySQL

خب دوستان یک سوال دارم راجع به group کردن سطر ها مبنی بر تاریخ (timestamp) به دیروز، هفته پیش، ماه پیش و ...

جدولی که دارم به صورت ساده شده یک همچین چیزی میشه:

// mytable
+----+------------+
| id | date_time  |
+----+------------+
| 1  | 1464136759 | -- 5 days ago
| 2  | 1464436759 | -- 2 days ago
| 3  | 1464538248 | -- 6 hours ago
+----+------------+

مقادیر 5 و 2 روز پیش یا 6 ساعت پیش اینا از لحظه فعلی حساب شده.

کوئری که نوشتم به این صورته:

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

که خروجیش میشه این:

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

همونطور که میبینید خروجی کلا اشتباه محاسبه میشه و تاریخ ها درست تبدیل نشدن. چیزی که مد نظرم هست اینه و باید خروجی به این شکل باشه:

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

ممنون میشم راهنمایی کنید چطوری میتونم کوئریم رو تغییر بدم که همچین خروجی بگیرم؟

کوئری که نیاز دارید به این شکل باید باشه:

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;

نکته اول این هست که نیازی نیست که unix تایم رو به date تبدیل کنید و میشه با curdate() مقایسش کنید و ختلافش رو در قالب روز/هفته/ماه و سال با date_sub() محاسبه کنید.

نکته دوم هم اینکه واژه range یک کلمه رزرو شده هست توی SQL، پس یا باید از یک کلمه دیگه استفاده کنید یا اونو با بک‌تیک escape کنید.

در MySQL، شما می‌توانید از توابع تاریخی مختلف استفاده کنید تا تاریخ‌ها را بر اساس دیروز، هفته پیش و... طبقه‌بندی کنید. این توابع معمولاً بر روی مقادیر تاریخ و زمان عملیات مورد نظر انجام می‌دهند. در ادامه، نمونه‌هایی از تابع DATE_SUB را برای طبقه‌بندی تاریخ‌ها در MySQL آورده‌ام:

1. دیروز:

   SELECT * FROM table_name
   WHERE date_column = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

2. هفته پیش:

   SELECT * FROM table_name
   WHERE date_column >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
   AND date_column <= DATE_SUB(CURDATE(), INTERVAL 1 DAY);

3. ماه قبل:

   SELECT * FROM table_name
   WHERE date_column >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
   AND date_column <= DATE_SUB(CURDATE(), INTERVAL 1 DAY);

در اینجا، table_name نام جدول داده‌ها و date_column نام ستون حاوی تاریخ‌ها می‌باشد. تابع DATE_SUB با مقادیر تاریخ و اینترول تعیین‌کننده مدت زمان مورد نظر، تاریخ جدیدی را بازگردانده و مقایسه‌های مورد نیاز را بر اساس تاریخ‌های جدید انجام می‌دهد.

همچنین، توجه داشته باشید که نحوه طبقه‌بندی تاریخ‌ها بستگی به نیاز و منطق برنامه شما دارد و می‌توانید از توابع دیگری نیز برای این منظور استفاده کنید.