Re: Timestamp output
От | Barry Lind |
---|---|
Тема | Re: Timestamp output |
Дата | |
Msg-id | 3C5FBB0C.9060304@xythos.com обсуждение исходный текст |
Ответ на | Re: Timestamp output (Christopher Kings-Lynne <chriskl@familyhealth.com.au>) |
Список | pgsql-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@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 > >
В списке pgsql-sql по дате отправления: