pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Barry Lind <barry@xythos.com>
To: Andreas Joseph Krogh <andreak@officenet.no>
Cc: pgsql-sql@postgresql.org
Subject: Re: Timestamp output
Date: Tue, 05 Feb 2002 02:59:24 -0800
Message-ID: <3C5FBB0C.9060304@xythos.com> (raw)
References: <20020226223623.L56253-100000@houston.familyhealth.com.au>
	<29989.1014738941@sss.pgh.pa.us>
	<20020226175601.0944123FA1@dell-laptop01.officenet.no>

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





view thread (9+ messages)  latest in thread

Message-ID: <3C5FBB0C.9060304@xythos.com>
Permalink:  ../3C5FBB0C.9060304@xythos.com/
Also on:    postgresql.org/message-id/3C5FBB0C.9060304@xythos.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: barry@xythos.com, andreak@officenet.no
  Subject: Re: Timestamp output
  In-Reply-To: <3C5FBB0C.9060304@xythos.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox