On Thu, 19 Apr 2007, Jim Stewart wrote:
> I'm trying to figure out how to get a database timestamp value (specifically a
> "timestamp with time zone") into some sort of native Unix/C++ data type.
> Ideally a struct timeval (ala gettimeofday()), but any type that supports at
> least millisecond accuracy is ok. I haven't been able to find anything
> documented for this.
>
> Any suggestions?
I ended up hacking up a solution to this:
dbHandle->prepare("selectTargetsStmt",
"select *, "
"extract(epoch from date_trunc('second', execution_time))::bigint as
_epoch_sec, "
"extract(milliseconds from execution_time)::integer as _epoch_sub "
"from target left join nac_target on "
"target.target_id = nac_target.target_id "
"where "
"execution_time >= $1::timestamp with time zone and "
"execution_time < $1::timestamp with time zone + $2::interval")
("timestamp", pqxx::prepare::treat_string)
("interval", pqxx::prepare::treat_string);
... store query in row ...
long execTimeSec, execTimeSecWithSub;
row["_epoch_sec"].to(execTimeSec);
row["_epoch_sub"].to(execTimeSecWithSub);
struct timeval tv;
tv.tv_sec = execTimeSec;
tv.tv_usec = (execTimeSecWithSub - (floor(execTimeSecWithSub / 1000000) *
1000000));
It sure ain't pretty, but it gets a PostgreSQL timestamp with subsecond
accuracy into a native time structure. I chose struct timeval because it
matches my own needs, but if you just need nearest-second accuracy, the first
extract line (without date_trunc(), if you don't mind it rounding up to whole
seconds) will get you seconds-since-epoch, suitable for dumping directly into
a time_t.
Still looking for a better solution, if anyone has one. I was hoping to find
a timestamp struct in libpq that could hold all the details, including time
zone and milli/micro(/nano?) seconds, but I haven't found that yet.
Jim
_______________________________________________
Libpqxx-general mailing list
Libpqxx-general@gborg.postgresql.org
http://gborg.postgresql.org/mailman/listinfo/libpqxx-general
