結論

DATEDIFF関数は使わないほうがよい。WHERE句で頑張ろう。

環境とテストデータ

> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.014 sec)

> select id, name, start_at from hoges;
+----+--------------+---------------------+
| id | name         | start_at            |
+----+--------------+---------------------+
|  1 | yesterday 0  | 2024-10-21 00:00:00 |
|  2 | yesterday 1  | 2024-10-21 01:00:00 |
|  3 | yesterday 2  | 2024-10-21 02:00:00 |
|  4 | yesterday 3  | 2024-10-21 03:00:00 |
|  5 | yesterday 4  | 2024-10-21 04:00:00 |
|  6 | yesterday 5  | 2024-10-21 05:00:00 |
|  7 | yesterday 6  | 2024-10-21 06:00:00 |
|  8 | yesterday 7  | 2024-10-21 07:00:00 |
|  9 | yesterday 8  | 2024-10-21 08:00:00 |
| 10 | yesterday 9  | 2024-10-21 09:00:00 |
| 11 | yesterday 10 | 2024-10-21 10:00:00 |
| 12 | yesterday 11 | 2024-10-21 11:00:00 |
| 13 | yesterday 12 | 2024-10-21 12:00:00 |
| 14 | yesterday 13 | 2024-10-21 13:00:00 |
| 15 | yesterday 14 | 2024-10-21 14:00:00 |
| 16 | yesterday 15 | 2024-10-21 15:00:00 |
| 17 | yesterday 16 | 2024-10-21 16:00:00 |
| 18 | yesterday 17 | 2024-10-21 17:00:00 |
| 19 | yesterday 18 | 2024-10-21 18:00:00 |
| 20 | yesterday 19 | 2024-10-21 19:00:00 |
| 21 | yesterday 20 | 2024-10-21 20:00:00 |
| 22 | yesterday 21 | 2024-10-21 21:00:00 |
| 23 | yesterday 22 | 2024-10-21 22:00:00 |
| 24 | yesterday 23 | 2024-10-21 23:00:00 |
| 25 | today 0      | 2024-10-22 00:00:00 |
| 26 | today 1      | 2024-10-22 01:00:00 |
| 27 | today 2      | 2024-10-22 02:00:00 |
| 28 | today 3      | 2024-10-22 03:00:00 |
| 29 | today 4      | 2024-10-22 04:00:00 |
| 30 | today 5      | 2024-10-22 05:00:00 |
| 31 | today 6      | 2024-10-22 06:00:00 |
| 32 | today 7      | 2024-10-22 07:00:00 |
| 33 | today 8      | 2024-10-22 08:00:00 |
| 34 | today 9      | 2024-10-22 09:00:00 |
| 35 | today 10     | 2024-10-22 10:00:00 |
| 36 | today 11     | 2024-10-22 11:00:00 |
| 37 | today 12     | 2024-10-22 12:00:00 |
| 38 | today 13     | 2024-10-22 13:00:00 |
| 39 | today 14     | 2024-10-22 14:00:00 |
| 40 | today 15     | 2024-10-22 15:00:00 |
| 41 | today 16     | 2024-10-22 16:00:00 |
| 42 | today 17     | 2024-10-22 17:00:00 |
| 43 | today 18     | 2024-10-22 18:00:00 |
| 44 | today 19     | 2024-10-22 19:00:00 |
| 45 | today 20     | 2024-10-22 20:00:00 |
| 46 | today 21     | 2024-10-22 21:00:00 |
| 47 | today 22     | 2024-10-22 22:00:00 |
| 48 | today 23     | 2024-10-22 23:00:00 |
| 49 | tomorrow 0   | 2024-10-23 00:00:00 |
| 50 | tomorrow 1   | 2024-10-23 01:00:00 |
| 51 | tomorrow 2   | 2024-10-23 02:00:00 |
| 52 | tomorrow 3   | 2024-10-23 03:00:00 |
| 53 | tomorrow 4   | 2024-10-23 04:00:00 |
| 54 | tomorrow 5   | 2024-10-23 05:00:00 |
| 55 | tomorrow 6   | 2024-10-23 06:00:00 |
| 56 | tomorrow 7   | 2024-10-23 07:00:00 |
| 57 | tomorrow 8   | 2024-10-23 08:00:00 |
| 58 | tomorrow 9   | 2024-10-23 09:00:00 |
| 59 | tomorrow 10  | 2024-10-23 10:00:00 |
| 60 | tomorrow 11  | 2024-10-23 11:00:00 |
| 61 | tomorrow 12  | 2024-10-23 12:00:00 |
| 62 | tomorrow 13  | 2024-10-23 13:00:00 |
| 63 | tomorrow 14  | 2024-10-23 14:00:00 |
| 64 | tomorrow 15  | 2024-10-23 15:00:00 |
| 65 | tomorrow 16  | 2024-10-23 16:00:00 |
| 66 | tomorrow 17  | 2024-10-23 17:00:00 |
| 67 | tomorrow 18  | 2024-10-23 18:00:00 |
| 68 | tomorrow 19  | 2024-10-23 19:00:00 |
| 69 | tomorrow 20  | 2024-10-23 20:00:00 |
| 70 | tomorrow 21  | 2024-10-23 21:00:00 |
| 71 | tomorrow 22  | 2024-10-23 22:00:00 |
| 72 | tomorrow 23  | 2024-10-23 23:00:00 |
+----+--------------+---------------------+
72 rows in set (0.011 sec)

