Postgresql TIMESTAMP無法更新正確時間

postgresql timestamp update insert

40527145 2019-08-22 14:53:24 ‧ 7183 瀏覽

分享至

小弟初學POSTGRESQL在建立資料表格中
建立一格TIMESTAMP WITHOUT TIME ZONE 並為了調整至台灣時間，增加了8小時，如圖

create or replace function upd_timestamp() returns trigger as
$$
begin
  new.modified = current_timestamp;
  return new;
end
$$
language plpgsql;
 
drop table if exists "topic"."score";
create table "topic"."score" (
  id   bigserial primary key,
  "fixno" char(5) COLLATE "pg_catalog"."default" ,
  "itemno" char(3) COLLATE "pg_catalog"."default",
  "score" varchar(20) COLLATE "pg_catalog"."default",
  "modified" timestamp default current_timestamp + interval '8 hours'
);
create trigger t_name before update on "topic"."score" for each row execute procedure upd_timestamp();

但在我UPDATE ID"1"資料後，時間卻沒有增加8小時，而INSERT ID"121"，時間卻是有增加的，請問高手們程式該如何修改?

UPDATE "topic"."score" SET score='80' WHERE id='1';

INSERT INTO "topic"."score" VALUES (121, '19001', 'c06', 42);

請問高手們程式該如何修改?

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

一級屠豬士

iT邦新手 2 級 ‧ 2019-08-22 23:30:22

最佳解答

-- 查看時區設定

SELECT current_setting('TIMEZONE');
+-----------------+
| current_setting |
+-----------------+
| Asia/Taipei     |
+-----------------+

-- 一個是 timestamp without timezone, 一個是 timestamp with timezone
-- 後面的+8 代表現在時區設定與UTC +8小時
select localtimestamp, current_timestamp;
+----------------------------+-------------------------------+
|       localtimestamp       |       current_timestamp       |
+----------------------------+-------------------------------+
| 2019-08-22 23:19:36.342856 | 2019-08-22 23:19:36.342856+08 |
+----------------------------+-------------------------------+

-- PostgreSQL 只要設定好Server所在的時區,就可以了喔
-- 不需要搞什麼設成UTC,再加8小時.
-- 你上面的 trigger 就是不加8小時啊. default 是自己加8小時.
-- 兩邊不一致.PostgreSQL 是依照你給的規則做的.
-- 只是你以沒有時區觀念的資料庫,來去做自行調整,又不一致.
-- 而PostgreSQL 與 Oracle 都是有時區觀念的,完全不需要如此麻煩.
-- 只要確定所在時區正確,就會自動產生正確的時間.

-- 甚至還可以用 extract() 來取出 timezone , 相對於 UTC的差距秒數.
-- 當然裡面的時間型態是要 with timezone的
select extract(timezone from current_timestamp) / 3600
     , extract(timezone from now()) / 3600;

+----------+----------+
| ?column? | ?column? |
+----------+----------+
|        8 |        8 |
+----------+----------+

--  離UTC 8小時.

回應 2
分享
檢舉

40527145 iT邦新手 5 級 ‧ 2019-09-06 14:50:02 檢舉

感謝大大的回答我成功完成了
但顯示的時間會是
2019-09-06 14:40:58.825583
如何去掉小數點
我只想要顯示
2019-09-06 14:40:58

目前的程式

create or replace function upd_timestamp() returns trigger as
$$
begin
  new.modified = current_timestamp ;
  set timezone to 'Asia/Taipei';
  return new;
end
$$
language plpgsql;
 
drop table if exists "public"."score";
CREATE SEQUENCE "public"."score_serno_seq" START 200;
create table "public"."score" (
  "serno" int4 NOT NULL DEFAULT nextval('score_serno_seq'::regclass),
  "length" varchar(20) COLLATE "pg_catalog"."default" ,
  "width" varchar(20) COLLATE "pg_catalog"."default",
  "area" varchar(20) COLLATE "pg_catalog"."default",
  "Lcircle" varchar(20) COLLATE "pg_catalog"."default",
  "Scircle" varchar(20) COLLATE "pg_catalog"."default",
  "Cdistance" varchar(20) COLLATE "pg_catalog"."default",
  "modified" timestamp default current_timestamp);
set timezone to 'Asia/Taipei';
create trigger t_name before update on "public"."score" for each row execute procedure upd_timestamp();

一級屠豬士 iT邦大師 1 級 ‧ 2019-10-02 22:24:22 檢舉

select clock_timestamp(), clock_timestamp()::timestamp(0);
+-------------------------------+---------------------+
|        clock_timestamp        |   clock_timestamp   |
+-------------------------------+---------------------+
| 2019-10-02 22:23:16.530898+08 | 2019-10-02 22:23:17 |
+-------------------------------+---------------------+
可以利用 ::timestamp(0) 來指定,秒數後精度.

登入發表回應