Menu
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;
But it will resulting in
+------------+------+
| date | hits |
+------------+------+
| 2015-03-12 | 94 |
| 2015-03-13 | 34 |
+------------+------+
I want to display only hits that are requested on 13th. Where i am going wrong.?
933
Here's an example to convert EST to UTC timezone by specifying time zone names instead of offset values. mysql> select convert_tz('2020-09-17 03:00:00','US/Eastern','UTC'); Hopefully, now you can convert datetime to UTC in MySQL. Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards.
MySQL CONVERT_TZ() function In MySQL the CONVERT_TZ() returns a resulting value after converting a datetime value from a time zone specified as the second argument to the time zone specified as the third argument. This function returns NULL when the arguments are invalid.
In MySQL, the UTC_TIMESTAMP returns the current UTC date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS. uuuuuu format depending on the usage of the function i.e. in a string or numeric context.
If you're trying to determine the session timezone you can use this query: SELECT IF(@@session. time_zone = 'SYSTEM', @@system_time_zone, @@session. time_zone);
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)
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 |
+-----------------------------------------------------------+
So yo get the data in IST for 13th you will need to search data within this range of dates.
So the condition would be as below -
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');
and since you are doing conversion at the time of select so it will return all 13th data.
162
select convert_tz('2015-03-13T00:00:00+00:00','+00:00','+05:30') ;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