DATEDIFF関数

実際に利用してみると2024-10-22という部分で一致検索されてしまっているような状態。
これではタイムゾーンを考慮した今日とは違ってしまう。

> select NOW();
+---------------------+------------+
| NOW()               | CURDATE()  |
+---------------------+------------+
| 2024-10-22 05:53:29 | 2024-10-22 |
+---------------------+------------+
1 row in set (0.002 sec)

> select id, name, start_at, DATEDIFF(CURDATE(), start_at) as DATEDIFF from hoges;
+----+--------------+---------------------+----------+
| id | name         | start_at            | DATEDIFF |
+----+--------------+---------------------+----------+
|  1 | yesterday 0  | 2024-10-21 00:00:00 |        1 |
|  2 | yesterday 1  | 2024-10-21 01:00:00 |        1 |
|  3 | yesterday 2  | 2024-10-21 02:00:00 |        1 |
|  4 | yesterday 3  | 2024-10-21 03:00:00 |        1 |
|  5 | yesterday 4  | 2024-10-21 04:00:00 |        1 |
|  6 | yesterday 5  | 2024-10-21 05:00:00 |        1 |
|  7 | yesterday 6  | 2024-10-21 06:00:00 |        1 |
|  8 | yesterday 7  | 2024-10-21 07:00:00 |        1 |
|  9 | yesterday 8  | 2024-10-21 08:00:00 |        1 |
| 10 | yesterday 9  | 2024-10-21 09:00:00 |        1 |
| 11 | yesterday 10 | 2024-10-21 10:00:00 |        1 |
| 12 | yesterday 11 | 2024-10-21 11:00:00 |        1 |
| 13 | yesterday 12 | 2024-10-21 12:00:00 |        1 |
| 14 | yesterday 13 | 2024-10-21 13:00:00 |        1 |
| 15 | yesterday 14 | 2024-10-21 14:00:00 |        1 |
| 16 | yesterday 15 | 2024-10-21 15:00:00 |        1 |
| 17 | yesterday 16 | 2024-10-21 16:00:00 |        1 |
| 18 | yesterday 17 | 2024-10-21 17:00:00 |        1 |
| 19 | yesterday 18 | 2024-10-21 18:00:00 |        1 |
| 20 | yesterday 19 | 2024-10-21 19:00:00 |        1 |
| 21 | yesterday 20 | 2024-10-21 20:00:00 |        1 |
| 22 | yesterday 21 | 2024-10-21 21:00:00 |        1 |
| 23 | yesterday 22 | 2024-10-21 22:00:00 |        1 |
| 24 | yesterday 23 | 2024-10-21 23:00:00 |        1 |
| 25 | today 0      | 2024-10-22 00:00:00 |        0 |
| 26 | today 1      | 2024-10-22 01:00:00 |        0 |
| 27 | today 2      | 2024-10-22 02:00:00 |        0 |
| 28 | today 3      | 2024-10-22 03:00:00 |        0 |
| 29 | today 4      | 2024-10-22 04:00:00 |        0 |
| 30 | today 5      | 2024-10-22 05:00:00 |        0 |
| 31 | today 6      | 2024-10-22 06:00:00 |        0 |
| 32 | today 7      | 2024-10-22 07:00:00 |        0 |
| 33 | today 8      | 2024-10-22 08:00:00 |        0 |
| 34 | today 9      | 2024-10-22 09:00:00 |        0 |
| 35 | today 10     | 2024-10-22 10:00:00 |        0 |
| 36 | today 11     | 2024-10-22 11:00:00 |        0 |
| 37 | today 12     | 2024-10-22 12:00:00 |        0 |
| 38 | today 13     | 2024-10-22 13:00:00 |        0 |
| 39 | today 14     | 2024-10-22 14:00:00 |        0 |
| 40 | today 15     | 2024-10-22 15:00:00 |        0 |
| 41 | today 16     | 2024-10-22 16:00:00 |        0 |
| 42 | today 17     | 2024-10-22 17:00:00 |        0 |
| 43 | today 18     | 2024-10-22 18:00:00 |        0 |
| 44 | today 19     | 2024-10-22 19:00:00 |        0 |
| 45 | today 20     | 2024-10-22 20:00:00 |        0 |
| 46 | today 21     | 2024-10-22 21:00:00 |        0 |
| 47 | today 22     | 2024-10-22 22:00:00 |        0 |
| 48 | today 23     | 2024-10-22 23:00:00 |        0 |
| 49 | tomorrow 0   | 2024-10-23 00:00:00 |       -1 |
| 50 | tomorrow 1   | 2024-10-23 01:00:00 |       -1 |
| 51 | tomorrow 2   | 2024-10-23 02:00:00 |       -1 |
| 52 | tomorrow 3   | 2024-10-23 03:00:00 |       -1 |
| 53 | tomorrow 4   | 2024-10-23 04:00:00 |       -1 |
| 54 | tomorrow 5   | 2024-10-23 05:00:00 |       -1 |
| 55 | tomorrow 6   | 2024-10-23 06:00:00 |       -1 |
| 56 | tomorrow 7   | 2024-10-23 07:00:00 |       -1 |
| 57 | tomorrow 8   | 2024-10-23 08:00:00 |       -1 |
| 58 | tomorrow 9   | 2024-10-23 09:00:00 |       -1 |
| 59 | tomorrow 10  | 2024-10-23 10:00:00 |       -1 |
| 60 | tomorrow 11  | 2024-10-23 11:00:00 |       -1 |
| 61 | tomorrow 12  | 2024-10-23 12:00:00 |       -1 |
| 62 | tomorrow 13  | 2024-10-23 13:00:00 |       -1 |
| 63 | tomorrow 14  | 2024-10-23 14:00:00 |       -1 |
| 64 | tomorrow 15  | 2024-10-23 15:00:00 |       -1 |
| 65 | tomorrow 16  | 2024-10-23 16:00:00 |       -1 |
| 66 | tomorrow 17  | 2024-10-23 17:00:00 |       -1 |
| 67 | tomorrow 18  | 2024-10-23 18:00:00 |       -1 |
| 68 | tomorrow 19  | 2024-10-23 19:00:00 |       -1 |
| 69 | tomorrow 20  | 2024-10-23 20:00:00 |       -1 |
| 70 | tomorrow 21  | 2024-10-23 21:00:00 |       -1 |
| 71 | tomorrow 22  | 2024-10-23 22:00:00 |       -1 |
| 72 | tomorrow 23  | 2024-10-23 23:00:00 |       -1 |
+----+--------------+---------------------+----------+
72 rows in set (0.006 sec)

