SoFunction
Updated on 2025-03-03

Analysis of the problem of timestamp data type precision carry problem in PostgreSQL database

PostgreSQL database timestamp data type precision carry problem

1. Introduction to PostgreSQL

PostgreSQL originated from the POSTGRES project developed by the University of California, Berkeley in 1986. After years of development and evolution, it has become a powerful and open source relational database management system.

PostgreSQL itself is open source, and there is usually no distinction between commercial and community versions in the strict sense.
Official website address：/
On the official website, you can obtain more detailed and accurate information, including the latest features, download and installation guides, documentation and tutorials, etc.

2. PostgreSQL features

1. Powerful functions: Supports rich features such as complex queries, transaction processing, stored procedures, views, etc.
2. Open source and community-driven: Have an active open source community, constantly improving and updating.
3. Scalability: Easy to expand features and data types.
4. Standard compliance: highly compliant with SQL standards.
5. Security: Provides a variety of security mechanisms, including user authentication, permission management, etc.

3. Pros and cons of PostgreSQL

advantage:
1. High stability: After long-term practice and optimization, it has excellent stability and reliability.
2. Data integrity: Ensure the consistency and integrity of the data.
3. Cross-platform support: can run on multiple operating systems.
4. Rich documentation and support: There are detailed official documentation and active community support.

shortcoming:
1. The configuration is relatively complex: For beginners, the initial configuration may be difficult.
2. Resource consumption: When processing large data sets, more system resources may be required.

4. PostgreSQL data type

Including numerical types (such as integers, floating-point numbers), string types (fixed and variable-length), date and time types, boolean types, array types, enumeration types, geometric types, etc.

