Skip site navigation (1) Skip section navigation (2)

Re: Retrieving timestamp data

From: "Wilhansen Li" <willi(dot)t1(at)gmail(dot)com>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: Retrieving timestamp data
Date: 2007-03-30 17:14:09
Message-ID: bc9549a50703301014l92ca0b5n51fa8d534d1c4cab@mail.gmail.com (view raw or flat)
Thread:
Lists: 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(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.

In response to

pgsql-interfaces by date

Next:From: MicazMAKDate: 2007-04-02 15:45:52
Subject: Cross Compile of libpq
Previous:From: Andy ShellamDate: 2007-03-29 19:29:30
Subject: Re: pgadmin III 1.6.2 crashes when inserting a row

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group