iT邦幫忙

2

以Postgresql為主,再聊聊資料庫 有趣的日光節約時間

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



尚未有邦友留言

立即登入留言