在MySQL中进行时区处理的最佳实践是什么? [英] What is best practice for timezone handling in MySQL?

问题描述

此问题已被问过，但没有找到我想要的答案.我将所有日期存储在UTC/GMT的MYSQL中.当我为引用时间的用户提取数据时，最好使用CONVERT_TZ构造...

This has been asked before but not the answer I am looking for. I am storing all my dates in MYSQL in UTC/GMT. When I extract data fora user that references time is it better to use the CONVERT_TZ construct...

SELECT CONVERT_TZ(mytime,'UTC',usertimezone) as mytime FROM table

还是最好在Mysql中临时设置会话区域，然后再进行常规查询?

or is it better to temporarily set the session zone in Mysql and then do normal queries?

SET time_zone = usertimezone;

如果我使用第二个，是否对每个用户会话都执行一次，或者如果我不使用持久打开，是否需要在每次查询之前进行设置?

And if I use the second, do I just do that once for each user session or if I am not using a persistent open, do I need to set it before each query?

推荐答案

如果您的数据存储在TIMESTAMP类型的列中，那么您应该SET time_zone，并且MySQL会在检索/插入时自动将UTC转换为UTC/从UTC转换为-不需要做更多的事情.这是推荐的方法.

If your data is stored in TIMESTAMP type columns, then you should SET time_zone and MySQL will automatically convert to/from UTC on retrieval/insertion—you don't need to do anything more. This is the recommended approach.

这篇关于在MySQL中进行时区处理的最佳实践是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！