Thread: Timestamp output
Hi, I've just upgraded to 7.2, and found a big difference in timestamp output. I'm using the default ISO datestyle, and newly inserted timestamp output looks like that: 2002-02-25 16:50:49.289828+01 What is the ".289828" which is breaking all my php scripts? Can I easily revert to the previous format? Thank you -- Matteo Beccati C.R.P. Software S.r.l. http://www.crpsoftware.it/
Sigh - so many people not reading release notes... But then again, I don't think this particular change was adequately documented in the 'observe the following incompatibilities' section of the HISTORY file - maybe it should be in 7.2.1? 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? Add casts everywhere?? Chris On Tue, 26 Feb 2002, Matteo Beccati wrote: > Hi, > > I've just upgraded to 7.2, and found a big difference in timestamp > output. I'm using the default ISO datestyle, and newly inserted > timestamp output looks like that: > > 2002-02-25 16:50:49.289828+01 > > What is the ".289828" which is breaking all my php scripts? > > Can I easily revert to the previous format? > > Thank you > -- > Matteo Beccati > C.R.P. Software S.r.l. > http://www.crpsoftware.it/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
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.
regards, tom lane
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.
> 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 | Hmmm...it seems to me that an easier way is to edit the pg_dump from the previous version to add (0) in everywhere. Why don't we put that in the 'please be aware of these incompatibilites' section of HISTORY? Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Hmmm...it seems to me that an easier way is to edit the pg_dump from the
> previous version to add (0) in everywhere.
Well, sure, if you haven't loaded your dump yet.
Note, however, that this does *not* provide a closer approximation to
the behavior of older versions than 7.2 does by default. Older versions
do support fractional-second timestamps, though only to a couple of
digits IIRC.
The main thing that would be useful to avoid breaking client code that
can't handle fractional-second timestamps is to replace calls to
now() and current_timestamp with current_timestamp(0).
regards, tom lane
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 > >