欢迎来到尧图网

客户服务 关于我们

您的位置：首页 > 房产 > 建筑 > PostgreSQL数据库timestamp数据类型精度进位问题

PostgreSQL数据库timestamp数据类型精度进位问题

2024/10/25 15:30:27 来源：https://blog.csdn.net/m0_37583655/article/details/141022298  浏览:  次   关键词：PostgreSQL数据库timestamp数据类型精度进位问题

PostgreSQL数据库timestamp数据类型精度进位问题

  • 1. PostgreSQL起源
  • 2. PostgreSQL特点
  • 3. PostgreSQL优缺点
  • 4. PostgreSQL数据类型
  • 5. PostgreSQL注意事项

1. PostgreSQL起源

PostgreSQL 起源于 1986 年加州大学伯克利分校开发的 POSTGRES 项目。它经过多年的发展和演进，成为了一个功能强大、开源的关系型数据库管理系统。

PostgreSQL 本身是开源的，通常不存在严格意义上的商业版和社区版之分。
官网地址：https://www.postgresql.org/
在官网上，您可以获取更详细和准确的信息，包括最新的特性、下载安装指南、文档教程等。

2. PostgreSQL特点

  1. 强大的功能：支持丰富的特性，如复杂查询、事务处理、存储过程、视图等。
  2. 开源和社区驱动：拥有活跃的开源社区，不断进行改进和更新。
  3. 可扩展性：易于扩展功能和数据类型。
  4. 标准符合性：高度符合 SQL 标准。
  5. 安全性：提供多种安全机制，包括用户认证、权限管理等。

3. PostgreSQL优缺点

优点：

  1. 稳定性高：经过长期的实践和优化，具有出色的稳定性和可靠性。
  2. 数据完整性：确保数据的一致性和完整性。
  3. 跨平台支持：可在多种操作系统上运行。
  4. 丰富的文档和支持：有详细的官方文档和活跃的社区支持。

缺点：

  1. 配置相对复杂：对于新手来说，初始配置可能具有一定的难度。
  2. 资源消耗：在处理大型数据集时，可能需要较多的系统资源。

4. PostgreSQL数据类型

包括数值类型（如整数、浮点数）、字符串类型（固定长度和可变长度）、日期和时间类型、布尔类型、数组类型、枚举类型、几何类型等。

数据类型说明精度特点案例
smallint小范围整数2 字节占用存储空间小，适用于数据范围较小的整数smallint: 100
integer（或 int）常规整数4 字节常见的整数类型，能满足大多数常规整数需求integer: 2000
bigint大范围整数8 字节用于存储非常大的整数数值bigint: 9000000000
decimal（或 numeric）用户指定精度和小数位数的精确数值取决于指定的精度和小数位数精度和小数位数可自定义，适用于对精度要求高的数值计算decimal(5, 2): 123.45
real单精度浮点数4 字节大约 6 位十进制精度存储精度要求不高的浮点数
double precision双精度浮点数8 字节大约 15 位十进制精度适用于高精度的浮点数计算
char(n)固定长度的字符字符串，不足长度用空格填充n 个字符长度固定，存储和读取效率较高char(10): 'hello '
varchar(n)可变长度的字符字符串，最大长度为 n最大 n 个字符节省存储空间，适用于长度不固定的字符串varchar(20): 'hello world'
text可变长度的字符字符串，无长度限制无限制可存储大量文本数据text: 'This is a long text.'
date日期格式为 YYYY-MM-DD只存储日期信息date: '2024-08-08'
time时间，不包含日期格式为 HH:MM:SS只存储时间信息time: '12:30:00'
timestamp日期和时间格式为 YYYY-MM-DD HH:MM:SS ，支持到微秒级别（6 位小数）同时包含日期和时间信息timestamp: '2024-08-08 12:30:00.123456'
boolean布尔值真或假用于表示逻辑值boolean: true
array数组取决于元素类型和数组长度可以存储相同数据类型的元素集合integer[]: '{1, 2, 3}'
jsonJSON 数据无固定精度方便存储和处理 JSON 格式的数据json: '{"key": "value"}'
uuid通用唯一标识符128 位用于生成唯一标识符uuid: 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'

5. PostgreSQL注意事项

timestamp数据类型精度进位问题，先说结论，在测试验证。
PostgreSQL只能存储最多6位的小数部分。因此，当尝试存储超过6位的分数秒时，会发生以下情况之一：
如果额外的数字导致整个分数秒部分超过 999999 微秒，则整个时间值会进位到下一秒，剩余的部分会被截断或者四舍五入到 6 位小数。
在这种情况下，2024-08-07 16:19:23.999999999 会被处理为 2024-08-07 16:19:24.000000。
如果你使用的是某种客户端或者编程语言接口来处理这个时间值，在插入之前它可能会自动进行四舍五入或截断。