CONVERT_TZ関数

正解は+09:00されたら今日であるレコードなのでid=16〜39のレコードが該当してほしい。

今度はCZDIFFカラムに正しい差分がとれた。

> select
    ->     id,
    ->     name,
    ->     start_at,
    ->     DATEDIFF(CURDATE(), start_at) as DATEDIFF,
    ->     CONVERT_TZ(start_at, "+00:00", "+09:00") as CZ,
    ->     DATEDIFF(CURDATE(), CONVERT_TZ(start_at, "+00:00", "+09:00")) as CZDIFF from hoges;
+----+--------------+---------------------+----------+---------------------+--------+
| id | name         | start_at            | DATEDIFF | CZ                  | CZDIFF |
+----+--------------+---------------------+----------+---------------------+--------+
|  1 | yesterday 0  | 2024-10-21 00:00:00 |        1 | 2024-10-21 09:00:00 |      1 |
|  2 | yesterday 1  | 2024-10-21 01:00:00 |        1 | 2024-10-21 10:00:00 |      1 |
|  3 | yesterday 2  | 2024-10-21 02:00:00 |        1 | 2024-10-21 11:00:00 |      1 |
|  4 | yesterday 3  | 2024-10-21 03:00:00 |        1 | 2024-10-21 12:00:00 |      1 |
|  5 | yesterday 4  | 2024-10-21 04:00:00 |        1 | 2024-10-21 13:00:00 |      1 |
|  6 | yesterday 5  | 2024-10-21 05:00:00 |        1 | 2024-10-21 14:00:00 |      1 |
|  7 | yesterday 6  | 2024-10-21 06:00:00 |        1 | 2024-10-21 15:00:00 |      1 |
|  8 | yesterday 7  | 2024-10-21 07:00:00 |        1 | 2024-10-21 16:00:00 |      1 |
|  9 | yesterday 8  | 2024-10-21 08:00:00 |        1 | 2024-10-21 17:00:00 |      1 |
| 10 | yesterday 9  | 2024-10-21 09:00:00 |        1 | 2024-10-21 18:00:00 |      1 |
| 11 | yesterday 10 | 2024-10-21 10:00:00 |        1 | 2024-10-21 19:00:00 |      1 |
| 12 | yesterday 11 | 2024-10-21 11:00:00 |        1 | 2024-10-21 20:00:00 |      1 |
| 13 | yesterday 12 | 2024-10-21 12:00:00 |        1 | 2024-10-21 21:00:00 |      1 |
| 14 | yesterday 13 | 2024-10-21 13:00:00 |        1 | 2024-10-21 22:00:00 |      1 |
| 15 | yesterday 14 | 2024-10-21 14:00:00 |        1 | 2024-10-21 23:00:00 |      1 |
| 16 | yesterday 15 | 2024-10-21 15:00:00 |        1 | 2024-10-22 00:00:00 |      0 |
| 17 | yesterday 16 | 2024-10-21 16:00:00 |        1 | 2024-10-22 01:00:00 |      0 |
| 18 | yesterday 17 | 2024-10-21 17:00:00 |        1 | 2024-10-22 02:00:00 |      0 |
| 19 | yesterday 18 | 2024-10-21 18:00:00 |        1 | 2024-10-22 03:00:00 |      0 |
| 20 | yesterday 19 | 2024-10-21 19:00:00 |        1 | 2024-10-22 04:00:00 |      0 |
| 21 | yesterday 20 | 2024-10-21 20:00:00 |        1 | 2024-10-22 05:00:00 |      0 |
| 22 | yesterday 21 | 2024-10-21 21:00:00 |        1 | 2024-10-22 06:00:00 |      0 |
| 23 | yesterday 22 | 2024-10-21 22:00:00 |        1 | 2024-10-22 07:00:00 |      0 |
| 24 | yesterday 23 | 2024-10-21 23:00:00 |        1 | 2024-10-22 08:00:00 |      0 |
| 25 | today 0      | 2024-10-22 00:00:00 |        0 | 2024-10-22 09:00:00 |      0 |
| 26 | today 1      | 2024-10-22 01:00:00 |        0 | 2024-10-22 10:00:00 |      0 |
| 27 | today 2      | 2024-10-22 02:00:00 |        0 | 2024-10-22 11:00:00 |      0 |
| 28 | today 3      | 2024-10-22 03:00:00 |        0 | 2024-10-22 12:00:00 |      0 |
| 29 | today 4      | 2024-10-22 04:00:00 |        0 | 2024-10-22 13:00:00 |      0 |
| 30 | today 5      | 2024-10-22 05:00:00 |        0 | 2024-10-22 14:00:00 |      0 |
| 31 | today 6      | 2024-10-22 06:00:00 |        0 | 2024-10-22 15:00:00 |      0 |
| 32 | today 7      | 2024-10-22 07:00:00 |        0 | 2024-10-22 16:00:00 |      0 |
| 33 | today 8      | 2024-10-22 08:00:00 |        0 | 2024-10-22 17:00:00 |      0 |
| 34 | today 9      | 2024-10-22 09:00:00 |        0 | 2024-10-22 18:00:00 |      0 |
| 35 | today 10     | 2024-10-22 10:00:00 |        0 | 2024-10-22 19:00:00 |      0 |
| 36 | today 11     | 2024-10-22 11:00:00 |        0 | 2024-10-22 20:00:00 |      0 |
| 37 | today 12     | 2024-10-22 12:00:00 |        0 | 2024-10-22 21:00:00 |      0 |
| 38 | today 13     | 2024-10-22 13:00:00 |        0 | 2024-10-22 22:00:00 |      0 |
| 39 | today 14     | 2024-10-22 14:00:00 |        0 | 2024-10-22 23:00:00 |      0 |
| 40 | today 15     | 2024-10-22 15:00:00 |        0 | 2024-10-23 00:00:00 |     -1 |
| 41 | today 16     | 2024-10-22 16:00:00 |        0 | 2024-10-23 01:00:00 |     -1 |
| 42 | today 17     | 2024-10-22 17:00:00 |        0 | 2024-10-23 02:00:00 |     -1 |
| 43 | today 18     | 2024-10-22 18:00:00 |        0 | 2024-10-23 03:00:00 |     -1 |
| 44 | today 19     | 2024-10-22 19:00:00 |        0 | 2024-10-23 04:00:00 |     -1 |
| 45 | today 20     | 2024-10-22 20:00:00 |        0 | 2024-10-23 05:00:00 |     -1 |
| 46 | today 21     | 2024-10-22 21:00:00 |        0 | 2024-10-23 06:00:00 |     -1 |
| 47 | today 22     | 2024-10-22 22:00:00 |        0 | 2024-10-23 07:00:00 |     -1 |
| 48 | today 23     | 2024-10-22 23:00:00 |        0 | 2024-10-23 08:00:00 |     -1 |
| 49 | tomorrow 0   | 2024-10-23 00:00:00 |       -1 | 2024-10-23 09:00:00 |     -1 |
| 50 | tomorrow 1   | 2024-10-23 01:00:00 |       -1 | 2024-10-23 10:00:00 |     -1 |
| 51 | tomorrow 2   | 2024-10-23 02:00:00 |       -1 | 2024-10-23 11:00:00 |     -1 |
| 52 | tomorrow 3   | 2024-10-23 03:00:00 |       -1 | 2024-10-23 12:00:00 |     -1 |
| 53 | tomorrow 4   | 2024-10-23 04:00:00 |       -1 | 2024-10-23 13:00:00 |     -1 |
| 54 | tomorrow 5   | 2024-10-23 05:00:00 |       -1 | 2024-10-23 14:00:00 |     -1 |
| 55 | tomorrow 6   | 2024-10-23 06:00:00 |       -1 | 2024-10-23 15:00:00 |     -1 |
| 56 | tomorrow 7   | 2024-10-23 07:00:00 |       -1 | 2024-10-23 16:00:00 |     -1 |
| 57 | tomorrow 8   | 2024-10-23 08:00:00 |       -1 | 2024-10-23 17:00:00 |     -1 |
| 58 | tomorrow 9   | 2024-10-23 09:00:00 |       -1 | 2024-10-23 18:00:00 |     -1 |
| 59 | tomorrow 10  | 2024-10-23 10:00:00 |       -1 | 2024-10-23 19:00:00 |     -1 |
| 60 | tomorrow 11  | 2024-10-23 11:00:00 |       -1 | 2024-10-23 20:00:00 |     -1 |
| 61 | tomorrow 12  | 2024-10-23 12:00:00 |       -1 | 2024-10-23 21:00:00 |     -1 |
| 62 | tomorrow 13  | 2024-10-23 13:00:00 |       -1 | 2024-10-23 22:00:00 |     -1 |
| 63 | tomorrow 14  | 2024-10-23 14:00:00 |       -1 | 2024-10-23 23:00:00 |     -1 |
| 64 | tomorrow 15  | 2024-10-23 15:00:00 |       -1 | 2024-10-24 00:00:00 |     -2 |
| 65 | tomorrow 16  | 2024-10-23 16:00:00 |       -1 | 2024-10-24 01:00:00 |     -2 |
| 66 | tomorrow 17  | 2024-10-23 17:00:00 |       -1 | 2024-10-24 02:00:00 |     -2 |
| 67 | tomorrow 18  | 2024-10-23 18:00:00 |       -1 | 2024-10-24 03:00:00 |     -2 |
| 68 | tomorrow 19  | 2024-10-23 19:00:00 |       -1 | 2024-10-24 04:00:00 |     -2 |
| 69 | tomorrow 20  | 2024-10-23 20:00:00 |       -1 | 2024-10-24 05:00:00 |     -2 |
| 70 | tomorrow 21  | 2024-10-23 21:00:00 |       -1 | 2024-10-24 06:00:00 |     -2 |
| 71 | tomorrow 22  | 2024-10-23 22:00:00 |       -1 | 2024-10-24 07:00:00 |     -2 |
| 72 | tomorrow 23  | 2024-10-23 23:00:00 |       -1 | 2024-10-24 08:00:00 |     -2 |
+----+--------------+---------------------+----------+---------------------+--------+
72 rows in set (0.008 sec)

