Remove TIMESTAMP precision from NOW() result in PostgreSQL?

Question

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

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

I'm looking for the format to be:

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

I'm attempting to update a column of type "timestamp without time zone" with the following SQL:

UPDATE table SET column = now();

Thanks!

Answer 1

Simple answer is to cast it to zero precision.

select now()::timestamptz(0);

Answer 2

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

See the docs for DATE_TRUNC.

Answer 3

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

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 that an error will occur if you try to insert a timestamp with millisecond.

Answer 4

Depending on your requirements, another option is to adjust the precision of the timestamp column itself - setting its precision to 0. This can be useful when using PostgreSQL with legacy programs that don't handle fractional seconds in timestamps.

Answer 5

select now()::timestamp(0);

in previous answer select now()::timestamptz(0); still keeps the timezone.