比如，
截取前三位数据：小于 2024-08-07 16:19:23.999999500
进位数据：[2024-08-07 16:19:23.999999500 - 2024-08-07 16:19:23.999999999]

注意问题：
PostgreSQL 的 timestamp 类型默认只支持到微秒级别（6位小数），为什么select出来的数据中展示三位2024-08-07 16:19:23.999？
PostgreSQL 的 timestamp 类型确实支持到微秒级别（6位小数）。然而，默认情况下，当您从数据库中查询 timestamp 值时，PostgreSQL 会将结果显示为毫秒级别（3位小数）以提高可读性。这是默认的行为，但是您可以更改输出的精度。案例如下。

--PostgreSQL数据库timestamp数据类型精度进位问题
--# 1.PostgreSQL数据库数据类型
-- 数据库版本，PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
SELECT version();-- 创建数据库
-- crm.t_wx_user_1 definition-- Drop table-- DROP TABLE crm.t_wx_user_1;CREATE TABLE crm.t_wx_user_1 (user_id int8 NOT NULL,union_id varchar(50) NULL,open_id varchar(50) NULL,last_login_time timestamp NULL,privacy_agreement_version int8 NOT NULL DEFAULT 0,create_time timestamp NOT NULL,update_time timestamp NOT NULL DEFAULT '1970-01-01 00:00:00'::timestamp without time zone,delete_flag int2 NOT NULL DEFAULT 0,delete_time timestamp NOT NULL DEFAULT '1970-01-01 00:00:00'::timestamp without time zone,first_login_time timestamp NULL,created_by int8 NOT NULL DEFAULT '-1'::integer,updated_by int8 NOT NULL DEFAULT '-1'::integer,inst_id int8 NULL,CONSTRAINT t_wx_user_pkey_1 PRIMARY KEY (user_id)
);
CREATE INDEX idx_t_wx_user_open_id_1 ON crm.t_wx_user_1 USING btree (open_id);
CREATE INDEX idx_t_wx_user_union_id_1 ON crm.t_wx_user_1 USING btree (union_id);
CREATE UNIQUE INDEX uniq_wx_userid_unionid_openid_1 ON crm.t_wx_user_1 USING btree (user_id, union_id, open_id);-- 测试验证数据库
select * from crm.t_wx_user_1 order by user_id  ;-- postgres数据库 timestamp类型，新增操作，支持类型 2024-08-07 16:19:23.142，正常插入数据库
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230000, '1', '1', '2024-08-07 16:19:23.142', 0, '2024-07-12 16:52:39.674', '2024-08-07 16:19:23.142', 0, '1970-01-01 00:00:00.000', '2024-07-12 16:52:39.674', -1, -1, 1);-- postgres数据库 timestamp类型，新增操作，6位数据类型 2024-08-07 16:19:23.123456，截断前三位插入数据库
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230001, '1', '1', '2024-08-07 16:19:23.123456', 0, '2024-07-12 16:52:39.123456', '2024-08-07 16:19:23.123456', 0, '1970-01-01 00:00:00.123456', '2024-07-12 16:52:39.123456', -1, -1, 1);-- postgres数据库 timestamp类型，新增操作，3个9验证 2024-08-07 16:19:23.999，正常插入数据库
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230002, '1', '1', '2024-08-07 16:19:23.999', 0, '2024-07-12 16:52:39.999', '2024-08-07 16:19:23.999', 0, '1970-01-01 00:00:00.999', '2024-07-12 16:52:39.999', -1, -1, 1);-- postgres数据库 timestamp类型，新增操作，4个9验证 2024-08-07 16:19:23.9999，截断前三位插入数据库
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230003, '1', '1', '2024-08-07 16:19:23.9999', 0, '2024-07-12 16:52:39.9999', '2024-08-07 16:19:23.9999', 0, '1970-01-01 00:00:00.9999', '2024-07-12 16:52:39.9999', -1, -1, 1);-- postgres数据库 timestamp类型，新增操作，6个9验证 2024-08-07 16:19:23.999999，截断前三位插入数据库
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230004, '1', '1', '2024-08-07 16:19:23.999999', 0, '2024-07-12 16:52:39.999999', '2024-08-07 16:19:23.999999', 0, '1970-01-01 00:00:00.999999', '2024-07-12 16:52:39.999999', -1, -1, 1);-- postgres数据库 timestamp类型，新增操作，9个9验证 2024-08-07 16:19:23.999999999，会进位，存到数据库为：2024-08-07 16:19:24.000
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230005, '1', '1', '2024-08-07 16:19:23.999999999', 0, '2024-07-12 16:52:39.999999999', '2024-08-07 16:19:23.999999999', 0, '1970-01-01 00:00:00.999999999', '2024-07-12 16:52:39.999999999', -1, -1, 1);-- postgres数据库 timestamp类型，查询操作，9个9验证 2024-08-07 16:19:23.999999999，会进位，把2024-08-07 16:19:24.000的也查询出来。
select * from crm.t_wx_user_1 where last_login_time <= '2024-08-07 16:19:23.999999999';-- postgres数据库 timestamp类型，新增操作，9个值验证 2024-08-07 16:19:23.123456789，截断前三位插入数据库
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230006, '1', '1', '2024-08-07 16:19:23.123456789', 0, '2024-07-12 16:52:39.123456789', '2024-08-07 16:19:23.123456789', 0, '1970-01-01 00:00:00.123456789', '2024-07-12 16:52:39.123456789', -1, -1, 1);-- postgres数据库 timestamp类型，查询操作，9个9验证 2024-08-07 16:19:23.999999999，会进位，把2024-08-07 16:19:24.000的也查询出来。
select * from crm.t_wx_user_1 where last_login_time <= '2024-08-07 16:19:23.999999999';-- postgres数据库 timestamp类型，新增操作，9个9验证 2024-08-07 16:19:23.999999998，会进位，存到数据库为：2024-08-07 16:19:24.000
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230007, '1', '1', '2024-08-07 16:19:23.999999998', 0, '2024-07-12 16:52:39.999999998', '2024-08-07 16:19:23.999999998', 0, '1970-01-01 00:00:00.999999998', '2024-07-12 16:52:39.999999998', -1, -1, 1);-- postgres数据库 timestamp类型，新增操作，9个9验证 2024-08-07 16:19:23.999999001，截断前三位插入数据库
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230008, '1', '1', '2024-08-07 16:19:23.999999001', 0, '2024-07-12 16:52:39.999999001', '2024-08-07 16:19:23.999999001', 0, '1970-01-01 00:00:00.999999001', '2024-07-12 16:52:39.999999001', -1, -1, 1);-- postgres数据库 timestamp类型，新增操作，9个9验证 2024-08-07 16:19:23.999999991，进位
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230009, '1', '1', '2024-08-07 16:19:23.999999991', 0, '2024-07-12 16:52:39.999999991', '2024-08-07 16:19:23.999999991', 0, '1970-01-01 00:00:00.999999991', '2024-07-12 16:52:39.999999991', -1, -1, 1);
-- postgres数据库 timestamp类型，新增操作，9个9验证 2024-08-07 16:19:23.999999990，进位
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230010, '1', '1', '2024-08-07 16:19:23.999999990', 0, '2024-07-12 16:52:39.999999990', '2024-08-07 16:19:23.999999990', 0, '1970-01-01 00:00:00.999999990', '2024-07-12 16:52:39.999999990', -1, -1, 1);
-- postgres数据库 timestamp类型，新增操作，9个9验证 2024-08-07 16:19:23.999999900，进位
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230011, '1', '1', '2024-08-07 16:19:23.999999900', 0, '2024-07-12 16:52:39.999999900', '2024-08-07 16:19:23.999999900', 0, '1970-01-01 00:00:00.999999900', '2024-07-12 16:52:39.999999900', -1, -1, 1);-- postgres数据库 timestamp类型，新增操作，9个9验证 2024-08-07 16:19:23.999999800，进位
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230012, '1', '1', '2024-08-07 16:19:23.999999800', 0, '2024-07-12 16:52:39.999999800', '2024-08-07 16:19:23.999999800', 0, '1970-01-01 00:00:00.999999800', '2024-07-12 16:52:39.999999800', -1, -1, 1);-- postgres数据库 timestamp类型，新增操作，9个9验证 2024-08-07 16:19:23.999999000，截取前三位
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230013, '1', '1', '2024-08-07 16:19:23.999999000', 0, '2024-07-12 16:52:39.999999000', '2024-08-07 16:19:23.999999000', 0, '1970-01-01 00:00:00.999999000', '2024-07-12 16:52:39.999999000', -1, -1, 1);
-- postgres数据库 timestamp类型，新增操作，9个9验证 2024-08-07 16:19:23.999999500，进位
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230014, '1', '1', '2024-08-07 16:19:23.999999500', 0, '2024-07-12 16:52:39.999999500', '2024-08-07 16:19:23.999999500', 0, '1970-01-01 00:00:00.999999500', '2024-07-12 16:52:39.999999000', -1, -1, 1);
-- postgres数据库 timestamp类型，新增操作，9个9验证 2024-08-07 16:19:23.999999100，进位
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230015, '1', '1', '2024-08-07 16:19:23.999999100', 0, '2024-07-12 16:52:39.999999100', '2024-08-07 16:19:23.999999100', 0, '1970-01-01 00:00:00.999999100', '2024-07-12 16:52:39.999999100', -1, -1, 1);
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230016, '1', '1', '2024-08-07 16:19:23.999999200', 0, '2024-07-12 16:52:39.999999200', '2024-08-07 16:19:23.999999100', 0, '1970-01-01 00:00:00.999999100', '2024-07-12 16:52:39.999999100', -1, -1, 1);
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230017, '1', '1', '2024-08-07 16:19:23.999999300', 0, '2024-07-12 16:52:39.999999300', '2024-08-07 16:19:23.999999100', 0, '1970-01-01 00:00:00.999999100', '2024-07-12 16:52:39.999999100', -1, -1, 1);
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230018, '1', '1', '2024-08-07 16:19:23.999999400', 0, '2024-07-12 16:52:39.999999400', '2024-08-07 16:19:23.999999100', 0, '1970-01-01 00:00:00.999999100', '2024-07-12 16:52:39.999999100', -1, -1, 1);-- 再次验证 500 进位
INSERT INTO crm.t_wx_user_1
(user_id, union_id, open_id, last_login_time, privacy_agreement_version, create_time, update_time, delete_flag, delete_time, first_login_time, created_by, updated_by, inst_id)
VALUES(1230019, '1', '1', '2024-08-07 16:19:23.999999500', 0, '2024-07-12 16:52:39.999999500', '2024-08-07 16:19:23.999999100', 0, '1970-01-01 00:00:00.999999100', '2024-07-12 16:52:39.999999100', -1, -1, 1);-- postgres数据库 timestamp类型，查询操作，9个9验证 2024-08-07 16:19:23.999999999，会进位，把2024-08-07 16:19:24.000的也查询出来。
select * from crm.t_wx_user_1 ;
where last_login_time <= '2024-08-07 16:19:23.999999999';--截取前三位数据：小于 2024-08-07 16:19:23.999999500
--进位数据：[2024-08-07 16:19:23.999999500 - 2024-08-07 16:19:23.999999999]结论
PostgreSQL只能存储最多6位的小数部分。因此，当尝试存储超过6位的分数秒时，会发生以下情况之一：
如果额外的数字导致整个分数秒部分超过 999999 微秒，则整个时间值会进位到下一秒，剩余的部分会被截断或者四舍五入到 6 位小数。
在这种情况下，2024-08-07 16:19:23.999999999 会被处理为 2024-08-07 16:19:24.000000。
如果你使用的是某种客户端或者编程语言接口来处理这个时间值，在插入之前它可能会自动进行四舍五入或截断。比如：
截取前三位数据：小于 2024-08-07 16:19:23.999999500
进位数据：[2024-08-07 16:19:23.999999500 - 2024-08-07 16:19:23.999999999]-- 创建表
CREATE TABLE crm.wx_users (id SERIAL PRIMARY KEY,name VARCHAR(255) NOT NULL,created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL
);-- 插入数据
INSERT INTO crm.wx_users (name, created_at)
VALUES ('John Doe', '2024-08-07 16:19:23.999999');
INSERT INTO crm.wx_users (name, created_at)
VALUES ('Jerry', '2024-08-07 16:19:23.999996');-- 查询数据
SELECT * FROM crm.wx_users;PostgreSQL 的 timestamp 类型默认只支持到微秒级别（6位小数），为什么select出来的数据中展示三位2024-08-07 16:19:23.999PostgreSQL 的 timestamp 类型确实支持到微秒级别（6位小数）。然而，默认情况下，当您从数据库中查询 timestamp 值时，PostgreSQL 会将结果显示为毫秒级别（3位小数）以提高可读性。这是默认的行为，但是您可以更改输出的精度。-- 使用 to_char 函数来显示完整的微秒
SELECT id, name, to_char(created_at, 'YYYY-MM-DD HH24:MI:SS.US') AS created_at FROM crm.wx_users;

版权声明:

本网仅为发布的内容提供存储空间，不对发表、转载的内容提供任何形式的保证。凡本网注明“来源：XXX网络”的作品，均转载自其它媒体，著作权归作者所有，商业转载请联系作者获得授权，非商业转载请注明出处。

我们尊重并感谢每一位作者，均已注明文章来源和作者。如因作品内容、版权或其它问题，请及时与我们联系，联系邮箱：809451989@qq.com，投稿邮箱：809451989@qq.com

相关资讯

热文排行

最新新闻

推荐新闻

热搜词

Vue3结合vue-plugin-hiprint实现自定义打印模板设计与布局 如何看待AI技术的应用前景？ 《宝可梦：阿尔宙斯》和《宝可梦：BDSP》遭黑客攻击，PC版本遭泄露 传知代码-ChatGPT多模态命名实体识别 2024_Newstar_week3_Crypto 为什么诺贝尔物理学奖颁给了 AI 大神