結果どうすればいいか

SQLで頑張るならば、CONVERT_TZをつかったSQLにしておいたほうがよい。

プログラムから呼ぶことができるならば検索条件に日付でFrom, Toを指定したほうがよい。

# ActiveRecordの例
[5] pry(main)> Hoge.where(start_at: Time.zone.now.beginning_of_day..Time.zone.now.end_of_day)
=>   Hoge Load (2.3ms)  SELECT `hoges`.* FROM `hoges` WHERE `hoges`.`start_at` BETWEEN '2024-10-21 15:00:00' AND '2024-10-22 14:59:59'
[#<Hoge:0x0000aaaae9164948 id: 16, name: "yesterday 15", start_at: Tue, 22 Oct 2024 00:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae91647b8 id: 17, name: "yesterday 16", start_at: Tue, 22 Oct 2024 01:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae9164650 id: 18, name: "yesterday 17", start_at: Tue, 22 Oct 2024 02:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae91644e8 id: 19, name: "yesterday 18", start_at: Tue, 22 Oct 2024 03:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae9164330 id: 20, name: "yesterday 19", start_at: Tue, 22 Oct 2024 04:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae9164178 id: 21, name: "yesterday 20", start_at: Tue, 22 Oct 2024 05:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915ff88 id: 22, name: "yesterday 21", start_at: Tue, 22 Oct 2024 06:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915fe48 id: 23, name: "yesterday 22", start_at: Tue, 22 Oct 2024 07:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915fa60 id: 24, name: "yesterday 23", start_at: Tue, 22 Oct 2024 08:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915f808 id: 25, name: "today 0", start_at: Tue, 22 Oct 2024 09:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915f3a8 id: 26, name: "today 1", start_at: Tue, 22 Oct 2024 10:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915ef20 id: 27, name: "today 2", start_at: Tue, 22 Oct 2024 11:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915ed40 id: 28, name: "today 3", start_at: Tue, 22 Oct 2024 12:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915ea70 id: 29, name: "today 4", start_at: Tue, 22 Oct 2024 13:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915e430 id: 30, name: "today 5", start_at: Tue, 22 Oct 2024 14:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915e2a0 id: 31, name: "today 6", start_at: Tue, 22 Oct 2024 15:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915e138 id: 32, name: "today 7", start_at: Tue, 22 Oct 2024 16:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915df08 id: 33, name: "today 8", start_at: Tue, 22 Oct 2024 17:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915dd50 id: 34, name: "today 9", start_at: Tue, 22 Oct 2024 18:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915dbe8 id: 35, name: "today 10", start_at: Tue, 22 Oct 2024 19:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915da08 id: 36, name: "today 11", start_at: Tue, 22 Oct 2024 20:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915d8a0 id: 37, name: "today 12", start_at: Tue, 22 Oct 2024 21:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915d5f8 id: 38, name: "today 13", start_at: Tue, 22 Oct 2024 22:00:00.000000000 JST +09:00>,
 #<Hoge:0x0000aaaae915d350 id: 39, name: "today 14", start_at: Tue, 22 Oct 2024 23:00:00.000000000 JST +09:00>]
[6] pry(main)>

やってはいけない

こっちが本題というかきっかけなのですが、ransackでこれやってしまうと上手くいきません。

class Hoge < ApplicationRecord
  ransacker :start_at_date_diff, type: :integer do
    Arel.sql("DATEDIFF(NOW(), start_at)")
  end
  def self.ransackable_attributes(auth_object = nil)
    ["start_at", "start_at_date_diff"]
  end
end

# これだと上にあるようなタイムゾーン問題が！
Hoge.ransack(start_at_date_diff_gteq: 1).result.to_sql
=> "SELECT `hoges`.* FROM `hoges` WHERE DATEDIFF(NOW(), start_at) >= 1"

# 日付範囲を指定したほうがいい
now = Time.current
Hoge.ransack(start_at_lt: now.end_of_day, start_at_gteq: (now - 1.day).beginning_of_day).result.to_sql
=> "SELECT `hoges`.* FROM `hoges` WHERE (`hoges`.`start_at` < '2024-10-22 14:59:59' AND `hoges`.`start_at` >= '2024-10-20 15:00:00')"