MySql GMT日期和时区 [英] MySql GMT Dates and Timezones

问题描述

我有一个php应用程序，用于在时间戳字段(mySQL)中的GMT +0000中存储事件日期.请忽略下面的大部分代码，因为它来自我正在处理其他时区的函数-只是为了表明我将输入的任何日期都转换为GMT +0000以便存储在数据库中.出于此问题的目的，偏移量= 0.

I have php application where I store event dates in GMT +0000 within a timestamp field (mySQL). Please ignore the bulk of the code below as it comes from a function where I am dealing with other timezones - its just to show that I am converting any date input to GMT +0000 for storage in the db. For the purposes of this question the offset = 0.

date_default_timezone_set('GMT');
$gmtTimezone = new DateTimeZone('GMT');
$userTimezone = new DateTimeZone('GMT');
$myDateTime = new DateTime(date('Y-m-d H:i'), $gmtTimezone);
$offset = $userTimezone->getOffset($myDateTime);
$event_date = date('Y-m-d H:i', $myDateTime->format('U') + $offset);
$q = "insert into time_zone(dtime) values('".event_date."')";
$r = mysql_query($q);

这似乎很好.因此，即使我的服务器在美国运行，该字段中存储的日期也是伦敦的日期/时间.

And this seems to work well. So even if my server is running in the USA the date stored in the field is London date/time.

现在我的挑战就是这个.我的客户需要找到在日期/时区内发生的事件，但它们将在自己的时区内工作.例如太平洋/奥克兰(GMT + 13).

Now my challange is this. My clients need to locate events that occured within a date/time zone but they will work on their own timezone. For example Pacific/Auckland (GMT + 13).

因此，如果客户端输入开始日期为"2010-12-30 00:01"的搜索日期和结束日期为"2010-12-30 23:01"的搜索，该如何在SQL语句中传递基本上是告诉数据库在日期字段(存储为GMT + 0000)中搜索值，但由于时区是太平洋/奥克兰，所以要增加13小时(或负数).有没有考虑到夏令时等的mySQL方式...

So if the client enters in a search date of start "2010-12-30 00:01" and an end date for the search of "2010-12-30 23:01" how do I pass in a SQL statement that basically tells the database to search for values in the date field (stored as GMT + 0000) but add 13 hours (or negative) since the timezone is Pacific/Auckland. Is there an mySQL way of doing this taking into account Daylight savings etc...

因此，如果存储的格林尼治标准时间为"2010-12-30 08:17:00"，并且我居住在奥克兰的客户搜索的日期高于此记录"2010-12-30 08:17:00"，向上，因为GMT +13涵盖了搜索.

So if a GMT date of "2010-12-30 08:17:00" is stored and my client living in Auckland searches with the dates above this record "2010-12-30 08:17:00" should come up because GMT +13 covers the search.

推荐答案

客户端应先将日期时间从本地时间转换为GMT/UTC，然后再将其发送到数据库.

The client should convert the datetimes from local time to GMT / UTC before sending them to database.

这篇关于MySql GMT日期和时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！