Re: Timestamp output

On Tuesday 26 February 2002 16:55, Tom Lane wrote:
> Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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.

It would be nice if the JDBC driver reflected the changes as it chokes on:
Bad Timestamp Format at 23 in 2002-02-26 18:32:54.83294+01

--
Andreas Joseph Krogh (Senior Software Developer) <andreak(at)officenet(dot)no>
A hen is an egg's way of making another egg.