Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Change Google Cloud SQL CURRENT_TIMESTAMP time zone?

I'm using CURRENT_TIMESTAMP in Google Cloud SQL and according to Google, it returns the server's current time.

Currently, with the time that it returns, it looks like the server's at UTC -1 (i.e., in the Atlantic Ocean). This is not very helpful.

How do I change the timezone and have it display the right time (i.e., a time zone of my own choosing) whenever I call CURRENT_TIMESTAMP?

like image 501
user2323030 Avatar asked Oct 21 '14 17:10

user2323030


People also ask

What is the timezone of CURRENT_TIMESTAMP in SQL?

CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE . The time zone offset reflects the current local time of the SQL session. If you omit precision, then the default is 6.

What timezone is CURRENT_TIMESTAMP?

Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine.

How do I change the time zone in SQL Server?

Changing the database timezone SQL Server users typically fetch the local timezone by using the GETDATE() function with insert/update statements or as a default constraint for a datetime column in a select statement.

How do I change the time zone on my database?

Use the ALTER DATABASE SET TIME_ZONE command to change the time zone of a database. This command takes either a named region such as America/Los_Angeles or an absolute offset from UTC. This example sets the time zone to UTC: ALTER DATABASE SET TIME_ZONE = '+00:00';


2 Answers

There is a new way to to change Google cloud SQL time zone https://stackoverflow.com/a/29758598/4121388

Instructions: https://cloud.google.com/sql/docs/mysql-flags

  1. Go to the Google Developers Console.
  2. Create a new Developers Console project, or open an existing project by clicking on the project name.
  3. In the sidebar on the left, click Storage > Cloud SQL to show a list of Cloud SQL instances for the project.
  4. Open your instance and click Edit. If you need to create a new instance, click New instance.
  5. Scroll down to the MySQL Flags section.
  6. Choose flags from the drop-down menu and set their values.
  7. Click Save. You can check your flags under MySQL Flags on the overview page.
like image 181
Juan Peñuelas Martínez Avatar answered Oct 08 '22 11:10

Juan Peñuelas Martínez


Cloud SQL sets the system time as UTC.

You can set the timezone for an individual session with a statement like:

SET @@session.time_zone='-07:00';

You can use named (and daylight savings aware) timezones such as "US/Pacific" if you first set up the timezone table by running a system command like:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -h YOUR_INSTANCE_IP -u root -p mysql
like image 45
David Avatar answered Oct 08 '22 10:10

David