populate time zone table

[rka]

Hello,

  By default the mysql database does not have the time zone information populated.

  You have to populate it manually by running  mysql_tzinfo_to_sql on the machine mysql is installed.

  For mysql on google cloud, how do I do that? Is there a way to access the box ?

Thanks,

rka

  

[Ken Ashcraft]

On Thu, Apr 5, 2012 at 7:39 AM, rka <rkag...@gmail.com> wrote:

Hello,

  By default the mysql database does not have the time zone information populated.

Can you explain what problem you are trying to solve with the time zone info?  Is this something that could be solved at the application level?

 

  You have to populate it manually by running  mysql_tzinfo_to_sql on the machine mysql is installed.

  For mysql on google cloud, how do I do that? Is there a way to access the box ?

No, you can't access the machine running your mysql instance.

Timezones are tricky, and the machine's timezone is probably not the one that you want.  In general, it seems better to store things in UTC and present them to the user in the user's timezone.  Both Java and Python have facilities for that.

Ken

[Rajesh Agrawal]

Wanted to use the convert_tz functionality of mysql to convert the datetime to a standard timezone before saving it in the database.

This can definitely be done at the app level in java / python. However, they also rely on the machine timezone info no?

[Santiago López de Haro]

Well, being able to do

CONVERT_TZ(dt, 'GMT', 'US/Eastern')

would be great. Why is this such an issue?

[Jaap Taal]

I you want to GROUP BY dates in timezone X, but the dates are stored in UTC.

Did anyone ever solve this?

[David Newgas]

If you have stored the column to group as a DATETIME or TIMESTAMP type, or if you have stored as a DATE and also have the TIME in a different then it is possible - just convert to the desired timezone with CONVERT_TZ before grouping etc. If you have only stored the DATE and the date was determined in UTC then you cannot do this, as you don't have enough info to determine which day it occured in the desired timezone.

Note that this question actually has nothing Cloud SQL specific - this simply applies to MySQL in general. Because the MySQL community is much larger than the Cloud SQL community you are better off asking questions like this in a general MySQL forum.  I recommend Stack Overflow, where this question was already asked and answered several times.

David

On Thu, Feb 11, 2016 at 11:45 PM, Jaap Taal <ja...@q42.nl> wrote:

I you want to GROUP BY dates in timezone X, but the dates are stored in UTC.

Did anyone ever solve this?

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/cf4c26b2-b9f7-4b33-9e93-6051ebfbca0f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[geoffre...@gmail.com]

The issue is that the MySQL time zone tables are not populated by default and need to be populated on the database instance prior to using the `CONVERT_TZ` function.  See "Populating the Time Zone Tables" here:

     https://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html

Has anyone found a solution to this issue?

On Friday, February 12, 2016 at 11:32:06 AM UTC-6, David Newgas wrote:

If you have stored the column to group as a DATETIME or TIMESTAMP type, or if you have stored as a DATE and also have the TIME in a different then it is possible - just convert to the desired timezone with CONVERT_TZ before grouping etc. If you have only stored the DATE and the date was determined in UTC then you cannot do this, as you don't have enough info to determine which day it occured in the desired timezone.

Note that this question actually has nothing Cloud SQL specific - this simply applies to MySQL in general. Because the MySQL community is much larger than the Cloud SQL community you are better off asking questions like this in a general MySQL forum.  I recommend Stack Overflow, where this question was already asked and answered several times.

David

On Thu, Feb 11, 2016 at 11:45 PM, Jaap Taal <ja...@q42.nl> wrote:

I you want to GROUP BY dates in timezone X, but the dates are stored in UTC.

Did anyone ever solve this?

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.