MySQL의 현재 시간대를 어떻게 얻습니까?

MySQL의 현재 시간대를 어떻게 얻습니까?

---

MySQL에 그러한 기능이 있는지 아는 사람이 있습니까?

최신 정보

유효한 정보가 출력되지 않습니다 :

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

또는 MySQL 자체가 정확히 time_zone사용 된 것을 알 수없는 경우도 PHP있습니다 SYSTEM.

---

매뉴얼에서 ( 섹션 9.6 ) :

글로벌 및 클라이언트 특정 시간대의 현재 값은 다음과 같이 검색 할 수 있습니다.
mysql> SELECT @@global.time_zone, @@session.time_zone;

편집 는 반환 위 SYSTEM의 MySQL은 도움보다 작은 시스템의 시간대에 슬레이브로 설정되어있는 경우. 당신이 PHP를 사용하고 있기 때문에 MySQL의에서 대답이 경우 SYSTEM, 당신은 어떤 시간대 시스템 요청할 수 있습니다 그것의 를 통해 사용하여 date_default_timezone_get. VolkerK 지적으로 (물론, PHP가 다른 서버에서 실행 될 수 있지만 가정이가는대로, 웹 서버하고있다하는 얘기 DB 서버 가정 으로 설정을 [경우 실제로 에서 ] 같은 시간대가 아닌 거대한 도약.)하지만 MySQL의에로 (즉, 조심은), 당신은 PHP가 사용하는 (시간대를 설정할 수 있습니다date_default_timezone_set)는 OS가 사용중인 것과 다른 값을보고 할 수 있음을 의미합니다. PHP 코드를 제어하고 있다면, 그 작업을 수행하고 있는지 잘 알고 있어야합니다.

그러나 MySQL 서버가 어떤 시간대를 사용하는지에 대한 모든 질문은 서버에 어떤 시간대가 있는지 묻는 것이 데이터베이스의 데이터에 대해 전혀 아무것도 알려주지 않기 때문에 접할 수 있습니다 . 자세한 내용을 읽으십시오 :

추가 토론 :

물론 서버를 제어하는 ​​경우 시간대가 알려진 수량인지 확인할 수 있습니다. 서버를 제어하지 않는 경우 다음 과 같이 연결에 사용되는 시간대를 설정할 수 있습니다 .

set time_zone = '+00:00';

그러면 시간대가 GMT로 설정되어 추가 작업 (예 now():)이 GMT를 사용합니다.

그러나 시간 및 날짜 값은 MySQL의 시간대 정보와 함께 저장 되지 않습니다 .

mysql> create table foo (tstamp datetime) Engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into foo (tstamp) values (now());
Query OK, 1 row affected (0.00 sec)

mysql> set time_zone = '+01:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select tstamp from foo;
+---------------------+
| tstamp              |
+---------------------+
| 2010-05-29 08:31:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = '+02:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select tstamp from foo;
+---------------------+
| tstamp              |
+---------------------+
| 2010-05-29 08:31:59 |      <== Note, no change!
+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-05-29 10:32:32 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2010-05-29 08:32:38 |      <== Note, it changed!
+---------------------+
1 row in set (0.00 sec)

So knowing the timezone of the server is only important in terms of functions that get the time right now, such as now(), unix_timestamp(), etc.; it doesn't tell you anything about what timezone the dates in the database data are using. You might choose to assume they were written using the server's timezone, but that assumption may well be flawed. To know the timezone of any dates or times stored in the data, you have to ensure that they're stored with timezone information or (as I do) ensure they're always in GMT.

Why is assuming the data was written using the server's timezone flawed? Well, for one thing, the data may have been written using a connection that set a different timezone. The database may have been moved from one server to another, where the servers were in different timezones (I ran into that when I inherited a database that had moved from Texas to California). But even if the data is written on the server, with its current time zone, it's still ambiguous. Last year, in the United States, Daylight Savings Time was turned off at 2:00 a.m. on November 1st. Suppose my server is in California using the Pacific timezone and I have the value 2009-11-01 01:30:00 in the database. When was it? Was that 1:30 a.m. November 1st PDT, or 1:30 a.m. November 1st PST (an hour later)? You have absolutely no way of knowing. Moral: Always store dates/times in GMT (which doesn't do DST) and convert to the desired timezone as/when necessary.

---

The query below returns the timezone of the current session.

select timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00'));

---

Simply SELECT @@system_time_zone;

Returns PST (or whatever is relevant to your system).

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);

Which will return the session timezone if it differs from the system timezone.

---

