Timestamp with libpq

[Jakob Lechner-4]

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@...
Web: http://www.applstrudl.com
Handelsgericht Linz, FN 303988 t

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[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

On Mon, Oct 13, 2008 at 3:53 PM, Jakob Lechner <jakob.lechner@...> wrote:

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@...
Web: http://www.applstrudl.com
Handelsgericht Linz, FN 303988 t

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

--
Life is too short for dial-up.

[Jakob Lechner-4]

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@...
Web: http://www.applstrudl.com
Handelsgericht Linz, FN 303988 t

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[Michael Meskes-3]

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@...
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[Jakob Lechner-4]

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@...
Web: http://www.applstrudl.com
Handelsgericht Linz, FN 303988 t

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[Michael Meskes-3]

> > 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@...
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[Jakob Lechner-4]

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@...
Web: http://www.applstrudl.com
Handelsgericht Linz, FN 303988 t

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[Andrejs Cainikovs]

Hi Jakob,
can you post your code?
I've wrote my own binary timestamp functions (available here), but looking forward to adapt my code to doubles also.

Jakob Lechner-4 wrote:

>
> 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