Skip site navigation (1) Skip section navigation (2)

Re: fixes for date_part micro/millisecond precision

From: Brent Verner <brent(at)rcfile(dot)org>
To: Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: fixes for date_part micro/millisecond precision
Date: 2001-11-26 14:11:02
Message-ID: 20011126141102.GB7219@rcfile.org (view raw or flat)
Thread:
Lists: pgsql-patches
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

In response to

Responses

pgsql-patches by date

Next:From: Bruce MomjianDate: 2001-11-26 17:58:02
Subject: Re: Chinese NLS patch, the third try.
Previous:From: Weiping HeDate: 2001-11-26 12:57:29
Subject: Re: Chinese NLS patch, the third try.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group