As Jakub Vrána (The creator or Adminer and NotORM) mentions in the comments, to select the current timezone offset in TIME use:

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);

It will return: 02:00:00 if your timezone is +2:00 for that date

I made a cheatsheet here: Should MySQL have its timezone set to UTC?

---

SELECT EXTRACT(HOUR FROM (TIMEDIFF(NOW(), UTC_TIMESTAMP))) AS `timezone`

This will return the timezone as an integer (eg: -6), handling positive or negative times (here is where EXTRACT comes into play: HOUR function alone returns negative timezones as positive).

---

To get Current timezone of the mysql you can do following things:

1. SELECT @@system_time_zone; //from this you can get the system timezone
2. SELECT IF(@@session.time_zone = 'SYSTEM', @@system_time_zone, @@session.time_zone) //This will give you time zone if system timezone is different from global timezone

Now if you want to change the mysql timezone then: 1. SET GLOBAL time_zone = '+00:00' //this will set mysql timezone in UTC 2. SET @@session.time_zone = "+00:00"; //by this you can chnage the timezone only for your particular session

---

Check out MySQL Server Time Zone Support and the system_time_zone system variable. Does that help?

---

select sec_to_time(TIME_TO_SEC( curtime()) + 48000); here you can specify your time differents as sec

---

My PHP framework uses

SET LOCAL time_zone='Whatever'

on after connect, where 'Whatever' == date_default_timezone_get()

Not my solution, but this ensures SYSTEM timezone of MySQL server is always the same as PHP's one

So, yes, PHP is strongly envolved and can affect it

---

The command mention in the description returns "SYSTEM" which indicated it takes the timezone of the server. Which is not useful for our query.

Following query will help to understand the timezone

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) as GMT_TIME_DIFF;

Above query will give you the time interval with respect to Coordinated Universal Time(UTC). So you can easily analyze the timezone. if the database time zone is IST the output will be 5:30

UTC_TIMESTAMP

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.

NOW()

NOW() function. MySQL NOW() returns the value of current date and time in 'YYYY-MM-DD HH:MM:SS' format or YYYYMMDDHHMMSS.uuuuuu format depending on the context (numeric or string) of the function. CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP() are synonyms of NOW().

---

You just need to restart mysqld after altering timezone of System..

The Global time zone of MySQL takes timezone of System. When you change any such attribute of system, you just need a restart of Mysqld.

---

Insert a dummy record into one of your databases that has a timestamp Select that record and get value of timestamp. Delete that record. Gets for sure the timezone that the server is using to write data and ignores PHP timezones.

---

Use LPAD(TIME_FORMAT(TIMEDIFF(NOW(), UTC_TIMESTAMP),’%H:%i’),6,’+') to get a value in MySQL's timezone format that you can conveniently use with CONVERT_TZ(). Note that the timezone offset you get is only valid at the moment in time where the expression is evaluated, since the offset may change over time if you have daylight saving time. Yet the expression is useful together with NOW() to store the offset with the local time, which disambiguates what NOW() yields. (In DST timezones, NOW() jumps back one hour once a year, thus has some duplicate values for distinct points in time).

---

To get the current time according to your timezone, you can use the following (in my case its '+5:30')

select DATE_FORMAT(convert_tz(now(),@@session.time_zone,'+05:30') ,'%Y-%m-%d')

---

It may be as stupid as this

select timediff(current_time(),utc_time())

as is whole mysql

you won't get directly timezone value this way, but if there were no other way...

@@global.time_zone cannot be used in view as it is a variable - and it returns quite unusable value 'SYSTEM' ( i haven't got why somebody bothered with it )

if you need to use your query in a session with changed time_zone ( by session SET TIME_ZONE = ) you will get that with @@session.time_zone if you query @@global.time_zone you get 'SYSTEM' catch 22

if you try datediff, date_sub, or timediff with now() and utc_time() you'll probably run into conversion issues being silently chown by a server

The worst documentation i have ever seen my not help you either.

Excellent work, everybody!

But the something suggested above will probably work at least with some server versions as is mine (5.5.43-37) hosted solution.

---

Try using the following code:

//ASP CLASSIC
Set dbdate = Server.CreateObject("ADODB.Recordset")
dbdate.ActiveConnection = MM_connection
dbdate.Source = "SELECT NOW() AS currentserverdate "
dbdate.Open()
currentdate = dbdate.Fields("currentserverdate").Value
response.Write("Server Time is "&currentdate)
dbdate.Close()
Set dbdate = Nothing

참고URL : https://stackoverflow.com/questions/2934258/how-do-i-get-the-current-time-zone-of-mysql