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?

Answers


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)

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 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.

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:


Need Your Help

Three-way-merge - different philosophies?

user-interface version-control merge three-way-merge

I've been a user of UltraCompare Pro since it first came out, and I think it's a very full-featured compare and merge tool. However, since I have been looking more closely into DVCS, I found that it

no border around DIV

jquery css html

I want to be able to fade one image into another on hover and then fade out the hovered image once the mouse leaves the object. Because its tightly packed within a DIV i do not want any space aroun...