运维丨postgresql与openGauss TIMESTAMP类型 与TIMESTAMPTZ时间戳操作

学习目的

TIMESTAMP 数据类型可以同时存储日期和时间，但它不存储时区。这意味着，当修改了数据库服务器所在的时区时，它里面存储的值不会改变。

TIMESTAMPTZ 数据类型在存储日期和时间的同时还能正确处理时区。PostgreSQL 使用 UTC 值来存储 TIMESTAMPTZ 数据。在向 TIMESTAMPTZ 字段插入值的时候，PostgreSQL 会自动将值转换成 UTC 值，并保存到表里。当从一个 TIMESTAMPTZ 字段查询数据的时候，PostgreSQL 会把存储在其中的 UTC 值转换成数据库服务器、用户或当前连接所在的时区。

数据类型

openGauss=# SELECT typname,typlen FROM pg_type WHERE typname ~ '^timestamp';
   typname   | typlen
-------------+--------
 timestamp   |      8
 timestamptz |      8
(2 rows)

openGauss=#

表定义

create table timestamp_demo(
id   int  primary key,
ts timestamp,
tstz timestamptz
)
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "timestamp_demo_pkey" for table "timestamp_demo"
CREATE TABLE

表结构

openGauss=# \d+ timestamp_demo
                              Table "public.timestamp_demo"
 Column |            Type             | Modifiers | Storage | Stats target | Description
--------+-----------------------------+-----------+---------+--------------+-------------
 id     | integer                     | not null  | plain   |              |
 ts     | timestamp without time zone |           | plain   |              |
 tstz   | timestamp with time zone    |           | plain   |              |
Indexes:
    "timestamp_demo_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

openGauss=#

测试

America/Los_Angeles

openGauss=# SHOW TIMEZONE;
   TimeZone
--------------
 Asia/Beijing
(1 row)

openGauss=#
openGauss=# SET timezone = 'America/Los_Angeles';
SET
openGauss=# INSERT INTO timestamp_demo (id,ts, tstz) VALUES(2,'2024-05-27 13:53:25-07','2024-05-27 13:53:25-07');
INSERT 0 1
openGauss=# select * from timestamp_demo;
 id |         ts          |          tstz
----+---------------------+------------------------
  2 | 2024-05-27 13:53:25 | 2024-05-27 13:53:25-07
(1 row)

Asia/Beijing

openGauss=# SET timezone ='Asia/Beijing';
SET
openGauss=# select * from timestamp_demo;
 id |         ts          |          tstz
----+---------------------+------------------------
  2 | 2024-05-27 13:53:25 | 2024-05-28 04:53:25+08
(1 row)

openGauss=#

测试时间范围

openGauss=# CREATE TABLE tab_test(id serial,app_name varchar,app_release_date timestamp with time zone default now());
NOTICE:  CREATE TABLE will create implicit sequence "tab_test_id_seq" for serial column "tab_test.id"
CREATE TABLE
openGauss=#
openGauss=# INSERT INTO tab_test VALUES(1,'app');
INSERT 0 1
openGauss=# select * from tab_test;
 id | app_name |       app_release_date
----+----------+-------------------------------
  1 | app      | 2024-05-27 14:29:39.865979+08
(1 row)

openGauss=#

修改字段

openGauss=#
openGauss=# alter table tab_test alter column app_release_date set  data type timestamp without time zone;
ALTER TABLE
openGauss=# alter table tab_test alter column app_release_date set default now();
ALTER TABLE
openGauss=#
openGauss=# insert into tab_test values(2,'app2');
INSERT 0 1
    openGauss=# select * from tab_test;
 id | app_name |      app_release_date
----+----------+----------------------------
  1 | app      | 2024-05-27 14:29:39.865979
  2 | app2     | 2024-05-27 14:31:57.902754
(2 rows)

openGauss=#

时间日期函数转换为带时区和不带时区的操作

openGauss=# select current_timestamp,clock_timestamp();
        pg_systimestamp        |        clock_timestamp
-------------------------------+-------------------------------
 2024-05-27 14:32:44.545916+08 | 2024-05-27 14:32:44.545955+08
(1 row)

openGauss=# select current_timestamp::timestamp without time zone,clock_timestamp()::timestamp without time zone;
      pg_systimestamp       |      clock_timestamp
----------------------------+----------------------------
 2024-05-27 14:32:57.360687 | 2024-05-27 14:32:57.360742
(1 row)

openGauss=#

API接口测试

package main  
  
import (  
"database/sql"  
"fmt"  
_ "gitee.com/opengauss/openGauss-connector-go-pq"  
"log"  
"time"  
)  
  
func main() {  
connStr := "host=127.0.0.1 port=5432 user=gaussdb password=openGauss@123 dbname=postgres sslmode=disable"  
db, err := sql.Open("opengauss", connStr)  
if err != nil {  
log.Fatal(err)  
}  
ddl := `CREATE TABLE if not exists test_timestampz (id serial, ts timestamp,tstz timestamptz);`  
_, err = db.Exec(ddl)  
if err != nil {  
fmt.Println(err)  
}  
_, err = db.Exec("SET timezone = 'America/Los_Angeles'")  
if err != nil {  
fmt.Println(err)  
}  
_, err = db.Exec("INSERT INTO test_timestampz (ts, tstz) VALUES('2024-05-27 13:53:25-07','2024-05-27 13:53:25-07');")  
if err != nil {  
fmt.Println(err)  
}  
_, err = db.Exec("SET timezone ='Asia/Beijing';")  
if err != nil {  
fmt.Println(err)  
}  
rows, err := db.Query("select ts,tstz from gaussdb.test_timestampz;")  
if err != nil {  
log.Fatal("xxx", err)  
}  
for rows.Next() {  
var ts time.Time  
var tstz time.Time  
if err := rows.Scan(&ts, &tstz); err != nil {  
log.Fatal(err)  
}  
fmt.Printf("%v,%v;\n", ts, tstz)  
  
}  
  
}

输出(一个时区/一个没有)

2024-05-27 13:53:25 +0000 +0000,2024-05-28 04:53:25 +0800 +0800;
2024-05-27 13:53:25 +0000 +0000,2024-05-28 04:53:25 +0800 +0800;
2024-05-27 13:53:25 +0000 +0000,2024-05-28 04:53:25 +0800 +0800;
2024-05-27 13:53:25 +0000 +0000,2024-05-28 04:53:25 +0800 +0800;
2024-05-27 13:53:25 +0000 +0000,2024-05-28 04:53:25 +0800 +0800;
2024-05-27 13:53:25 +0000 +0000,2024-05-28 04:53:25 +0800 +0800;
2024-05-27 13:53:25 +0000 +0000,2024-05-28 04:53:25 +0800 +0800;
2024-05-27 13:53:25 +0000 +0000,2024-05-28 04:53:25 +0800 +0800;