postgreSql——时区问题,PostgreSQL的时间/日期函数使用

timestamptz、timestamp

SELECT ts AT TIME ZONE 'UTC'
FROM (
VALUES
(timestamptz '2012-03-05 17:00:00+0')
, (timestamptz '2012-03-05 18:00:00+1')
, (timestamp '2012-03-05 18:00:00+1') -- ① loaded footgun!
, (timestamp '2012-03-05 11:00:00' AT TIME ZONE '+6')
, (timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC')
, (timestamp '2012-03-05 07:00:00' AT TIME ZONE 'US/Hawaii') -- ②
, (timestamp '2012-03-05 07:00:00' AT TIME ZONE 'HST') -- ②
) t(ts);

SELECT timestamptz '2012-03-05 20:00+03',
timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC',
timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'

查看现在距1970-01-01 00:00:00 UTC 的秒数

select extract(epoch from now());

select timestamp without time zone 'epoch',timestamp without time zone 'epoch' + 3600*interval '1 sec'

select timestamp without time zone 'epoch',timestamp with time zone 'epoch'

把epoch 值转换回时间戳

select timestamp without time zone 'epoch',timestamp with time zone 'epoch' ,timestamp 'epoch',timestamptz 'epoch'

PostgreSQL的时间/日期函数使用

postgreSQL格式化时间的函数详解

mysql转为postgesql

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(UTC_TIMESTAMP()) + tz.gmt_offset, '%a, %d %b %Y, %H:%i:%s') AS local_time
FROM "Dict"."TimeZoneDetail" tz JOIN "Dict"."TimeZoneToCountry" 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;
SELECT (now() AT TIME ZONE 'UTC' )+interval '1'* tz."GMTOffset" AS local_time,now() as now,now() AT TIME ZONE 'UTC' as utcnow,tz."GMTOffset" as offsett
FROM "Dict"."TimeZoneDetail" tz inner JOIN "Dict"."TimeZoneToCountry" z
ON tz."ZoneId"=z."ZoneId"
WHERE to_timestamp(tz."TimeStart") <= timestamp 'epoch' AND z."ZoneName"='America/Los_Angeles'
ORDER BY tz."TimeStart" DESC LIMIT 1;