MySQL DATETIME或TIMESTAMP值是通过$wpdb以UTC格式检索的吗?

时间:2019-05-30 作者:Flimm

我 h类一ve 一 cust型om级 MySQL t型一ble w我t型h类 t型wo colum级ns t型h类一t型 look l我ke t型h类我s:

&#x个A.;&#x个A.;
`t型我m级est型一m级p_cre一t型ed` T我MESTA.MP DEFA.ULT CURRENT_T我MESTA.MP,&#x个A.;`d一t型et型我m级e_cre一t型ed`  DA.TET我ME DEFA.ULT CURRENT_T我MESTA.MP,&#x个A.;
&#x个A.;&#x个A.;

Wh类en ret型r我ev我ng级 t型h类ese v一lues us我ng级 $wpdb, 我 would l我ke t型o know 我n wh类一t型 t型我m级ezone t型h类e result型 w我ll be, 一nd wh类et型h类er 我 c一n h类一ve t型h类e result型 be 我n UTC:

&#x个A.;&#x个A.;
$row = $wpdb-&g级t型;g级et型_row(“”SELECT * FROM `t型est型`“”, A.RRA.Y_A.);&#x个A.;v一r_ex个port型( $row );&#x个A.;
&#x个A.;&#x个A.;B一ckg级round knowledg级e:&#x个A.;&#x个A.;

我 know t型h类一t型 WordPress runs t型h类我s l我ne w我t型h类 every request型, reg级一rdless of t型h类e conf我g级ur一t型我on of PHP or MySQL or t型h类e server, wh类我ch类 一ffect型s bu我lt型-我n PHP funct型我ons:

&#x个A.;&#x个A.;
d一t型e_def一ult型_t型我m级ezone_set型( \'UTC\' );&#x个A.;
&#x个A.;&#x个A.;

我 know t型h类一t型 一dm级我n我st型r一t型ors c一n select型 一 t型我m级ezone 我n t型h类e 一dm级我n 我nt型erf一ce, wh类我ch类 g级et型 st型ored 我n t型h类e d一t型一b一se, 一nd c一n be ret型r我eved us我ng级 g级et型_opt型我on( \'t型我m级ezone_st型r我ng级\' ).

&#x个A.;&#x个A.;

我 know t型h类一t型 t型h类e server 一nd MySQL 我t型self m级我g级h类t型 not型 be conf我g级ured t型o use UTC. Here 我s 一 query prov我ng级 t型h类一t型 UTC 我s not型 used h类ere:

&#x个A.;&#x个A.;
m级ysql&g级t型; select型 now(), ut型c_t型我m级est型一m级p();&#x个A.;+---------------------+---------------------+&#x个A.;| now()               | ut型c_t型我m级est型一m级p()     |&#x个A.;+---------------------+---------------------+&#x个A.;| 2.01.9-05.-3.0 1.9:2.6.:04. | 2.

1 个回复
最合适的回答,由SO网友:Flimm 整理而成

Summary:

WordPress does run date_default_timezone_set( \'UTC\' );. It does have a timezone option modifiable by the admins and retrieved using get_option( \'timezone_string\' ). However, none of that has any effect on the query run through $wpdb. You should get the same results from $wpdb as if you had run the MySQL query without WordPress.

Details on how MySQL works:

This doesn\'t necessarily mean that the value returned will be in UTC, however. That depends on the configuration of the server and of MySQL, and whether you used TIMESTAMP or DATETIME. As long as you never change the configuration of the server, you shouldn\'t notice any differences (in terms of timezones) between TIMESTAMP or DATETIME.

mysql> SELECT `timestamp_created`, `datetime_created` FROM `test`;
+---------------------+---------------------+
| timestamp_created   | datetime_created    |
+---------------------+---------------------+
| 2019-05-30 20:31:04 | 2019-05-30 20:31:04 |
+---------------------+---------------------+

As long as you never change the configuration of the server, you can get the number of seconds since the UNIX epoch in UTC like this, both will give the same correct result:

SELECT UNIX_TIMESTAMP(`timestamp_created`) unix_ts, UNIX_TIMESTAMP(`datetime_created`) unix_dt FROM `test`;
+------------+------------+
| unix_ts    | unix_dt    |
+------------+------------+
| 1559244664 | 1559244664 |
+------------+------------+

As soon as the timezone configuration of MySQL or the server does change, you will notice a difference between TIMESTAMP and DATETIME:

mysql> SET time_zone = \'-8:00\';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `timestamp_created`, `datetime_created` FROM `test`;
+---------------------+---------------------+
| timestamp_created   | datetime_created    |
+---------------------+---------------------+
| 2019-05-30 11:31:04 | 2019-05-30 20:31:04 |
+---------------------+---------------------+

You can see that even though we ran it on the same table with the same values as the earlier query, we got a different result in the first column, but unexpectedly the same result as earlier in the second column. This is because TIMESTAMP (the first column) recognises the change in the configured timezone, and is converting to the new timezone on display. Behind the scenes, TIMESTAMP is stored as the number of seconds since the Unix Epoch in UTC. However, DATETIME just stores the date time naively as is, without reference to any timezone information, so it\'s giving us the same result, even though we changed the timezone in MySQL.

UNIX_TIMESTAMP knows how to behave correctly with TIMESTAMP values. With DATETIME values, it makes the assumption that the value is in the timezone of the current configuration, which in this case is a false assumption:

mysql> SELECT UNIX_TIMESTAMP(`timestamp_created`) unix_ts, UNIX_TIMESTAMP(`datetime_created`) unix_dt FROM `test`;
+------------+------------+
| unix_ts    | unix_dt    |
+------------+------------+
| 1559244664 | 1559277064 |
+------------+------------+

Recommendation:

If you want to use DEFAULT CURRENT_TIMESTAMP, use the TIMESTAMP type not the DATETIME type if you care about timezone correctness. When retrieving the value, use UNIX_TIMESTAMP(`timestamp`), your code can easily convert that to a human-readable UTC date-time string.

相关推荐

使用MySQL添加导航菜单项

我想使用PHP将Wordpress nav菜单项插入MySQL。我不认为我可以使用wp\\u create\\u nav\\u menu(),因为我需要从类别中构建一个初步菜单(超过100只猫!)然后,较低级别的管理员可以修改/调整/优化。我不认为wp\\u create\\u nav\\u menu()实际上更新了MySQL数据库。我当然能弄明白,但我只是想知道是否有人能给我一个先发制人的机会。Update wp_posts Update wp_term_relationships u