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/
Hello there! Are there any statments or sentences like transform and pivot (from Access) for postgres?? Thanks for yous answers! Sonny
Well, I know the source of your problems, if not the solution... (from the docs) time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The effective limit of precision is determined by the underlying double precision floating-point number used to store values (in seconds for interval and in seconds since 2000-01-01 for timestamp). The useful range of p is from 0 to about 6 for timestamp, but may be more for interval. The system will accept p ranging from 0 to 13. http://www7.us.postgresql.org/users-lounge/docs/7.2/postgres/datatype-dateti me.html I didn't see anything specific about reverting back to the old format, but this might help point you in the right direction Robert > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Matteo Beccati > Sent: Monday, February 25, 2002 12:43 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] 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/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
More info:
CREATE TABLE "stamp" (
"param_none" timestamp,
"param2" timestamp(2),
"param0" timestamp(0)
);
insert into stamp values ('now','now','now');
this should give you a good idea how the new timestamping works, and you'll
need to change your column data type accordingly. (You probably should
verify your data entry still works correctly after you do that!)
The one thing I don't get is that this seems to create fields that are
"timestamp with timezone", but they should be "timestamp without timezone"
(timestamptz vs. timestamp)
When I tried to create a column of type timestamptz(2) it errors out, though
I can create timestamptz columns... is this a bug or am I misinterpreting
the docs?
(btw you can specify a type of "timestamp(n) without time zone" if you
really need it)
Robert
> -----Original Message-----
> From: Robert Treat [mailto:robertt@auctionsolutions.com]
> Sent: Tuesday, February 26, 2002 2:35 PM
> To: Matteo Beccati; pgsql-general@postgresql.org
> Subject: RE: [GENERAL] Timestamp output
>
>
> Well, I know the source of your problems, if not the solution...
>
> (from the docs) time, timestamp, and interval accept an optional
> precision value p which specifies the number of fractional digits
> retained in the seconds field. By default, there is no explicit
> bound on precision. The effective limit of precision is
> determined by the underlying double precision floating-point
> number used to store values (in seconds for interval and in
> seconds since 2000-01-01 for timestamp). The useful range of p is
> from 0 to about 6 for timestamp, but may be more for interval.
> The system will accept p ranging from 0 to 13.
>
> http://www7.us.postgresql.org/users-lounge/docs/7.2/postgres/datat
> ype-datetime.html
>
> I didn't see anything specific about reverting back to the old
> format, but this might help point you in the right direction
>
> Robert
>
>
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Matteo Beccati
> > Sent: Monday, February 25, 2002 12:43 PM
> > To: pgsql-general@postgresql.org
> > Subject: [GENERAL] 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/
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> this should give you a good idea how the new timestamping works, and you'll
> need to change your column data type accordingly. (You probably should
> verify your data entry still works correctly after you do that!)
Right. You can enforce the old two-digit behavior by explicitly defining
your columns to be timestamp(2), by using CURRENT_TIMESTAMP(2), etc.
> The one thing I don't get is that this seems to create fields that are
> "timestamp with timezone"...
That is to assist with upgrades from versions prior to 7.2. To support
the precision syntax, the parser explicitly recognizes TIMESTAMP and
related tokens, and converts TIMESTAMP internally to "timestamptz" and
TIMESTAMP WITHOUT TIME ZONE internally to "timestamp". In future
versions the parser tokens and internal names will line up more like you
would expect, but for this version they do not.
hth
- Thomas