PHP切换时区/检索MySQL数据

I display a system status table on my site that has a row for each of the last 14 days (including day). Then for each column, I select any events that have occurred for that day from a MySQL database.

我在我的网站上显示一个系统状态表,其中包含过去14天(包括日期)中每一天的行。然后,对于每一列,我从MySQL数据库中选择当天发生的任何事件。

All data in MySQL is stored in UTC and PHP is operating under UTC as well. I'm having two issues:

MySQL中的所有数据都以UTC格式存储,PHP也在UTC下运行。我有两个问题:

If the user is in Eastern time for example, the grid was updating to display tomorrow's date even though it was only 8pm for the Eastern user. I think I've solved that by doing this:

例如,如果用户在东部时间,则网格正在更新以显示明天的日期,即使东部用户仅在晚上8点。我想通过这样做我已经解决了这个问题:
```
while ($i < 14)
    {       
        //SET STATUS DATE
        $status_date = date("Y-m-d H:i:s", strtotime("-$i day"));

        //LOCALIZE TIMES    
        $status_date = new DateTime($status_date);
        $status_date->setTimeZone(new DateTimeZone('America/New_York'));

        echo $status_date->format("F j, Y");
    }
```
The next issue I'm having is on the search for events. Each event has an INCIDENT_START field in the database and I query like this:

我遇到的下一个问题是搜索事件。每个事件在数据库中都有一个INCIDENT_START字段,我这样查询:
```
SELECT INCIDENT_STATUS FROM TABLE_system_status WHERE SYSTEM_AFFECTED='WEBSITE' AND INCIDENT_START BETWEEN '$start_time' AND '$end_time' ORDER BY INCIDENT_START DESC LIMIT 1
```

Here's my issue. If I use $start_time = $status_date->format("Y-m-d 00:00:00") and $end_time = $status_date->format("Y-m-d 23:59:59"), incidents are not displayed on the correct day within the table.

这是我的问题。如果我使用$ start_time = $ status_date-> format(“Ymd 00:00:00”)和$ end_time = $ status_date-> format(“Ymd 23:59:59”),则事件不会在正确的日期显示桌子。

Example: An event in the database has an INCIDENT_START time of 2013-03-19 01:02:01 UTC which is equal to 2013-03-18 21:02:01 EDT. However, in this case, it's still showing up on the March 19th column of my table.

示例:数据库中的事件的INCIDENT_START时间为2013-03-19 01:02:01 UTC,等于2013-03-18 21:02:01 EDT。但是,在这种情况下,它仍然出现在我桌子的3月19日专栏中。

Bottom line....I need to be able to get events from 00:00:00 to 23:59:59 in the user's local time even though they're in UTC in the database. I have a feeling I'm over complicating this but I've tried a bunch of different combinations and can't seem to get it right. Really appreciate any help!

底线....我需要能够在用户当地时间从00:00:00到23:59:59获取事件,即使它们在数据库中是UTC。我有一种感觉,我已经过度复杂了,但我尝试了一堆不同的组合,似乎无法做到正确。真的很感激任何帮助!

2 个解决方案

#1

You need to set $status_date in the user's local timezone and convert to UTC.

您需要在用户的本地时区设置$ status_date并转换为UTC。

$status_date = new DateTime("today -$i day", new DateTimeZone('America/New_York'));
$status_date->setTimeZone(new DateTimeZone('UTC'));

$start_time = $status_date->format("Y-m-d H:i:s");
$status_date->add(new DateInterval('PT23H59M59S'));
$end_time = $status_date->format("Y-m-d H:i:s");

#2

MySQL can do it for you:

MySQL可以为你做到:

SELECT CONVERT_TZ(INCIDENT_STATUS, 'UTC', 'EST') ...

relevant docs: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz

相关文档:https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz

#1