Postgres, Timestamps and the C API

I ran into something unexpected, recently, related to libpq (the Postgres C API). When running PQexecParams() / PQexecPrepared() and passing a "1" for the resultFormat argument (ie, provide results in binary), a timestamp is returned as a 64-bit value (a uint64 or float, depending on a compile-time option). I forgot that I had tossed that "1" in, so that explains the "unexpected" part. Anyway, the uint64 value represents microseconds since 2000-01-01 00:00:00.

uint64_t timestamp;
timestamp = *(uint64_t*)PQgetvalue(pgresult, row, col);
timestamp = swap64(timestamp);

One possible value of timestamp is 303,687,735,731,339. It can be interpreted as follows:

303,687,735,731,339 = microseconds since 2000
303,687,735,731,339 / 1,000,000 = ~303,687,735 = seconds since 2000
303,687,735 + 946,684,800 (epoch delta) = 1,250,372,535 = seconds since 1970
1,250,372,535 = 2009.08.15 21:42:15

If your postgres install was compiled with the option to store timestamps as floats, you'll obviously have to change your code accordingly.