pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Timestamp output
9+ messages / 6 participants
[nested] [flat]
* Timestamp output
@ 2002-02-26 09:36 Matteo Beccati <m.beccati@crpsoftware.it>
2002-02-26 14:39 ` Re: Timestamp output Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 9+ messages in thread
From: Matteo Beccati @ 2002-02-26 09:36 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Timestamp output
2002-02-26 09:36 Timestamp output Matteo Beccati <m.beccati@crpsoftware.it>
@ 2002-02-26 14:39 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-26 15:55 ` Re: Timestamp output Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Christopher Kings-Lynne @ 2002-02-26 14:39 UTC (permalink / raw)
To: Matteo Beccati <m.beccati@crpsoftware.it>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Timestamp output
2002-02-26 09:36 Timestamp output Matteo Beccati <m.beccati@crpsoftware.it>
2002-02-26 14:39 ` Re: Timestamp output Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-02-26 15:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-26 17:56 ` Re: Timestamp output Andreas Joseph Krogh <andreak@officenet.no>
2002-02-27 03:17 ` Re: Timestamp output Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 2 replies; 9+ messages in thread
From: Tom Lane @ 2002-02-26 15:55 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Matteo Beccati <m.beccati@crpsoftware.it>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Timestamp output
2002-02-26 09:36 Timestamp output Matteo Beccati <m.beccati@crpsoftware.it>
2002-02-26 14:39 ` Re: Timestamp output Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-26 15:55 ` Re: Timestamp output Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-02-26 17:56 ` Andreas Joseph Krogh <andreak@officenet.no>
2002-02-05 10:59 ` Re: Timestamp output Barry Lind <barry@xythos.com>
1 sibling, 1 reply; 9+ messages in thread
From: Andreas Joseph Krogh @ 2002-02-26 17:56 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Timestamp output
2002-02-26 09:36 Timestamp output Matteo Beccati <m.beccati@crpsoftware.it>
2002-02-26 14:39 ` Re: Timestamp output Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-26 15:55 ` Re: Timestamp output Tom Lane <tgl@sss.pgh.pa.us>
2002-02-26 17:56 ` Re: Timestamp output Andreas Joseph Krogh <andreak@officenet.no>
@ 2002-02-05 10:59 ` Barry Lind <barry@xythos.com>
2002-03-01 02:32 ` Re: [SQL] Timestamp output Barry Lind <barry@xythos.com>
0 siblings, 1 reply; 9+ messages in thread
From: Barry Lind @ 2002-02-05 10:59 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: 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
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Timestamp output
2002-02-26 09:36 Timestamp output Matteo Beccati <m.beccati@crpsoftware.it>
2002-02-26 14:39 ` Re: Timestamp output Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-26 15:55 ` Re: Timestamp output Tom Lane <tgl@sss.pgh.pa.us>
2002-02-26 17:56 ` Re: Timestamp output Andreas Joseph Krogh <andreak@officenet.no>
2002-02-05 10:59 ` Re: Timestamp output Barry Lind <barry@xythos.com>
@ 2002-03-01 02:32 ` Barry Lind <barry@xythos.com>
2002-03-05 02:13 ` Re: [SQL] Timestamp output Thomas O'Dowd <tom@nooper.com>
0 siblings, 1 reply; 9+ messages in thread
From: Barry Lind @ 2002-03-01 02:32 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-jdbc@postgresql.org
Andreas,
I can't reproduce the problem you are seeing. I created a table 'test'
with one column of type timestamp. Inserted one row with the value from
now(). And then ran a simple jdbc program to select out that one row.
This all worked without errors.
Again I ask are you using the latest 7.2 driver? I know there where
some bugs in earlier 7.2 drivers (I think in all of the betas actually)
that look similar to the problem you are reporting.
thanks,
--Barry
Andreas Joseph Krogh wrote:
> On Tuesday 05 February 2002 11:59, you wrote:
>
>>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?
>>
>
> I have the following table:
> CREATE TABLE FORUMARTICLE(
> ARTICLEID SERIAL NOT NULL PRIMARY KEY,
> FORUMID INTEGER NOT NULL REFERENCES FORUM(FORUMID),
> CATEGORYID INTEGER NOT NULL REFERENCES CATEGORY(CATEGORYID),
> SUBJECT VARCHAR(512) NOT NULL,
> LEADTEXT VARCHAR(4000),
> BODY VARCHAR(4000) NOT NULL,
> USERNAME VARCHAR(64) NOT NULL,
> CREATED TIMESTAMP DEFAULT 'now' NOT NULL,
> LASTPOSTING TIMESTAMP,
> LASTPOSTINGBY VARCHAR(64),
> ISAPPROVED INTEGER DEFAULT 0 NOT NULL,
> APPROVEDBY INTEGER DEFAULT 0 NOT NULL
> );
>
> I have the following trigger on FORUMARTICLE:
> CREATE FUNCTION stamp_article () RETURNS OPAQUE AS '
> BEGIN
> -- Check that lastpostingby is given
> IF NEW.LASTPOSTINGBY ISNULL THEN
> RAISE EXCEPTION ''LASTPOSTINGBY cannot be NULL value'';
> END IF;
>
> -- Remember when LASTPOSTINGBY changed
> NEW.LASTPOSTING := ''now'';
> RETURN NEW;
> END;
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER stamp_article BEFORE UPDATE ON FORUMARTICLE
> FOR EACH ROW EXECUTE PROCEDURE stamp_article();
>
> This causes the follogin entry in FORUMARTICLE:
>
> lastposting
> ------------------------------
> 2002-02-26 18:32:54.83294+01
>
> which throws the Exception in the JDBC driver as follows:
>
> Bad Timestamp Format at 23 in 2002-02-26 18:32:54.83294+01
>
> I have the postgresql.jar from 7.2 in my classpath:
>
> diff ~/postgresql-7.2/src/interfaces/jdbc/jars/postgresql.jar
> ~/catalina/common/lib/postgresql.jar
>
> echo $?
> 0
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Timestamp output
2002-02-26 09:36 Timestamp output Matteo Beccati <m.beccati@crpsoftware.it>
2002-02-26 14:39 ` Re: Timestamp output Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-26 15:55 ` Re: Timestamp output Tom Lane <tgl@sss.pgh.pa.us>
2002-02-26 17:56 ` Re: Timestamp output Andreas Joseph Krogh <andreak@officenet.no>
2002-02-05 10:59 ` Re: Timestamp output Barry Lind <barry@xythos.com>
2002-03-01 02:32 ` Re: [SQL] Timestamp output Barry Lind <barry@xythos.com>
@ 2002-03-05 02:13 ` Thomas O'Dowd <tom@nooper.com>
0 siblings, 0 replies; 9+ messages in thread
From: Thomas O'Dowd @ 2002-03-05 02:13 UTC (permalink / raw)
To: Barry Lind <barry@xythos.com>; +Cc: Andreas Joseph Krogh <andreak@officenet.no>; pgsql-jdbc@postgresql.org
On Thu, Feb 28, 2002 at 06:32:54PM -0800, Barry Lind wrote:
> Andreas,
>
> I can't reproduce the problem you are seeing. I created a table 'test'
> with one column of type timestamp. Inserted one row with the value from
> now(). And then ran a simple jdbc program to select out that one row.
> This all worked without errors.
>
> Again I ask are you using the latest 7.2 driver? I know there where
> some bugs in earlier 7.2 drivers (I think in all of the betas actually)
> that look similar to the problem you are reporting.
Yep, that should definately be fixed in the latest 7.2 driver. Also,
there is a patch waiting to fix a different timestamp bug which I
submitted on Thu, 17 Jan 2002 22:57:08 which fixes the usage of
timestamp without the timezone.
Tom.
--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Timestamp output
2002-02-26 09:36 Timestamp output Matteo Beccati <m.beccati@crpsoftware.it>
2002-02-26 14:39 ` Re: Timestamp output Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-26 15:55 ` Re: Timestamp output Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-02-27 03:17 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-27 05:48 ` Re: Timestamp output Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Christopher Kings-Lynne @ 2002-02-27 03:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matteo Beccati <m.beccati@crpsoftware.it>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Timestamp output
2002-02-26 09:36 Timestamp output Matteo Beccati <m.beccati@crpsoftware.it>
2002-02-26 14:39 ` Re: Timestamp output Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-26 15:55 ` Re: Timestamp output Tom Lane <tgl@sss.pgh.pa.us>
2002-02-27 03:17 ` Re: Timestamp output Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-02-27 05:48 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2002-02-27 05:48 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Matteo Beccati <m.beccati@crpsoftware.it>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2002-03-05 02:13 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-26 09:36 Timestamp output Matteo Beccati <m.beccati@crpsoftware.it>
2002-02-26 14:39 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-26 15:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-26 17:56 ` Andreas Joseph Krogh <andreak@officenet.no>
2002-02-05 10:59 ` Barry Lind <barry@xythos.com>
2002-03-01 02:32 ` Barry Lind <barry@xythos.com>
2002-03-05 02:13 ` Thomas O'Dowd <tom@nooper.com>
2002-02-27 03:17 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-27 05:48 ` Tom Lane <tgl@sss.pgh.pa.us>
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