Default timestamp format and fractional seconds
I'm trying to format the timestamps in my Postgres database to a certain format:
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?
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
The types timestamp or timestamptz optionally take a precision modifier p: timestamp(p). To round to full seconds, set the default to:
now()::timestamp(0)` or `now()::timestamptz(0)
That's a bit cheaper and shorter than calling date_trunc() - which truncates fractional seconds (may be what you really want!)
Store timestamps as timestamptz (or timestamp), not using a character type.
Finally, to make sure that ...
newly added entries also have the format: YYYY-MM-DD HH24:MI:SS
you could define your column as type timestamptz(0). This will not only cover the default, but all values entered to that column.
This related answer for in-depth information on timestamps and time zone handling: