agora inbox for pgsql-interfaces@postgresql.org
help / color / mirror / Atom feedlibpq binary data
3+ messages / 3 participants
[nested] [flat]
* libpq binary data
@ 2014-10-01 16:26 thilo@riessner.de
2014-10-01 19:45 ` Re: libpq binary data Daniel Verite <daniel@manitou-mail.org>
2014-10-02 11:43 ` Re: libpq binary data Thilo Rießner <thilo@riessner.de>
0 siblings, 2 replies; 3+ messages in thread
From: thilo@riessner.de @ 2014-10-01 16:26 UTC (permalink / raw)
To: pgsql-interfaces
Hello,
I try to get the epoch value of a date via the
PQexecParams(conn, "SELECT extract(epoch from date + time) as epoch, content
FROM daten .....);
In that database, the timestamp ist stored in the two fields date and time.
I want to get this data in binary form. The
PQfsize(res, 1);
tells me, that the size of the returned data is 8 byte (in contrast to the
standard size of epoch, which is meant to be 4 byte)
I don't manage to get the epoch valule (seconds since 1970) from that returned
value. After ntohll (which I wrote as a wrapper around ntohl for long int, see
below) it is a very huge value (4743709917079142400) but it should be
1412179252 as I get it from the psql interface, when I type in the same
command.
What am I missing or doing wrong?
Thanks for any help in advance
Thilo
unsigned long int ntohll(long int x)
{
if (ntohl(1) == 1)
return x;
else
return (long int) (ntohl((int)((x << 32) >> 32))) << 32 | (long
int)ntohl(((int)(x >> 32)));
}
--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: libpq binary data
2014-10-01 16:26 libpq binary data thilo@riessner.de
@ 2014-10-01 19:45 ` Daniel Verite <daniel@manitou-mail.org>
1 sibling, 0 replies; 3+ messages in thread
From: Daniel Verite @ 2014-10-01 19:45 UTC (permalink / raw)
To: thilo@riessner.de; +Cc: pgsql-interfaces
thilo@riessner.de wrote:
> I try to get the epoch value of a date via the
> PQexecParams(conn, "SELECT extract(epoch from date + time) as epoch, content
> FROM daten .....);
> In that database, the timestamp ist stored in the two fields date and time.
> I want to get this data in binary form. The
> PQfsize(res, 1);
> tells me, that the size of the returned data is 8 byte (in contrast to the
> standard size of epoch, which is meant to be 4 byte)
> I don't manage to get the epoch valule (seconds since 1970) from that
> returned value.
The doc about EXTRACT(field FROM source) says:
"The extract function returns values of type double precision"
In C, that would presumably map to the "double" 64 bits floating point type.
For code converting the binary representation to a host variable, you may get
bits from postgres itself in backend/libpq/pqformat.c
Otherwise, a working example could look like this
(without guarantee that it's suitable for your platform):
#include <stdint.h>
union {
uint64_t i;
double fp;
} swap;
uint64_t ibe = *((uint64_t*)PQgetvalue(result, row, column);
swap.i = be64toh(ibe);
And your result would be in swap.fp
Or you if prefer getting an int from postgres, cast the result of extract()
to an integer in the SQL query itself.
Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: libpq binary data
2014-10-01 16:26 libpq binary data thilo@riessner.de
@ 2014-10-02 11:43 ` Thilo Rießner <thilo@riessner.de>
1 sibling, 0 replies; 3+ messages in thread
From: Thilo Rießner @ 2014-10-02 11:43 UTC (permalink / raw)
To: pgsql-interfaces
Hello again,
after trying the PQftype function on the date row, I realized, that the type
is 701 which is a double. Treating the pointer as a double solved my problem,
now I get the right number of seconds. But it is still necessary to swap the
byte order from network to host with the function "ntohll" as shown below.
Hope this helps someone stucking in a similar problem.
Thilo
> Hello,
> I try to get the epoch value of a date via the
> PQexecParams(conn, "SELECT extract(epoch from date + time) as epoch, content
> FROM daten .....);
> In that database, the timestamp ist stored in the two fields date and time.
> I want to get this data in binary form. The
> PQfsize(res, 1);
> tells me, that the size of the returned data is 8 byte (in contrast to the
> standard size of epoch, which is meant to be 4 byte)
> I don't manage to get the epoch valule (seconds since 1970) from that
> returned value. After ntohll (which I wrote as a wrapper around ntohl for
> long int, see below) it is a very huge value (4743709917079142400) but it
> should be 1412179252 as I get it from the psql interface, when I type in
> the same command.
> What am I missing or doing wrong?
> Thanks for any help in advance
>
> Thilo
>
> unsigned long int ntohll(long int x)
> {
> if (ntohl(1) == 1)
> return x;
> else
> return (long int) (ntohl((int)((x << 32) >> 32))) << 32 | (long
> int)ntohl(((int)(x >> 32)));
> }
--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-10-02 11:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-10-01 16:26 libpq binary data thilo@riessner.de
2014-10-01 19:45 ` Daniel Verite <daniel@manitou-mail.org>
2014-10-02 11:43 ` Thilo Rießner <thilo@riessner.de>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox