Remove TIMESTAMP precision from NOW () in PostgreSQL?

Question

Is there a way to remove precision from the result of the NOW () function in PostgreSQL?

"2012-08-21 10:23:34.867502" 

I am looking for the format:

 "2012-08-21 10:23:34" 

I am trying to update a column like "timestamp without timezone" with the following SQL:

 UPDATE table SET column = now(); 

Thanks!

Answer 1

 UPDATE tbl SET col = DATE_TRUNC('second', NOW()); 

See documents for DATE_TRUNC .

Answer 2

The simple answer is to distinguish it to zero accuracy.

 select now()::timestamptz(0); 

Answer 3

You can change the structure of your il table, you move the column length to 0 in pgAdmin 3 or create a table using timestamp (0) :

 CREATE TABLE public.users ( id integer serial, username character varying(255) NOT NULL, email character varying(255) NOT NULL, password character varying(255) NOT NULL, created_at timestamp(0) without time zone NOT NULL, updated_at timestamp(0) without time zone NOT NULL, CONSTRAINT users_pkey PRIMARY KEY (id) ); 

But if you do, an error will occur if you try to insert a timestamp with a millisecond.

Answer 4

Depending on your requirements, another parameter is to adjust the accuracy of the timestamp column itself - set its accuracy to 0. This can be useful when using PostgreSQL with legacy programs that do not process fractional seconds in timestamps.