Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 0A3DD9FB32D for ; Fri, 30 Mar 2007 14:14:26 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 39498-07 for ; Fri, 30 Mar 2007 14:14:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.235]) by postgresql.org (Postfix) with ESMTP id D9CC69FB323 for ; Fri, 30 Mar 2007 14:14:10 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id i28so604300wxd for ; Fri, 30 Mar 2007 10:14:09 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=eQ5Ny9fVy9bjfKsN3ECN4Tpq/XRJrisBUUYtq+hEPFakdj0/BXcpV83Bmp1Y3J18e/xtXV/AlVYPvaM658dmI7Iqdu8bFjx3sDtJH/hKTiZLMRvG6pAC+zGo3cZOKyUKnvWLIYV+2pa8HCLYI//ZKB/HbL3JsHesRBuoWTwrOt8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=V7733cS+D4/tYmFRAba0UdPogx4GNPax01TMWoIhQUIqgvmWPudogw1oWZgH1B9h42y8upfnT5oN7rVx3xNf39+8zYqm2EEcMVOveJW8513LQXRysFfex7lP68qzJlmzMjfY15TNNmXq01Tgg0OHf2FRaYNvRZwvDvVSBVDnUq4= Received: by 10.90.52.18 with SMTP id z18mr2042465agz.1175274849359; Fri, 30 Mar 2007 10:14:09 -0700 (PDT) Received: by 10.90.100.19 with HTTP; Fri, 30 Mar 2007 10:14:09 -0700 (PDT) Message-ID: Date: Sat, 31 Mar 2007 01:14:09 +0800 From: "Wilhansen Li" To: pgsql-interfaces@postgresql.org Subject: Re: Retrieving timestamp data In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_47587_955596.1175274849318" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/47 X-Sequence-Number: 6488 ------=_Part_47587_955596.1175274849318 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 wrote: > > --- Michael Fuhr 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. ------=_Part_47587_955596.1175274849318 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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. ------=_Part_47587_955596.1175274849318--