mysql从UTC转换为IST [英] mysql converting from UTC to IST

问题描述

我有一个数据库，以UTC时间戳存储记录.我想根据当地时间标准(IST)来获取它们.

I have a database storing records in UTC timestamp. I want to fetch them in terms of local time standard(IST).

通过提及某些推荐，我就这样尝试过.

By referring to some referral i tried like this.

select date(convert_tz(sa.created_at,'+00:00','+05:30')) as date,count(*) as hits from session_acts sa, sessions s where sa.session_id = s.id and s.created_at between convert_tz('2015-03-12T11:33:00+00:00','+00:00','-05:30') and convert_tz('2015-03-13T11:33:00+00:00','+00:00','-05:30') group by date;

但这会导致

+------------+------+
| date       | hits |
+------------+------+
| 2015-03-12 |   94 |
| 2015-03-13 |   34 |
+------------+------+

我只想显示在13日请求的匹配.我要去哪里错了??

I want to display only hits that are requested on 13th. Where i am going wrong.?

推荐答案

IST比UTC早5.30小时，因此，在IST中第13位开始时，即2015-03-13 : 00:00:00，其在UTC中的2015-03-12 18:30:00

IST is 5.30 hours ahead of UTC, so when 13th starts in IST i.e. 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)

当IST中的13结束时，即2015-03-13 : 23:59:59的UTC中的2015-03-13 18:29:59结束

And when 13 ends in IST i.e. 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个数据，您需要在此日期范围内搜索数据.

So yo get the data in IST for 13th you will need to search data within this range of dates.

所以条件如下-

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个数据.

and since you are doing conversion at the time of select so it will return all 13th data.

这篇关于mysql从UTC转换为IST的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！