Skip site navigation (1) Skip section navigation (2)

Re: Timestamp output

From: Barry Lind <barry(at)xythos(dot)com>
To: Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Timestamp output
Date: 2002-02-05 10:59:24
Message-ID: 3C5FBB0C.9060304@xythos.com (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-sql
Andreas,

Are you using the latest 7.2 driver?  I don't have any problems with 
this using the latst 7.2 driver.  If you are using the latest driver, 
can you send in a test case that reproduces this problem?

thanks,
--Barry


Andreas Joseph Krogh wrote:
> 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
> 
> 



In response to

Responses

pgsql-sql by date

Next:From: Luis SousaDate: 2002-02-05 11:26:59
Subject: RULES - Difference between DO and DO INSTEAD
Previous:From: RudiDate: 2002-02-05 03:28:12
Subject: Database ownership

pgsql-jdbc by date

Next:From: Barry LindDate: 2002-02-05 11:02:34
Subject: Re: Blob support...
Previous:From: David HookerDate: 2002-02-04 20:17:50
Subject: Arrays returned in a result set

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group