Обсуждение: Timestamp with libpq
Hello everybody, I'm trying to convert timestamp values returned by an SQL query into an unix time_t. I'm using the libpq library. For executing the query I use "PQexecParams" with the parameter "resultFormat" set to 1 in order to obtain results in binary format. The table I'm fetching data from was created with the following statement: create table testtable (c timestamp); The statement executed by "PQexecParams" is a simple "select"-statement: select * from testtable; I've read that postgres internally handles timestamps as double numbers, where the timestamp is represented in seconds since 2000-01-01. Thus, I assumed the data returned by "PQgetvalue" for the timestamp field is encoded in this format. Then I wrote a little routine, which takes the double number obtained from "PQgetvalue" and converts it into an unix time_t. This works perfectly if the postgres server (version 8.1.4-1.1) runs on RHEL but if I query the data from a postgres server (version 8.1.4-1.3) runnning on SLES the result totally wrong. Any ideas? Best regards Jakob Lechner -- Jakob Lechner Research & Development appl.strudl Software GmbH Honauerstraße 4 A-4020 Linz Tel.: [+43] (70) 60 61 62 Fax: [+43] (70) 60 61 62-609 E-Mail: jakob.lechner@applstrudl.com Web: http://www.applstrudl.com Handelsgericht Linz, FN 303988 t
<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>
Hello, Am Montag, den 13.10.2008, 17:32 +0800 schrieb Wilhansen Li: > > Actually, I've done this before. And, uh, you can check out my blog > for details: > > > http://blogs.crammerz-inc.net/thunk/2007/05/09/grabbing_time_in_postgresql_using_libpq I think I've seen your blog before. Your solution is reasonable but unfortunately not feasible for me: I'm writing a simple generic interface for accessing databases and thus I need to cope with arbitrary SQL statements. As I posted before I want to execute the statement "select * from testtable" without any modifications and if there are timestamp coloumns in the result set I want to convert the timestamp values to unix time_t values. I just wonder why my conversion routine for the binary timestamps returned from a postgres server running on RHEL works while it's not for a SLES server. Apparently the same SQL statement executed by exactly the same libpq API calls produces different results for the two postgres servers, one running on RHEL and another on SLES. Both postgres servers basically have the same version (8.1.4). The program that executes the SQL statement runs on my workstation and either connects to my RHEL server or to my SLES machine. Can anybody figure out a reason for this behaviour? Best regards Jakob -- Jakob Lechner Research & Development appl.strudl Software GmbH Honauerstraße 4 A-4020 Linz Tel.: [+43] (70) 60 61 62 Fax: [+43] (70) 60 61 62-609 E-Mail: jakob.lechner@applstrudl.com Web: http://www.applstrudl.com Handelsgericht Linz, FN 303988 t
On Mon, Oct 13, 2008 at 11:57:23AM +0200, Jakob Lechner wrote: > I just wonder why my conversion routine for the binary timestamps > returned from a postgres server running on RHEL works while it's not for > a SLES server. Apparently the same SQL statement executed by exactly the > same libpq API calls produces different results for the two postgres > servers, one running on RHEL and another on SLES. Both postgres servers > basically have the same version (8.1.4). The program that executes the > SQL statement runs on my workstation and either connects to my RHEL > server or to my SLES machine. Please keep in mind that there is no guarantee that your server sends a double in a binary query. This depens on whether integer-datatypes are configure or not. Or in other words, it might be a long long instead of a double. Is there any reason to use a binary transfer? I would not recommend this in a general setup. If you just want to avoid some ascii translation hassle, how about using ecpg instead of libpq? It will take care of everything you need. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!
Hi Michael, Am Montag, den 13.10.2008, 12:46 +0200 schrieb Michael Meskes: > Please keep in mind that there is no guarantee that your server sends a double > in a binary query. This depens on whether integer-datatypes are configure or > not. Or in other words, it might be a long long instead of a double. Yes, you're right. The SLES postgres server transmits timestamps as long long numbers (microseconds since 2000-01-01). > Is there any reason to use a binary transfer? If I use textual transfer I'm losing precision. E.g. for timestamps the returned string from my table is "1955-06-08 00:00:00". Thus I'm restricted to timestamps with a granularity of 1 second. > I would not recommend this in a > general setup. If you just want to avoid some ascii translation hassle, how > about using ecpg instead of libpq? The generic database interface I'm writing is part of a basic library used in our project. The interface serves as an abstraction for accessing different database servers (Postgres, MSSQL, ...). I'm currently working on the postgres implementation of the interface. As far as I've seen using ecpg means to hardcode SQL statements which of course can't be done in a generic library. Thanks for your helpful hints. But it's seems I don't have much choice but to use ascii transfer mode. Best regards Jakob -- Jakob Lechner Research & Development appl.strudl Software GmbH Honauerstraße 4 A-4020 Linz Tel.: [+43] (70) 60 61 62 Fax: [+43] (70) 60 61 62-609 E-Mail: jakob.lechner@applstrudl.com Web: http://www.applstrudl.com Handelsgericht Linz, FN 303988 t
> > Is there any reason to use a binary transfer? > > If I use textual transfer I'm losing precision. E.g. for timestamps the > returned string from my table is "1955-06-08 00:00:00". Thus I'm > restricted to timestamps with a granularity of 1 second. I doubt this. It might be true for double storage but not for 8 byte integers, well that is in the database of course. But I see no reason whatsoever that the data is truncated by libpq. > > about using ecpg instead of libpq? > > The generic database interface I'm writing is part of a basic library > used in our project. The interface serves as an abstraction for > accessing different database servers (Postgres, MSSQL, ...). I'm > currently working on the postgres implementation of the interface. > As far as I've seen using ecpg means to hardcode SQL statements which of > course can't be done in a generic library. No, this is not true. You can execute arbitrary statements in ecpg as well. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!
Hi, Am Montag, den 13.10.2008, 14:59 +0200 schrieb Michael Meskes: > > I doubt this. It might be true for double storage but not for 8 byte integers, > well that is in the database of course. But I see no reason whatsoever that the > data is truncated by libpq. > I found an easy solution for my problem: Libpq provides information if the timestamps are double values or integer values. When passing the parameter "integer_datetimes" to the function "PQparameterStatus" "on" or "off" is returned. I adapted my conversion routine to handle both integer and double timestamps. Everything works fine now. Best regards Jakob -- Jakob Lechner Research & Development appl.strudl Software GmbH Honauerstraße 4 A-4020 Linz Tel.: [+43] (70) 60 61 62 Fax: [+43] (70) 60 61 62-609 E-Mail: jakob.lechner@applstrudl.com Web: http://www.applstrudl.com Handelsgericht Linz, FN 303988 t