Postgres truncates trailing zeros for timestamps

Question

Postgres (V11.3, 64bit, Windows) truncates trailing zeros for timestamps. So if I insert the timestamp '2019-06-12 12:37:07.880' into the table and I read it back as text postgres returns '2019-06-12 12:37:07.88'.

Table date_test:
CREATE TABLE public.date_test (
  id SERIAL,
  "timestamp" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  CONSTRAINT pkey_date_test PRIMARY KEY(id) 
)

SQL command when inserting data:

INSERT INTO date_test (timestamp) VALUES( '2019-06-12 12:37:07.880' )

SQL command to retrieve data:

SELECT dt.timestamp ::TEXT FROM date_test dt

returns '2019-06-12 12:37:07.88'

Do you consider this a bug or a feature?

My real issue is: I´m running queries from a C++ program and I have to convert the data returned from the database to appropriate data types. Since the protocol is text-based everything I read from the database is plain text. When parsing timestamps I first tokenize the string and then convert each token to integer. And because the millisecond part is truncated, the last token is "88" instead of "880", and converting "88" yields another value that converting "880" to integer.

score 2 · Answer 1 · answered Jun 27 '19 at 14:03

2

That's the default display format when using a cast to text.

If you want to see all three digits, use to_char()

SELECT to_char(dt.timestamp,'yyyy-mm-d hh24:mi:ss.ms') 
FROM date_test dt;

will return 2019-06-12 12:37:07.880

answered Jun 27 '19 at 14:03

1

Thanks for your suggestion, but unfortunately I have to modify the SQL statements. Is it possible to modify the default-formatting for timestamps? It was really nice if I was able to modify the default formatting per connection. – Guido Niewerth Jun 27 '19 at 14:58

Ole V.V. · Answer 2 · 2019-06-27T14:43:28.510

It’s a matter of presentation only.

First note that 07.88 seconds and 07.880 seconds is the same amount of time (also 7.88 and 07.880000000 for that matter).

PostgreSQL internally represents a timestamp in a way that we shouldn’t be concerned about as long as it’s an unambiguous representation. When you retrieve the timestamp, it is formatted into some string. This is where PostgreSQL apparently chooses not to print redundant trailing zeros. So it’s probably not even correct to say that it truncates anything. It just refrains from generating that 0.

I think that the nice solution would be to modify your parser in C++ to accept any number of decimals and parse them correctly with and without trailing zeroes. Another solution that should work is given in the answer by a_horse_with_no_name.

Postgres truncates trailing zeros for timestamps

2 Answers2