In SQL, does SET time_zone work for a table or the entire database?

Question

In SQL, does SET time_zone work for a table or the entire database?

Navigation

#1 by (0 votes)

1

When exporting a MySQL table, this was the result:

-- phpMyAdmin SQL Dump
-- version 4.4.10
-- http://www.phpmyadmin.net
--
-- Servidor: localhost:8889
-- Tiempo de generación: 18-04-2018 a las 21:18:37
-- Versión del servidor: 5.5.42
-- Versión de PHP: 7.0.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "-05:00";

...

My question is: Does this only alter the table that is created or the entire database?

mysql php

asked by Sebastian Gutierrez 18.04.2018 в 21:55

source

1 answer

Doubt about sqlite in java How to make the clip-path move with the image?

score 0 · Accepted Answer

To answer your question it is useful to understand that MySQL handles several types of time zone settings:

system time zone

current server time zone

time zone per connection

Consequently, when you use:

SET time_zone = "-05:00";

what you do is set a time zone per connection . That is, the possibility 3 mentioned above:

Time zones per connection

Each client that connects has its   own time zone configuration, given by the session variable    time_zone Initially, the session variable takes its value from the   global variable time_zone , but the customer can change their own   Time zone with this statement:
SET time_zone = timezone;
MySQL Documentation

To complete the information, I will put you as the other configurations work:

The system's time zone.

When the server starts, it tries to determine the time zone of the   host machine and use it to set the system variable    system_time_zone The value does not change after that.

You can set the system time zone for the MySQL server to   start with the --timezone = timezone_name option in mysqld_safe .   You can also set it by setting the environment variable TZ   before starting mysqld . The allowed values for --timezone   or TZ depend on the system. Consult your system documentation   operative to see what values are acceptable.

MySQL Documentation

The current time zone of the server.

The global system variable time_zone indicates the time zone in which the server is   currently operating. The initial value for time_zone is 'SYSTEM' ,   which indicates that the time zone of the server is the same as the zone   system time.

The time zone value of the initial global server can be   explicitly specify at the beginning with the option    --default-time-zone = timezone on the command line, or you can use   the following line in an options file:
default-time-zone='timezone'
If you have the privilege SUPER , you can set the value of the zone time of the global server at run time with this Instruction:
SET GLOBAL time_zone = timezone;
MySQL Documentation

Conclusion

From all the above we can deduce that the time zone is established at the server level, not at the table level. And that the scope of it can be partial (per session) or global, as the case may be.