Data Type illustrate Accuracy Features Case
smallint Small range integers 2 bytes Small storage space, suitable for integers with small data range smallint: 100
integer(orint） Regular integers 4 bytes Common integer types can meet most regular integer requirements integer: 2000
bigint Large range integers 8 bytes Used to store very large integer values bigint: 9000000000
decimal(ornumeric） User-specified precision and decimal places Depends on the specified precision and number of decimal places Accuracy and decimal places are customizable, suitable for numerical calculations with high accuracy requirements decimal(5, 2): 123.45
real Single precision floating point number 4 bytes Approximately 6-bit decimal precision Floating point numbers with low storage accuracy requirements
double precision Double precision floating point number 8 bytes Approximately 15-bit decimal precision Suitable for high-precision floating point calculations
char(n) Fixed-length character string, filled with spaces if insufficient length n characters Fixed length, high storage and reading efficiency char(10): 'hello '
varchar(n) Variable length character string, maximum length n Maximum n characters Save storage space, suitable for strings with unfixed length varchar(20): 'hello world'
text Variable length character string, no length limit Unlimited Can store large amounts of text data text: 'This is a long text.'
date date The format is YYYY-MM-DD Only store date information date: '2024-08-08'
time Time, not including date The format is HH:MM:SS Only store time information time: '12:30:00'
timestamp Date and time The format is YYYY-MM-DD HH:MM:SS, supported to the microsecond level (6 decimal places) Contains date and time information timestamp: '2024-08-08 12:30:00.123456'
boolean Boolean value True or fake Used to represent logical values boolean: true
array Array Depends on element type and array length A collection of elements that can store the same data type integer[]: '{1, 2, 3}'
json JSON data No fixed accuracy Conveniently store and process data in JSON format json: '{"key": "value"}'
uuid Universal unique identifier 128 bits Used to generate unique identifiers uuid: 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'

5. PostgreSQL precautions

There is a carry problem with the accuracy of the timestamp data type of PostgreSQL database. Let’s talk about the conclusion first and verify it in the test.

PostgreSQL can only store decimal parts with up to 6 digits. Therefore, when trying to store more than 6 bits of fractional seconds, one of the following happens:
If the extra number causes the entire fractional second portion to exceed 9999999 microseconds, the entire time value will be carried to the next second, and the remaining portion will be truncated or rounded to 6 decimal places.
In this case, 2024-08-07 16:19:23.99999999999999 will be processed as 2024-08-07 16:19:24.000000.
If you are using some client or programming language interface to handle this time value, it may be rounded or truncated automatically before inserting.

for example,
Intercept the first three digits of data: less than 2024-08-07 16:19:23.999999500
Carrying data: [2024-08-07 16:19:23.999999500 - 2024-08-07 16:19:23.999999999999]

By specifying the accuracy, 6 bits will still be saved in the database, but when specifying the accuracy of 3, the last three bits will always be 0, and if the accuracy of 6 is specified, 6 bits will be saved.

SELECT * FROM crm.t_wx_user_1 WHERE 1=1;
SELECT user_id, to_char(last_login_time, 'YYYY-MM-DD HH24:MI:'), to_char(create_time, 'YYYY-MM-DD HH24:MI:') FROM crm.t_wx_user_1 WHERE 1=1;
-- last_login_time timestamp，-- 》timestamp(3)
ALTER TABLE crm.t_wx_user_1 ALTER COLUMN last_login_time TYPE TIMESTAMP(3) WITHOUT TIME ZONE;
ALTER TABLE crm.t_wx_user_1 ALTER COLUMN create_time TYPE TIMESTAMP(6) WITHOUT TIME ZONE;

Note the problem:
The timestamp type of PostgreSQL only supports microsecond level (6 decimal places) by default. Why do three digits displayed in the selected data 2024-08-07 16:19:23.999?
PostgreSQL's timestamp type does support to the microsecond level (6 decimal places). However, by default, when you query the timestamp value from the database, PostgreSQL displays the results as millisecond levels (3 decimals) for greater readability. This is the default behavior, but you can change the accuracy of the output. The case is as follows.

--PostgreSQLdatabasetimestampData type precision carry problem
--# Database Data Type-- database版本，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();
-- 创建database
-- 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);
-- 测试verifydatabase
select * from crm.t_wx_user_1 order by user_id  ;
-- postgresdatabase timestamptype，New operations，支持type 2024-08-07 16:19:23.142，正常插入database
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);
-- postgresdatabase timestamptype，New operations，6位数据type 2024-08-07 16:19:23.123456，截断前三位插入database
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);
-- postgresdatabase timestamptype，New operations，3indivual9verify 2024-08-07 16:19:23.999，正常插入database
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);
-- postgresdatabase timestamptype，New operations，4indivual9verify 2024-08-07 16:19:23.9999，截断前三位插入database
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);
-- postgresdatabase timestamptype，New operations，6indivual9verify 2024-08-07 16:19:23.999999，截断前三位插入database
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);
-- postgresdatabase timestamptype，New operations，9indivual9verify 2024-08-07 16:19:23.999999999，Will carry，存到database为：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);
-- postgresdatabase timestamptype，Query operation，9indivual9verify 2024-08-07 16:19:23.999999999，Will carry，Bundle2024-08-07 16:19:24.000Query it。
select * from crm.t_wx_user_1 where last_login_time <= '2024-08-07 16:19:23.999999999';
-- postgresdatabase timestamptype，New operations，9indivual值verify 2024-08-07 16:19:23.123456789，截断前三位插入database
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);
-- postgresdatabase timestamptype，Query operation，9indivual9verify 2024-08-07 16:19:23.999999999，Will carry，Bundle2024-08-07 16:19:24.000Query it。
select * from crm.t_wx_user_1 where last_login_time <= '2024-08-07 16:19:23.999999999';
-- postgresdatabase timestamptype，New operations，9indivual9verify 2024-08-07 16:19:23.999999998，Will carry，存到database为：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);
-- postgresdatabase timestamptype，New operations，9indivual9verify 2024-08-07 16:19:23.999999001，截断前三位插入database
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);
-- postgresdatabase timestamptype，New operations，9indivual9verify 2024-08-07 16:19:23.999999991，Carry out
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);
-- postgresdatabase timestamptype，New operations，9indivual9verify 2024-08-07 16:19:23.999999990，Carry out
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);
-- postgresdatabase timestamptype，New operations，9indivual9verify 2024-08-07 16:19:23.999999900，Carry out
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);
-- postgresdatabase timestamptype，New operations，9indivual9verify 2024-08-07 16:19:23.999999800，Carry out
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);
-- postgresdatabase timestamptype，New operations，9indivual9verify 2024-08-07 16:19:23.999999000，Intercept the top three
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);
-- postgresdatabase timestamptype，New operations，9indivual9verify 2024-08-07 16:19:23.999999500，Carry out
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);
-- postgresdatabase timestamptype，New operations，9indivual9verify 2024-08-07 16:19:23.999999100，Carry out
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);
-- 再次verify 500 Carry out
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);
-- postgresdatabase timestamptype，Query operation，9indivual9verify 2024-08-07 16:19:23.999999999，Will carry，Bundle2024-08-07 16:19:24.000Query it。
select * from crm.t_wx_user_1 ;
where last_login_time <= '2024-08-07 16:19:23.999999999';
--Intercept the top three数据：Less than 2024-08-07 16:19:23.999999500
--Carry out数据：[2024-08-07 16:19:23.999999500 - 2024-08-07 16:19:23.999999999]
in conclusion
PostgreSQLOnly store the most6The fractional part of the bit。therefore，When trying to store more than6Fraction of bits in seconds，One of the following will occur：
如果额外of数字导致整indivual分数秒部分超过 999999 Microseconds，则整indivual时间值Will carry到下一秒，The remaining part will be truncated or rounded to 6 Decimal places。
in this case，2024-08-07 16:19:23.999999999 Will be processed as 2024-08-07 16:19:24.000000。
如果你useof是某种客户端或者编程语言接口来处理这indivual时间值，It may be rounded or truncated automatically before inserting。
for example：
Intercept the top three数据：Less than 2024-08-07 16:19:23.999999500
Carry out数据：[2024-08-07 16:19:23.999999500 - 2024-08-07 16:19:23.999999999]
-- Create a table
CREATE TABLE crm.wx_users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL
);
-- Insert data
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');
-- Query data
SELECT * FROM crm.wx_users;
 PostgreSQL of timestamp type默认只支持到Microseconds级别（6Decimal places），Whyselect出来of数据中展示三位2024-08-07 16:19:23.999
 PostgreSQL of timestamp type确实支持到Microseconds级别（6Decimal places）。However，By default，当您从database中查询 timestamp When value，PostgreSQL The result will be displayed as milliseconds level（3Decimal places）To improve readability。这是默认of行为，但是您可以更改输出of精度。
-- use to_char 函数来显示完整ofMicroseconds
SELECT id, name, to_char(created_at, 'YYYY-MM-DD HH24:MI:') AS created_at FROM crm.wx_users;

This is the article about the accuracy carry problem of PostgreSQL database timestamp data type accuracy. For more related PostgreSQL timestamp data type content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!