IST 比 UTC 早 5.30 小时,因此 IST 13 日开始时,即2015-03-13 : 00:00:00
its 2015-03-12 18:30:00
in UTC
mysql> select convert_tz('2015-03-13T00:00:00+00:00','+00:00','+05:30') ;
+-----------------------------------------------------------+
| convert_tz('2015-03-13T00:00:00+00:00','+00:00','+05:30') |
+-----------------------------------------------------------+
| 2015-03-12 18:30:00 |
+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
当 13 以 IST 结尾时,即2015-03-13 : 23:59:59
its 2015-03-13 18:29:59
in UTC
mysql> select convert_tz('2015-03-13T23:59:59+00:00','+00:00','+05:30') ;
+-----------------------------------------------------------+
| convert_tz('2015-03-13T23:59:59+00:00','+00:00','+05:30') |
+-----------------------------------------------------------+
| 2015-03-13 18:29:59 |
+-----------------------------------------------------------+
因此,如果您在 IST 中获取 13 日的数据,您将需要搜索此日期范围内的数据。
所以条件如下 -
s.created_at
between convert_tz('2015-03-13T00:00:00+00:00','+00:00','+05:30')
and convert_tz('2015-03-13T23:59:59+00:00','+00:00','+05:30');
由于您在选择时进行转换,因此它将返回所有第 13 个数据。