Re: Timestamp output - Mailing list pgsql-sql

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Although I know of this problem, I would also be interested in the fix.
> I know that you can declare a column of type timestamp(0) to get the old
> format, but how do you change an existing column?

Officially, it's not supported.  Unofficially, you can always hack
pg_attribute.atttypmod, which is where precision info is stored.
Observe the following example:

regression=# create table foo (f1 timestamp, f2 timestamp(0));
CREATE
regression=# \d foo                  Table "foo"Column |            Type             | Modifiers
--------+-----------------------------+-----------f1     | timestamp with time zone    |f2     | timestamp(0) with time
zone|
 

regression=# select * from pg_attribute where attrelid =
regression-# (select oid from pg_class where relname = 'foo')
regression-# and attnum > 0;attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff |
atttypmod| attbyval | attstorage | attisset | attalign | attnotnull | atthasdef
 

----------+---------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+----------+------------+-----------
146285 | f1      |     1184 |            10 |      8 |      1 |        0 |          -1 |        -1 | f        | p
  | f        | d        | f          | f  146285 | f2      |     1184 |            10 |      8 |      2 |        0 |
     -1 |         0 | f        | p          | f        | d        | f          | f
 
(2 rows)

Comparing the atttypmod values, we see that -1 implies "no precision
restriction" and 0 means "zero fractional digits" (note that this
applies to timestamp only, other datatypes have their own conventions).
Now that we know where the gold is hidden:

regression=# update pg_attribute set atttypmod = 0 where
regression-# attrelid = (select oid from pg_class where relname = 'foo')
regression-# and attnum = 1;
UPDATE 1
regression=# \d foo                  Table "foo"Column |            Type             | Modifiers
--------+-----------------------------+-----------f1     | timestamp(0) with time zone |f2     | timestamp(0) with time
zone|
 


This does not change the data already in the column, only cause a
rounding adjustment to be applied during future inserts and updates.

If you've already got fractional timestamps in the table, you could
now fix 'em all with something like
update foo set f1 = f1;

BTW: if what you're unhappy about is not a readout from a table but
just the result of "select now()", try "select current_timestamp(0)"
instead.
        regards, tom lane