Re: fixes for date_part micro/millisecond precision

On 26 Nov 2001 at 13:38 (+0100), Karel Zak wrote:
| On Sat, Nov 24, 2001 at 02:24:09PM -0500, Tom Lane wrote:
| > Brent Verner <brent(at)rcfile(dot)org> writes:
| > > This patch fixes a problem where extract/date_part returns bad values
| > > for microsecond and millisecond parts.
| >
| > I'm not convinced the existing behavior is wrong. Timestamps are floats
| > in PG, and therefore not infinitely precise. Your patch seems to be
| > trying to cover this up, but I fear it will only succeed in introducing
| > other problems.
|
| Tom is right. If you need non-float microsecond/millisecond you can
| use to_char() -- it's news in 7.2:
|
| test=# select to_char('2001-11-24 13:49:12.826833-05'::timestamp, 'MS');
| to_char
| ---------
| 827

Also, casting to float4 gives the 'expected' results. The behavior
still seems wrong, since it gives me back something that I did not
put in. Additionally, I can get back a value that I /can't/ put back
in the database. I do agree with Tom that my solution was simply
a cover-up. The SQL-99 spec says nothing of MILLI or MICROSECONDS,
so I'm curious if there is interest in really fixing the date_part
behavior WRT fractional seconds. Comments Thomas?

The only way I can see to fix it is to make Timestamp a struct
something like...

typedef struct _pgTimestamp {
unsigned long ts_epoch; /* seconds */
unsigned int ts_frac; /* microseconds */
signed char ts_tzo; /* GMT offset in hours */
unsigned char ts_precision; /* (???) precision of fractional part */
} Timestamp;

I'll probably try to implement this new Timestamp as a learning
exercise. If my hacking goes well and there is a desire to fix
the current Timestamp limitations, I'll submit a patch.

cheers.
brent

--
"Develop your talent, man, and leave the world something. Records are
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing." -- Duane Allman