CODEWITHSUNDEEP
postgresqlpsql
Aru S
Aru S

Reputation: 1529

Postgres timestamp field not showing microseconds component when value is all zeros

Context: I'm using Postgres 9.3 and psql (PostgreSQL) 11.1.

Given the following table:

create table ts_test(ts timestamp);

The following insert commands display time with microsecond granularity:

INSERT INTO ts_test
VALUES
    (now()),
    (TIMESTAMP '2019-03-06 20:18:41.000001');

select * from ts_test;
             ts
----------------------------
 2019-03-06 20:40:35.062547
 2019-03-06 20:18:41.000001

However, when a timestamp with all zeroes as the microsecond component is inserted, I observe the following behavior:

INSERT INTO ts_test VALUES (TIMESTAMP '2019-03-06 20:18:41.000000');

select * from ts_test;
             ts
----------------------------
 2019-03-06 20:40:35.062547
 2019-03-06 20:18:41.000001
 2019-03-06 20:18:41

I've pored over the documentation, but am drawing a total blank as to why the microseconds are omitted from the third entry.

Upvotes: 4

Views: 7128

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659247

TIMESTAMP '2019-03-06 20:18:41.000000' and TIMESTAMP '2019-03-06 20:18:41' are identical values. All-zero fractional digits, that's just insignificant noise which is not displayed by default by psql.

If you want to preserve original literals including all noise, insignificant zeros and white space, you'll have to store it in a string type like text.

If you want to display timestamps with microseconds you might use to_char() with the template pattern US:

select to_char(ts, 'YYYY-MM-DD HH24:MI:SS:US') AS ts1 from ts_test;
             ts1
----------------------------
 2019-03-06 20:40:35.062547
 2019-03-06 20:18:41.000001
 2019-03-06 20:18:41.000000  -- !

This result is text, of course. (Use the pattern MS for milliseconds.)

Upvotes: 7

Related Questions