Re: Retrieving timestamp data

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(dot)t1(at)gmail(dot)com> wrote:
>
> --- Michael Fuhr <mike(at)fuhr(dot)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.