09/05/07
Grabbing time in PostgreSQL using libpq
One of the things I’ve always wondered in PostgreSQL is how to turn the timestamp data retrieved using libpq into something more managable aside from string values like time_t or struct tm (useful for programs that manipulates time data and not just display them). One way is to dig through the PostgresSQL sources to find the actual function (there’s actually a function to convert the binary data into struct pg_tm) but that’s not a good idea since it may change when there’s a new release and you’ll get one more thing to compile with your program. Another way is to use “extract(epoch from [identifier])” which converts the timestamp data into seconds elapsed after January 1, 1970 which is… you guessed it, equivalent to time_t. But before actually retrieving it, you have to cast it to bigint first since it’s actually using a floating-point representation. Here’s the SQL statement:
Follow up:
SQL:
| SELECT extract(epoch FROM [FIELD])::bigint FROM [TABLE] |
The actual code goes like this (note that “time” here is actually a field), error checking omitted for clarity:
C++:
| PGconn *connection; | |
| PGresult *res = PQexecParams( connection, "SELECT extract(epoch from time)::bigint FROM datetimetest", 0, 0, 0, 0, 0, 1 ); | |
| //binary, note the byte-order flipping | |
| for ( int i = 0; i < PQntuples( res ); i++ ) { | |
| unsigned __int64 timestamp; | |
| unsigned char *data = (unsigned char*)PQgetvalue( res, i, 0 ); | |
| unsigned int length = PQgetlength( res, i , 0 ); | |
| assert( length == sizeof(timestamp) ); | |
| timestamp = ntohull(*(unsigned __int64*)(data)); | |
| cout << "binary: " << timestamp << " | " << ctime( (time_t)timestamp )<< endl; | |
| cout << endl; | |
| } | |
| PQclear( res ); | |
| //String version | |
| 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 __int64 timestamp; | |
| unsigned char *data = (unsigned char*)PQgetvalue( res, i, 0 ); | |
| unsigned int length = PQgetlength( res, i , 0 ); | |
| timestamp = _atoi64( (char*)data ); | |
| cout << "string: " << timestamp << " | " << ctime( (time_t)timestamp )<< endl; | |
| cout << endl; | |
| } |
Trackback address for this post
Trackback URL (right click and copy shortcut/link location)
No feedback yet
Comment feed for this postLeave a comment
