Обсуждение: Re: [GENERAL] binary timestamp conversion

I originally incorrectly posted my question to the
GENERAL list with the same subject.

As I indicated in my original post I am trying to
convert an binary timestamp (int64).

After further experimentation I have yet to find a
method to correctly convert the int64 value retrieved
from a query. 

I save 1970-01-01 00:00:00 and read it back expecting
the difference between postgres and unix epochs but
get different results each time. 

(After conversion to host byte order).

-946686612744257
-946684195712577
-946684196834881

I then tried using PGTYPEStimestamp_to_asc() just to
see what it does and get....

1969-12-31 23:07:28.818367
1969-12-31 23:38:40.464063
1969-12-31 23:52:03.009727

psql shows 1970-01-01 00:00:00

Using 2000-01-01 the minutes/seconds/fractional
seconds  varies as well. 

If request back dates formattea as strings, I get what
psql shows, the correct result. 

Why would pg returning inconsistent binary timestampo
results? 

Thanks








    
__________________________________ 
Yahoo! Music Unlimited 
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/

H Hale <hhale21@yahoo.com> writes:
> I save 1970-01-01 00:00:00 and read it back expecting
> the difference between postgres and unix epochs but
> get different results each time. 

> (After conversion to host byte order).

Sounds to me like you're picking up garbage rather than the data you
should.  Does your code to fetch the binary value produce correct
answers on an ordinary int8 column?
        regards, tom lane

On Fri, Oct 14, 2005 at 05:04:35PM -0400, Tom Lane wrote:
> H Hale <hhale21@yahoo.com> writes:
> > I save 1970-01-01 00:00:00 and read it back expecting
> > the difference between postgres and unix epochs but
> > get different results each time. 
> 
> > (After conversion to host byte order).
> 
> Sounds to me like you're picking up garbage rather than the data you
> should.

Dunno if this is significant, but the garbage appears to be confined
to the lower 32 bits, at least in the three examples given:

test=> select val, to_hex(val) from foo;      val        |      to_hex      
------------------+-------------------946686612744257 | fffca2fe58bbd7bf-946684195712577 |
fffca2fee8ccd1bf-946684196834881| fffca2fee8bbb1bf
 

> Does your code to fetch the binary value produce correct answers
> on an ordinary int8 column?

Could we see the code?  A minimal but complete program that somebody
else could compile and run might be revealing.

-- 
Michael Fuhr