I am trying to run mysql query to delete rows, but facing a timezone problem. My server is in the US and my users are in Asia. The timestamp which is entered in table is Asian. Can I delete rows with asian time? Here is my query ;
我试图运行mysql查询删除行,但面临时区问题。我的服务器在美国,我的用户在亚洲。在表格中输入的时间戳是亚洲人。我可以删除亚洲时间的行吗?这是我的查询;
mysql_query("DELETE FROM SMS_IN WHERE NOW() > sent_dt");
NOW() is taking US time instead of Asian time.
现在()正在花费美国时间而不是亚洲时间。
2 个解决方案
#1
2
You can use CONVERT_TZ to convert time from user time zone to server time zone:
您可以使用CONVERT_TZ将时间从用户时区转换为服务器时区:
SELECT CONVERT_TZ(NOW(), 'user time zone', 'server time zone');
For example:
mysql_query("DELETE FROM SMS_IN WHERE CONVERT_TZ(NOW(), 'Asia/Hong_Kong', 'America/Montreal') > sent_dt");
#2
0
You can either change the global time_zone, or you can use DATE_ADD or DATE_SUB on NOW() to adjust the number of hours of separation, which is probably known by you (and I hope for the sake of all of us will remain constant [except for DST]).
您可以更改全局time_zone,也可以在NOW()上使用DATE_ADD或DATE_SUB来调整分离小时数,这可能是您所知道的(我希望我们所有人都能保持不变[除外]对于夏令时])。
#1
2
You can use CONVERT_TZ to convert time from user time zone to server time zone:
您可以使用CONVERT_TZ将时间从用户时区转换为服务器时区:
SELECT CONVERT_TZ(NOW(), 'user time zone', 'server time zone');
For example:
mysql_query("DELETE FROM SMS_IN WHERE CONVERT_TZ(NOW(), 'Asia/Hong_Kong', 'America/Montreal') > sent_dt");
#2
0
You can either change the global time_zone, or you can use DATE_ADD or DATE_SUB on NOW() to adjust the number of hours of separation, which is probably known by you (and I hope for the sake of all of us will remain constant [except for DST]).
您可以更改全局time_zone,也可以在NOW()上使用DATE_ADD或DATE_SUB来调整分离小时数,这可能是您所知道的(我希望我们所有人都能保持不变[除外]对于夏令时])。