aki[aki]#* show timezone;
+-------------+
| TimeZone |
+-------------+
| Asia/Taipei |
+-------------+
(1 row)
select '2020-03-07 00:00:00'::timestamptz + '2 days';
+------------------------+
| ?column? |
+------------------------+
| 2020-03-09 00:00:00+08 |
+------------------------+
select '2020-03-07 00:00:00'::timestamptz + '48 hours';
+------------------------+
| ?column? |
+------------------------+
| 2020-03-09 00:00:00+08 |
+------------------------+
set timezone='America/New_York';
select '2020-03-07 00:00:00'::timestamptz + '2 days';
+------------------------+
| ?column? |
+------------------------+
| 2020-03-09 00:00:00-04 |
+------------------------+
select '2020-03-07 00:00:00'::timestamptz + '48 hours';
+------------------------+
| ?column? |
+------------------------+
| 2020-03-09 01:00:00-04 |
+------------------------+
-- Daylight Saving Time
https://www.timeanddate.com/time/change/usa
https://udn.com/news/story/6813/4399368
select '2020-03-07 22:00:00'::timestamptz + interval '1 hour' * n as "by hour"
, '2020-03-08 01:58:00'::timestamptz + interval '1 minute' * n as "by minute"
, '2020-03-08 01:59:58'::timestamptz + interval '1 second' * n as "by second"
from generate_series(1, 5) as n;
+------------------------+------------------------+------------------------+
| by hour | by minute | by second |
+------------------------+------------------------+------------------------+
| 2020-03-07 23:00:00-05 | 2020-03-08 01:59:00-05 | 2020-03-08 01:59:59-05 |
| 2020-03-08 00:00:00-05 | 2020-03-08 03:00:00-04 | 2020-03-08 03:00:00-04 |
| 2020-03-08 01:00:00-05 | 2020-03-08 03:01:00-04 | 2020-03-08 03:00:01-04 |
| 2020-03-08 03:00:00-04 | 2020-03-08 03:02:00-04 | 2020-03-08 03:00:02-04 |
| 2020-03-08 04:00:00-04 | 2020-03-08 03:03:00-04 | 2020-03-08 03:00:03-04 |
+------------------------+------------------------+------------------------+
(5 rows)
可以觀察到微妙的變化,特別注意到時區與UTC的差距 -05 變成 -04 了.