Re: Timestamp output - Mailing list pgsql-sql

On Tuesday 26 February 2002 16:55, Tom Lane wrote:
> 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.

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@officenet.no>
A hen is an egg's way of making another egg.