将时间以格林威治标准时间(GMT)存储在内部。所有操作都在GMT中进行。然后,在最后一刻,就在你即将向用户显示结果时,再转换为用户的本地时间。
我建议使用Date::Parse,但是您需要增加其时区偏移量,因为它目前没有印度支那夏令时和日本夏令时等。
use warnings;
use strict;
use Date::Format;
use Date::Parse;
$Time::Zone::Zone{icst} = +7*3600;
$Time::Zone::Zone{jdt} = +9*3600;
while (<DATA>) {
chomp;
warn("$0: failed conversion for $_\n"), next
unless defined(my $time_t = str2time $_);
my @t = gmtime($time_t);
print $_, " => ", strftime("%Y-%m-%d %H:%M:%S", @t), "\n";
}
__DATA__
Sat Mar 06 09:00:00 ICST 2010
Fri Feb 19 19:30:00 JDT 2010
Fri Feb 19 19:30:00 PST 2010
输出:
Sat Mar 06 09:00:00 ICST 2010 => 2010-03-06 02:00:00
Fri Feb 19 19:30:00 JDT 2010 => 2010-02-19 10:30:00
Fri Feb 19 19:30:00 PST 2010 => 2010-02-20 03:30:00
为了支持您想要的查询,请将时间存储在GMT加上偏移量的格式中(即从API的GMT到本地时间)。请注意,下面的代码假设如果str2time
可以解析给定的时间,则strptime
也可以。将循环更改为
my @dates;
while (<DATA>) {
chomp;
warn("$0: failed conversion for $_\n"), next
unless defined(my $time_t = str2time $_);
my $zone = (strptime $_)[-1];
my @t = gmtime($time_t);
push @dates => [ strftime("%Y-%m-%d %H:%M:%S", @t)
, sprintf("%+03d:%02d",
int($zone / 3600),
int($zone
, $_
];
}
使用收集的时间,将其渲染为SQL语句:
print "DROP TABLE IF EXISTS dates;\n",
"CREATE TABLE dates (date DATETIME, offset CHAR(6));\n",
"INSERT INTO dates (date,offset) VALUES\n",
join(",\n\n" =>
map(" -- $_->[2]\n" .
" ('$_->[0]','$_->[1]')", @dates)),
";\n",
"SELECT CONVERT_TZ(date,'+00:00',offset) FROM dates;\n"
输出结果为:
DROP TABLE IF EXISTS dates;
CREATE TABLE dates (date DATETIME, offset CHAR(6));
INSERT INTO dates (date,offset) VALUES
('2010-03-06 02:00:00','+07:00'),
('2010-02-19 10:30:00','+09:00'),
('2010-02-20 03:30:00','-08:00');
SELECT CONVERT_TZ(date,'+00:00',offset) FROM dates;
我们可以将其导入到mysql
中:
$ ./prog.pl | mysql -u 用户名 -D 数据库名
CONVERT_TZ(date,'+00:00',offset)
2010-03-06 09:00:00
2010-02-19 19:30:00
2010-02-19 19:30:00