Re: Timestamp with libpq - Mailing list pgsql-interfaces

<div dir="ltr"><br /><p>Actually, I've done this before. And, uh, you can check out my blog for details:<br /><p><a
href="http://blogs.crammerz-inc.net/thunk/2007/05/09/grabbing_time_in_postgresql_using_libpq">http://blogs.crammerz-inc.net/thunk/2007/05/09/grabbing_time_in_postgresql_using_libpq</a><div
class="gmail_quote">OnMon, Oct 13, 2008 at 3:53 PM, Jakob Lechner <span dir="ltr"><<a
href="mailto:jakob.lechner@applstrudl.com">jakob.lechner@applstrudl.com</a>></span>wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"> Hello everybody,<br /><br />
I'mtrying to convert timestamp values returned by an SQL query into an<br /> unix time_t. I'm using the libpq
library.<br/><br /> For executing the query I use "PQexecParams" with the parameter<br /> "resultFormat" set to 1 in
orderto obtain results in binary format.<br /><br /> The table I'm fetching data from was created with the following<br
/>statement:<br /> create table testtable (c timestamp);<br /><br /> The statement executed by "PQexecParams" is a
simple"select"-statement:<br /> select * from testtable;<br /><br /><br /> I've read that postgres internally handles
timestampsas double numbers,<br /> where the timestamp is represented in seconds since 2000-01-01.<br /> Thus, I
assumedthe data returned by "PQgetvalue" for the timestamp<br /> field is encoded in this format. Then I wrote a little
routine,which<br /> takes the double number obtained from "PQgetvalue" and converts it into<br /> an unix time_t. This
worksperfectly if the postgres server (version<br /> 8.1.4-1.1) runs on RHEL but if I query the data from a postgres
server<br/> (version 8.1.4-1.3) runnning on SLES the result totally wrong.<br /><br /> Any ideas?<br /><br /> Best
regards<br/> Jakob Lechner<br /><br /><br /> --<br /> Jakob Lechner<br /> Research & Development<br /> appl.strudl
SoftwareGmbH<br /> Honauerstraße 4<br /> A-4020 Linz<br /> Tel.: [+43] (70) 60 61 62<br /> Fax: [+43] (70) 60 61
62-609<br/> E-Mail: <a href="mailto:jakob.lechner@applstrudl.com">jakob.lechner@applstrudl.com</a><br /> Web: <a
href="http://www.applstrudl.com"target="_blank">http://www.applstrudl.com</a><br /> Handelsgericht Linz, FN 303988 t<br
/><fontcolor="#888888"><br /> --<br /> Sent via pgsql-interfaces mailing list (<a
href="mailto:pgsql-interfaces@postgresql.org">pgsql-interfaces@postgresql.org</a>)<br/> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-interfaces"
target="_blank">http://www.postgresql.org/mailpref/pgsql-interfaces</a><br/></font></blockquote></div><br /><br
clear="all"/><br />-- <br />Life is too short for dial-up.<br /></div>