MySql GMT 日期和时区

发布于 2024-10-10 04:23:57 字数 977 浏览 1 评论 0原文

我有 php 应用程序，我将事件日期存储在时间戳字段（mySQL）中的 GMT +0000 中。请忽略下面的大部分代码，因为它来自我正在处理其他时区的函数 - 它只是为了表明我将任何日期输入转换为 GMT +0000 以存储在数据库中。就这个问题而言，偏移量 = 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);

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

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

因此，如果客户端输入搜索开始日期“2010-12-30 00:01”和搜索结束日期“2010-12-30 23:01”，我该如何传递基本上告诉我们的 SQL 语句数据库搜索日期字段中的值（存储为 GMT + 0000），但添加 13 小时（或负数），因为时区是太平洋/奥克兰。考虑到夏令时等情况，有没有一种 MySQL 方法可以做到这一点...

因此，如果存储了“2010-12-30 08:17:00”的 GMT 日期，并且居住在奥克兰的我的客户使用此记录上方的日期进行搜索应该出现“2010-12-30 08:17:00”，因为 GMT +13 涵盖了搜索。

原文

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.

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).

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...

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.

分享到QQ

分享到微博