Default timestamp format and fractional seconds

Question

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?

I see that you want to truncat milliseconds part ? you can do it by using a trigger. — Houari, Mar 11 '14 at 19:13
Where is your table definition? We'd need to see it to be sure. `\d myTable` in psql. — Erwin Brandstetter, Mar 12 '14 at 00:28

Clodoaldo Neto · Accepted Answer · 2014-03-11T19:35:45.733

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

Erwin Brandstetter · Answer 2 · 2022-12-20T00:38:09.153

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)

Standard SQL functions CURRENT_TIMESTAMP (returns timestamptz) or LOCALTIMESTAMP (returns timestamp) allow the same precision modifier:

CURRENT_TIMESTAMP(0)
LOCALTIMESTAMP(0)

That's a bit shorter than calling date_trunc() - which truncates fractional seconds (may be what you really want!)

date_trunc('second', now())

Store timestamps as timestamptz (or timestamp), not as 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 covers all values entered into that column, not just the default. But the rounding may introduce timestamps up to half a second in the future. If that can be an issue in any way, rather use date_trunc().

See @Clodoaldo's answer for instructions on to_char() and how to ALTER TABLE.

This related answer for in-depth information on timestamps and time zone handling:

Ignoring time zones altogether in Rails and PostgreSQL

Default timestamp format and fractional seconds

2 Answers2