From: Wilhansen Li <willi.t1@gmail.com>
To: pgsql-interfaces@postgresql.org
Subject: Re: Retrieving timestamp data
Date: Sat, 31 Mar 2007 01:14:09 +0800
Message-ID: <bc9549a50703301014l92ca0b5n51fa8d534d1c4cab@mail.gmail.com> (raw)
In-Reply-To: <bc9549a50703091918n2676e7c6l26c960dc2f9cd717@mail.gmail.com>
References: <bc9549a50703091918n2676e7c6l26c960dc2f9cd717@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: willi.t1@gmail.com
Subject: Re: Retrieving timestamp data
In-Reply-To: <bc9549a50703301014l92ca0b5n51fa8d534d1c4cab@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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