MySQL 日期和时间函数中使用的时区会导致冲突/问题吗?

发布于 2024-12-06 13:46:45 字数 1685 浏览 0 评论 0原文

我有一个网站,允许访问者查看内容,并可以选择让他们注册和登录(这允许他们提交内容)。

  • 每当用户提交内容时,我都会在 content 表中执行 INSERT INTO 查询,并使用 UNIX_TIMESTAMP() 来存储时间戳在 content_timestamp 列中(这是提交内容的时间)。

  • 我还允许用户从 usercp 的下拉菜单中选择一个时区(支持 http://php.net/manual/en/timezones.php 中的所有时区) - 以及users 表中的列 (user_timezone) 会随之进行UPDATE,因此所有显示的日期都会调整为该日期(如果选择)。< /p>

  • 在显示内容的页面上,我显示格式化日期(提交内容时的日期) - 如果他们未登录或已登录且未选择时区(在 usercp 中),则我将其格式化为 GMT,否则我在那里使用选定的时区,例如:


<?php
if (!is_logged_in() || is_logged_in() && empty($row['user_timezone'])) {
   echo gmdate('d/m/y', $row['content_timestamp']) . ' GMT'; //in GMT
} else {
   date_default_timezone_set($row['user_timezone']);
   echo date('d/m/y', $row['content_timestamp']); //use the users timezone
} 
?>

现在一切都很好,但现在我计划每周和每月安排一个 cronjob(这将是每周特色内容和每月特色内容),它将访问一个 php 文件,该文件将执行 INSERT INTO 查询(评分最高的内容)并将其添加到 featured 表中 - 以及 UNIX_TIMESTAMP() (featured_timestamp - of什么时候已添加)。

cronjob 设置为在每周第一天格林威治标准时间午夜 12 点和每月第一天格林威治标准时间午夜 12 点访问 php 文件。

...现在解决问题:

我通过使用 MySQL 日期和时间函数比较时间戳来显示特色内容,如下所示(下面的示例将返回每周特色内容):

SELECT * FROM featured WHERE WEEK(FROM_UNIXTIME(featured_timestamp), 1) = WEEK(UNIX_TIMESTAMP(), 1) AND
FROM_UNIXTIME(featured_timestamp, '%Y %M') = FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %M')

所以这是否会导致任何时区冲突/问题,因为 cronjob 正在 GMT 访问文件,而我不知道什么时区 MySQL WEEK()DATE_FORMAT( )使用(只是好奇,因为这意味着像上面这样的比较查询可能会产生不正确的结果)?

I have a website which allows visitors to view content, and has the option for them to register and login (which gives them permission to submit content).

  • Whenever content is submitted by users I do a INSERT INTO query in the content table, I use UNIX_TIMESTAMP() to store the timestamp in the column content_timestamp (which is the time of when the content is submitted).

  • I also allow users to optionally select a timezone (supports all timezones from http://php.net/manual/en/timezones.php) from a dropdown menu in there usercp - and a column (user_timezone) in the users table gets UPDATE'd with that, so all displayed dates are adjusted to that (if selected).

  • One the page which the content is displayed I display the formatted date (of when the content is submitted) - if they are not logged or have logged in and not selected a timezone (in the usercp) I format it in GMT otherwise I use there selected timezone e.g.:


<?php
if (!is_logged_in() || is_logged_in() && empty($row['user_timezone'])) {
   echo gmdate('d/m/y', $row['content_timestamp']) . ' GMT'; //in GMT
} else {
   date_default_timezone_set($row['user_timezone']);
   echo date('d/m/y', $row['content_timestamp']); //use the users timezone
} 
?>

Now everything is fine but now I'm planning on schedulling a cronjob weekly and monthly (this will be the weekly featured and monthly featured content) which will access a php file which will do a INSERT INTO query (of top rated content) and add it to the featured table - along with the UNIX_TIMESTAMP() (featured_timestamp - of when it was added).

The cronjob is set to access the php file the first day of every week 12 am midnight GMT and the first day of every month 12 am midnight GMT.

...Now getting to the problem:

I'm displaying the featured content by comparing timestamps using the MySQL Date and Time functions like so (below example will return the weekly featured content):

SELECT * FROM featured WHERE WEEK(FROM_UNIXTIME(featured_timestamp), 1) = WEEK(UNIX_TIMESTAMP(), 1) AND
FROM_UNIXTIME(featured_timestamp, '%Y %M') = FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %M')

So will this cause any timezone conflicts/issues seeing as the cronjob is accessing the file in GMT and I'm unaware of what timezone MySQL WEEK() and DATE_FORMAT() use (just curious as that would mean comparison queries like the above could produce incorrect results)?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。
列表为空,暂无数据
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文