postgresqldatetimeformattimestampdefault

Default timestamp format and fractional seconds

I'm trying to format the timestamps in my Postgres database to a certain format:

YYYY-MM-DD HH24:MI:SS

By doing:

update myTable set tds = to_char(tds, 'YYYY-MM-DD HH24:MI:SS')::timestamp;

I managed to set all the previously stored tds to this format. However, any newly added entry goes back to: YYYY-MM-DD HH24:MI:SS.MS since the default is set to now().

How do I change this so that newly added entries also have the format: YYYY-MM-DD HH24:MI:SS?

Solution

  • There is no format stored in a timestamp type. You can set its default to a timestamp truncated to the second at creation time

    create table t (
    tds timestamp default date_trunc('second', now())
)

    Or alter the table

    alter table t 
alter column tds 
set default date_trunc('second', now());

insert into t values (default);
INSERT 0 1

select * from t;
         tds         
---------------------
 2014-03-11 19:24:11

    If you just don't want to show the milliseconds part format the output

    select to_char(now(), 'YYYY-MM-DD HH24:MI:SS');
       to_char       
---------------------
 2014-03-11 19:39:40