結論
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')"