Remove millisecond from timestamp column in Postgres while inserting

Question

Can any body tell how to remove or skip the millisecond part while inserting the current time to the timestamp column in Postgres database table. Actually the column that holds the data type timestamp without timezone and default is set to now() i.e. it will take the current date and time.

It inserts right date and time but it puts the millisecond part for example the format that gets entered is "2015-01-07 10:27:44.234534" which has millisecond part i.e 234534 which I don't want to be inserted. I only want the the data that is to be inserted is as "2015-01-07 10:27:44". Can any body let me know without changing the data type of that column how to insert the current date and time skipping the millisecond part.

[This SO question](http://stackoverflow.com/questions/10213190/discard-millisecond-part-from-timestamp) might help. — J0ANMM, Feb 02 '17 at 09:16

score 8 · Answer 1 · answered Jan 07 '15 at 09:07

Change your default to strip off the milliseconds:

create table foo 
(
  id integer not null primary key,
  created_at timestamp default date_trunc('second', current_timestamp)
);

Or to change the default for an existing table:

alter table foo 
   alter column created_at set default date_trunc('second', current_timestamp);

To change the existing values, you can use:

update foo 
  set created_at = date_trunc('second', created_at)
where created_at is not null;

Instead of using date_trunc you can also just cast the value: created_at::timestamp(0) for the update statement, or current_timestamp::timestamp(0) in the default value.

Ok I got it but I am not able get the value after point is of second part or milisecond part. — bikashphp, Jan 08 '15 at 10:06

score 2 · Answer 2 · edited Apr 14 '21 at 22:38

As alternative, if you could modify the data type of that column, you could insert by default the CURRENT_TIME:

    CREATE TABLE my_table (
                    col1 TEXT,
                    col2 INTEGER,
                    ts TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP
                    );

The 0 between parentheses is saying that you don't want any milliseconds. See the documentation for further details.

Remove millisecond from timestamp column in Postgres while inserting

2 Answers2