From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200510132309.j9DN9OW23521@candle.pha.pa.us>
Subject: Re: [GENERAL] binary timestamp conversion
In-Reply-To: <20051013133945.80741.qmail@web52312.mail.yahoo.com>
To: H Hale <hhale21@yahoo.com>
Date: Thu, 13 Oct 2005 19:09:24 -0400 (EDT)
Cc: PostgreSQL-interfaces <pgsql-interfaces@postgresql.org>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII


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