postgres14 trims digits in timestamp

linux_beginner 0
Viewed 31

Postgres 14. I have following table:

CREATE TABLE IF NOT EXISTS F_WALLET
(
    id                      serial PRIMARY KEY,
    // columns
    // more columns
    inserttime              timestamp
);

I have python code that does insert, example:

INSERT INTO F_WALLET
    (
    // some more columns
    inserttime
    )
    VALUES (
          11.05,      137.66,        2.20,
         137.66,        0.00,       21.81,
           0.00,       21.81,      115.85,
         137.66,        0.00,      115.85,
           true, '2022-09-15 2022-09-15 20:21:57.768330+02:00' 
    )

But if microseconds in inserttime (last column) end with zeros it gets trimmed.. Now (using pgadmin/console/any other tool) when I do SELECT id, inserttime FROM F_WALLET I have:

 897  | 2022-09-15 20:22:05.400541 
 896  | 2022-09-15 20:22:03.477851 
 895  | 2022-09-15 20:22:01.9936   
 894  | 2022-09-15 20:22:00.591512 
 893  | 2022-09-15 20:21:57.76833  
 892  | 2022-09-15 20:21:54.860594

How do I force my table to put zeros to get exactly n-digits ? If not possible then how do I force my select to add zeroes to have exactly n-digits in microseconds ?

postgresql
1 Answers

Timestamps have no format. Queries present them as needed. You may use to_char to explicitly format the timestamp like this:

SELECT id, 
    to_char(inserttime, 'yyyy-mm-dd hh24:mi:ss.us') as inserttime
FROM F_WALLET;

or format and extract in more complex cases.

Stefanov.sm0
Related