pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedbinary timestamp conversion
5+ messages / 4 participants
[nested] [flat]
* binary timestamp conversion
@ 2005-10-13 13:39 H Hale <hhale21@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: H Hale @ 2005-10-13 13:39 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hello,
I have a table column TIMESTAMP (6) WITHOUT TIME ZONE
that I am trying to read and write as binary. Postgres
is configured to use int64 for timestamps (not
double).
I found tm2timestamp() in libpgtypes.so that seems to
convert struct tm to a Postgres timestamp correctly
(at least for the dates I have tried). However the
corresponding function timestamp2tm() is static and
therefore not exported from the lib. I tried exporting
and using it but the results are incorrect.
Minutes,seconds and sec fractions are always
incorrect.
For example..
in: 1805-Jun-21 11:21:47.999000
out: 1805-Jun-21 11:40:02
The backend has similiar functions that take struct
pg_tm instead of struct tm as in libpgtypes.so but
these functions but are not accessible. These
functions appear to be different from the ones with
the same names in libpgtypes.so.
Other than that I can't find anything else. What do I
used to convert Postgres binary timestamps to
something usable out side of Postgres?
When doing queries either all data returned is binary
or text, can't request indivdual columns to be binary
or text (or can you?). So I need a way of converting
timestamps to an external binary format like struct tm
+ seconds fractions.
Thanks
PS. This is with v8.0.3 on Linux (intel)
BTW. Using text in/out is not a option in this case.
__________________________________
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] binary timestamp conversion
@ 2005-10-13 23:09 Bruce Momjian <pgman@candle.pha.pa.us>
parent: H Hale <hhale21@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2005-10-13 23:09 UTC (permalink / raw)
To: H Hale <hhale21@yahoo.com>; +Cc: pgsql-interfaces
Can someone on the interfaces list assist this person? Thanks.
---------------------------------------------------------------------------
H Hale wrote:
> Hello,
>
> I have a table column TIMESTAMP (6) WITHOUT TIME ZONE
> that I am trying to read and write as binary. Postgres
> is configured to use int64 for timestamps (not
> double).
>
> I found tm2timestamp() in libpgtypes.so that seems to
> convert struct tm to a Postgres timestamp correctly
> (at least for the dates I have tried). However the
> corresponding function timestamp2tm() is static and
> therefore not exported from the lib. I tried exporting
> and using it but the results are incorrect.
> Minutes,seconds and sec fractions are always
> incorrect.
>
> For example..
> in: 1805-Jun-21 11:21:47.999000
> out: 1805-Jun-21 11:40:02
>
> The backend has similiar functions that take struct
> pg_tm instead of struct tm as in libpgtypes.so but
> these functions but are not accessible. These
> functions appear to be different from the ones with
> the same names in libpgtypes.so.
>
> Other than that I can't find anything else. What do I
> used to convert Postgres binary timestamps to
> something usable out side of Postgres?
>
> When doing queries either all data returned is binary
> or text, can't request indivdual columns to be binary
> or text (or can you?). So I need a way of converting
> timestamps to an external binary format like struct tm
> + seconds fractions.
>
>
> Thanks
>
> PS. This is with v8.0.3 on Linux (intel)
> BTW. Using text in/out is not a option in this case.
>
>
>
> __________________________________
> Yahoo! Music Unlimited
> Access over 1 million songs. Try it free.
> http://music.yahoo.com/unlimited/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] binary timestamp conversion
@ 2005-10-14 20:00 H Hale <hhale21@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: H Hale @ 2005-10-14 20:00 UTC (permalink / raw)
To: pgsql-interfaces
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] binary timestamp conversion
@ 2005-10-14 21:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: H Hale <hhale21@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2005-10-14 21:04 UTC (permalink / raw)
To: H Hale <hhale21@yahoo.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] binary timestamp conversion
@ 2005-10-15 03:09 Michael Fuhr <mike@fuhr.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Fuhr @ 2005-10-15 03:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: H Hale <hhale21@yahoo.com>; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-10-15 03:09 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-13 13:39 binary timestamp conversion H Hale <hhale21@yahoo.com>
2005-10-13 23:09 ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-10-14 20:00 Re: [GENERAL] binary timestamp conversion H Hale <hhale21@yahoo.com>
2005-10-14 21:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-15 03:09 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox