Обсуждение: Date/time precision in 7.2

Hello all:

I just upgraded a large application from 7.1 to 7.2. Postgres back end (I
always say "postgres", not "postgresql" FWIW), PHP 4.1 front end. Certain
date related functions immediately began to break.

A little digging showed that the new date/time formats in 7.2 cause PHP's
strtotime() function to break. In the release notes for 7.2 I found this
item:

> Add precision to TIME, TIMESTAMP, and INVERVAL data types (Thomas)

Which I assume is what's causing PHP the problem -- it doesn't appear to
like the floating-point representation.

Question 1: is there a flavor of PGDATESTYLE that makes this representation
go away? I'm fine with only having accuracy down to the second. I tried the
listed values for PGDATESTYLE and could not suppress the format that's
breaking PHP.

Question 2: What exactly does this extra information in the timestamp
represent? Is it standard, or a postgres extension?

Thanks in advance for any help.

-- sgl


=======================================================
Steve Lane

Vice President
Chris Moyer Consulting, Inc.
833 West Chicago Ave Suite 203

Voice: (312) 433-2421       Email: slane@fmpro.com
Fax:   (312) 850-3930       Web:   http://www.fmpro.com
=======================================================

> A little digging showed that the new date/time formats in 7.2 cause PHP's
> strtotime() function to break.

???

> In the release notes for 7.2 I found this item:
> > Add precision to TIME, TIMESTAMP, and INVERVAL data types (Thomas)
> Which I assume is what's causing PHP the problem -- it doesn't appear to
> like the floating-point representation.

It is a fractional seconds field.

> Question 1: is there a flavor of PGDATESTYLE that makes this representation
> go away? I'm fine with only having accuracy down to the second. I tried the
> listed values for PGDATESTYLE and could not suppress the format that's
> breaking PHP.

No, but you can declare your columns to be of type "timestamp(0)" which
will omit the fractional field.

> Question 2: What exactly does this extra information in the timestamp
> represent? Is it standard, or a postgres extension?

It is SQL9x standard, which requires a default precision on timestamp
and timestamp with time zone of six digits. For some reason it requires
a default precision of zero for time and time with time zone, but I
don't think that data type would solve problems.

Doesn't the PHP strtotime() function work with other database products?
Why wouldn't it trip over those too?

btw, you can modify src/backend/parser/gram.y to change the default
behavior.

                     - Thomas