mysql - Postgres 相当于 Timezonedb Mysql 查询

Question

我正在尝试使用 Timezone DB在 MySql 中创建的 psql 中。我已经通过节点脚本手动查询并将其插入到 psql 中来移植数据。现在我需要的是以下查询的等效 psql 查询。由于 Mysql 中的某些函数在 psql 中找不到，下面的查询将无法工作。任何帮助将不胜感激。

三个表的定义分别是

DROP TABLE IF EXISTS `country`;
CREATE TABLE `country` (
    `country_code` CHAR(2) NULL,
    `country_name` VARCHAR(45) NULL,
    INDEX `idx_country_code` (`country_code`)
) COLLATE='utf8_bin' ENGINE=MyISAM;

LOAD DATA LOCAL INFILE 'country.csv' INTO TABLE `country` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';


DROP TABLE IF EXISTS `timezone`;
CREATE TABLE `timezone` (
    `zone_id` INT(10) NOT NULL,
    `abbreviation` VARCHAR(6) NOT NULL,
    `time_start` INT NOT NULL,
    `gmt_offset` INT NOT NULL,
    `dst` CHAR(1) NOT NULL,
    INDEX `idx_zone_id` (`zone_id`),
    INDEX `idx_time_start` (`time_start`)
) COLLATE='utf8_bin' ENGINE=MyISAM;

LOAD DATA LOCAL INFILE 'timezone.csv' INTO TABLE `timezone` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';


DROP TABLE IF EXISTS `zone`;
CREATE TABLE `zone` (
    `zone_id` INT(10) NOT NULL AUTO_INCREMENT,
    `country_code` CHAR(2) NOT NULL,
    `zone_name` VARCHAR(35) NOT NULL,
    PRIMARY KEY (`zone_id`),
    INDEX `idx_zone_name` (`zone_name`)
) COLLATE='utf8_bin' ENGINE=MyISAM;

LOAD DATA LOCAL INFILE 'zone.csv' INTO TABLE `zone` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

在Mysql中显示本地时区的查询:

    SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(UTC_TIMESTAMP()) + tz.gmt_offset, '%a, %d %b %Y, %H:%i:%s') AS local_time
FROM `timezone` tz JOIN `zone` z
ON tz.zone_id=z.zone_id
WHERE tz.time_start <= UNIX_TIMESTAMP(UTC_TIMESTAMP()) AND z.zone_name='America/Los_Angeles'
ORDER BY tz.time_start DESC LIMIT 1;

Psql 中的等价物是什么？

这是我目前尝试过的方法。但这是不正确的。它显示了 +5.30 的差异，这是我们的加尔各答时区。

SELECT to_timestamp(EXTRACT(EPOCH FROM current_timestamp at time zone 'UTC') + tz.gmt_offset) AS local_time
FROM tbl_timezones tz JOIN tbl_zones z
ON tz.zone_id=z.id
WHERE tz.time_start <= (EXTRACT(EPOCH FROM current_timestamp at time zone 'UTC') ) AND z.zone_name='America/Los_Angeles'
ORDER BY tz.time_start DESC LIMIT 1;

Please note the field names are not exactly the same.

我得到的结果是 "2017-12-21 10:38:29.301579+05:30" 在 Google 中检查的实际时间是

5:08 AM
Thursday, 21 December 2017 (GMT-8)
Time in Los Angeles, CA, USA

Answer 1

我已经转换为 postgres 版本:

来自 timezonedb.com 的原创

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(UTC_TIMESTAMP()) + tz.gmt_offset, '%a, %d %b %Y, %H:%i:%s') AS local_time
FROM `timezone` tz JOIN `zone` z
ON tz.zone_id=z.zone_id
WHERE tz.time_start <= UNIX_TIMESTAMP(UTC_TIMESTAMP()) AND z.zone_name='Asia/Ho_Chi_Minh'
ORDER BY tz.time_start DESC LIMIT 1;

我的 postgres 查询:

SELECT TO_CHAR(to_timestamp(EXTRACT(EPOCH FROM timezone('UTC', CURRENT_TIMESTAMP)::timestamptz) + tz.gmt_offset), 'yyyy-mm-dd HH24:MI:SS') AS local_time
FROM timezone tz JOIN zone z
ON tz.zone_id=z.zone_id
WHERE tz.time_start <= (EXTRACT(EPOCH FROM timezone('UTC', CURRENT_TIMESTAMP)::timestamptz ) ) AND z.zone_name='Asia/Ho_Chi_Minh'
ORDER BY tz.time_start DESC LIMIT 1;

引用:

[1] https://stackoverflow.com/a/33276622/2552249

[2] https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29