pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRetrieving timestamp data
6+ messages / 3 participants
[nested] [flat]
* Retrieving timestamp data
@ 2007-03-04 15:23 ::Willi the One:: <sonic_and_tails2000@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: ::Willi the One:: @ 2007-03-04 15:23 UTC (permalink / raw)
To: pgsql-interfaces
In the documentation, it says that PQgetvalue()
returns string values for strings and "the value is in
the binary representation determined by the data
types typsend and typreceive functions." The problem
is, where's the typsend and typrecieve located? I know
that it's not in libpq, but I saw libpgtypes somewhere
in the postgres directory and saw that there are
PGTYPE-related functions but I can't find the typsend.
For example, if I want to retrieve a timestamp data,
how would I do it? Will it return as a string when I
call PQgetvalue()? What about other types such Points
or inet types?
Thanks in advance.
Life is too short for dial-up.
____________________________________________________________________________________
Now that's room service! Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel to find your fit.
http://farechase.yahoo.com/promo-generic-14795097
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Retrieving timestamp data
@ 2007-03-04 17:23 Michael Fuhr <mike@fuhr.org>
parent: ::Willi the One:: <sonic_and_tails2000@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Fuhr @ 2007-03-04 17:23 UTC (permalink / raw)
To: ::Willi the One:: <sonic_and_tails2000@yahoo.com>; +Cc: pgsql-interfaces
On Sun, Mar 04, 2007 at 07:23:10AM -0800, ::Willi the One:: wrote:
> In the documentation, it says that PQgetvalue()
> returns string values for strings and "the value is in
> the binary representation determined by the data
> type’s typsend and typreceive functions."
You've omitted part of what the documentation says. The complete
text is:
For data in text format, the value returned by PQgetvalue is a
null-terminated character string representation of the field
value. For data in binary format, the value is in the binary
representation determined by the data type's typsend and typreceive
functions.
> The problem is, where's the typsend and typrecieve located?
In the backend. You can find out which functions they are by
querying pg_type, and you can learn more about the functions
themselves by querying pg_proc and by examining the source code.
But you should only need to do this if you need query results in
binary format. Do you?
> For example, if I want to retrieve a timestamp data,
> how would I do it? Will it return as a string when I
> call PQgetvalue()? What about other types such Points
> or inet types?
Unless you request results in binary format all values will be
returned as text strings. What happened when you tried it? Are
you requesting results in binary format?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Retrieving timestamp data
@ 2007-03-04 17:54 ::Willi the One:: <sonic_and_tails2000@yahoo.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 6+ messages in thread
From: ::Willi the One:: @ 2007-03-04 17:54 UTC (permalink / raw)
To: pgsql-interfaces
>
> Unless you request results in binary format all
> values will be
> returned as text strings. What happened when you
> tried it? Are
> you requesting results in binary format?
>
So all data are returned as string unless I say so?
I'll try expermenting this a bit.
by the way, are there any site containing tutorials on
libpq?
Life is too short for dial-up.
____________________________________________________________________________________
Finding fabulous fares is fun.
Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains.
http://farechase.yahoo.com/promo-generic-14795097
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Retrieving timestamp data
@ 2007-03-04 19:17 Michael Fuhr <mike@fuhr.org>
parent: ::Willi the One:: <sonic_and_tails2000@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Fuhr @ 2007-03-04 19:17 UTC (permalink / raw)
To: ::Willi the One:: <sonic_and_tails2000@yahoo.com>; +Cc: pgsql-interfaces
On Sun, Mar 04, 2007 at 09:54:59AM -0800, ::Willi the One:: wrote:
> > Unless you request results in binary format all values will be
> > returned as text strings. What happened when you tried it? Are
> > you requesting results in binary format?
>
> So all data are returned as string unless I say so?
PQexecParams() and PQexecPrepared() take an argument that specifies
the result format so you have to indicate which format you want.
PQexec() doesn't take a format argument so it returns results in
text format unless you're fetching from a binary cursor.
> by the way, are there any site containing tutorials on
> libpq?
Probably; Google is your friend :-)
--
Michael Fuhr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Retrieving timestamp data
@ 2007-03-10 03:18 Wilhansen Li <willi.t1@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Wilhansen Li @ 2007-03-10 03:18 UTC (permalink / raw)
To: pgsql-interfaces
--- Michael Fuhr <mike@fuhr.org> wrote:
> For data in text format, the value returned by
> PQgetvalue is a
> null-terminated character string representation of
> the field
> value. For data in binary format, the value is in
> the binary
> representation determined by the data type's
> typsend and typreceive
> functions.
>
> But you should only need to do this if you need
> query results in
> binary format. Do you?
Actually, what I really needed is a timestamp data in either time_t or
struct tm format so that my application could easily manipulate the
data. If the data recieved is in string format, it would be a hassle
to manually parse the string just to convert it to struct tm.
Moreover, I'm not so sure but, if nls is supported, the query to
timestamp data might return the string formatted in another language
which would add more complications to parsing.
> Unless you request results in binary format all
> values will be
> returned as text strings. What happened when you
> tried it? Are
> you requesting results in binary format?
>
When I tried getting the results in binary format, I get some value
which I have no idea how to parse, however, PostgreSQL has functions
(timestamp_recv) for converting timestamp data to struct pg_tm (which
I need) somewhere in timestamp.c/.h and it uses j2date which is
located in datetime.c/.h (both are located at \src\utils\). The
problem with using these is that they are (probably) part of the
backend. Could libpq be manipulated so that it includes those
functions (it could probably share that same code that that it's
synched with the backend format)?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Retrieving timestamp data
@ 2007-03-30 17:14 Wilhansen Li <willi.t1@gmail.com>
parent: Wilhansen Li <willi.t1@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Wilhansen Li @ 2007-03-30 17:14 UTC (permalink / raw)
To: pgsql-interfaces
I finally found a solution thanks to the folks at the IRC channel.
Basically, when querying timestamp data, wrap it with extract( epoch from
timestampColumn )::bigint so the output of the query would be equivalent to
time_t.
Here's a sample:
/*
* CREATE TABLE datetimetest
* (
* "time" timestamp with time zone,
* id serial NOT NULL );
*/
unsigned __int64 value[2] = { 0 };
PGconn *connection = PQconectdb( "" );
//handle errors
//Binary
PGresult *res = PQexecParams( connection, "SELECT extract(epoch from
time)::bigint FROM datetimetest", 0, 0, 0, 0, 0, 1 );
for ( int i = 0; i < PQntuples( res ); i++ ) {
unsigned char *data = (unsigned char*)PQgetvalue( res, i, 0 );
unsigned int length = PQgetlength( res, i , 0 );
assert( length == sizeof(value[0]) );
value[0] = htonll(*(unsigned __int64*)(data)); //switch byte order
}
PQclear( res );
//String
res = PQexecParams( connection, "SELECT extract(epoch from time)::bigint
FROM datetimetest", 0, 0, 0, 0, 0, 0 );
for ( int i = 0; i < PQntuples( res ); i++ ) {
unsigned char *data = (unsigned char*)PQgetvalue( res, i, 1 );
unsigned int length = PQgetlength( res, i , 1 );
values[1] = _atoi64( (char*)data );
}
cout << "Binary: " << ctime( (time_t*)values ) << endl
<< "String: " << ctime( (time_t*)(values + 1) ) << endl;
PQclear( res );
PQfinish( connection );
Hope this helps in the future!
On 3/10/07, Wilhansen Li <willi.t1@gmail.com> wrote:
>
> --- Michael Fuhr <mike@fuhr.org> wrote:
>
> > For data in text format, the value returned by
> > PQgetvalue is a
> > null-terminated character string representation of
> > the field
> > value. For data in binary format, the value is in
> > the binary
> > representation determined by the data type's
> > typsend and typreceive
> > functions.
> >
> > But you should only need to do this if you need
> > query results in
> > binary format. Do you?
>
> Actually, what I really needed is a timestamp data in either time_t or
> struct tm format so that my application could easily manipulate the
> data. If the data recieved is in string format, it would be a hassle
> to manually parse the string just to convert it to struct tm.
> Moreover, I'm not so sure but, if nls is supported, the query to
> timestamp data might return the string formatted in another language
> which would add more complications to parsing.
>
> > Unless you request results in binary format all
> > values will be
> > returned as text strings. What happened when you
> > tried it? Are
> > you requesting results in binary format?
> >
>
> When I tried getting the results in binary format, I get some value
> which I have no idea how to parse, however, PostgreSQL has functions
> (timestamp_recv) for converting timestamp data to struct pg_tm (which
> I need) somewhere in timestamp.c/.h and it uses j2date which is
> located in datetime.c/.h (both are located at \src\utils\). The
> problem with using these is that they are (probably) part of the
> backend. Could libpq be manipulated so that it includes those
> functions (it could probably share that same code that that it's
> synched with the backend format)?
>
--
(<_<)(>_>)(>_<)(<.<)(>.>)(>.<)
Life is too short for dial-up.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-03-30 17:14 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-04 15:23 Retrieving timestamp data ::Willi the One:: <sonic_and_tails2000@yahoo.com>
2007-03-04 17:23 ` Michael Fuhr <mike@fuhr.org>
2007-03-04 17:54 ` ::Willi the One:: <sonic_and_tails2000@yahoo.com>
2007-03-04 19:17 ` Michael Fuhr <mike@fuhr.org>
2007-03-10 03:18 Re: Retrieving timestamp data Wilhansen Li <willi.t1@gmail.com>
2007-03-30 17:14 ` Wilhansen Li <willi.t1@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox