Re: getting julian number from date in libpq

Thanks for both your quick responses. Hm. If we could alter the SQL
automatically, we would; we're trying to keep dates as dates between
PostgreSQL and R without changing the query: this means the date
subtraction operator or EXTRACT don't really work for us. We're
trying to hack into RdbiPgSQL (the connector for R -> postgresql, so
that it returns dates to R as dates, not as text). Our queries take
twice as long to process because once we get a resultset back with
dates, we have to cast all the dates from text to R's date class.

If we could somehow use functions within libpq to massage the query
results back so that we could get date as a julian number, that would
do it. Or if we could have a custom DATESTYLE. The problem with
PQexecParams is that you have to choose binary results for all
columns; for most other columns, text is sufficient.

Any other ideas?

On Sep 8, 2005, at 3:45 PM, Tom Lane wrote:
> Something involving EXTRACT(EPOCH FROM timestamp) will probably solve
> your problem.

On Sep 8, 2005, at 3:37 PM, Michael Fuhr wrote:

> In 7.4 and later you could use PQexecParams() to request binary
> results; in earlier versions you could use a binary cursor. But
> the result will be days since 1 Jan 2000, not 1 Jan 1970, so you'd
> have to do a conversion; you'd also have to convert the binary value
> from network byte order to host byte order. It might be easier to
> use the date subtraction operator:

######################################################################
This e-mail is confidential and should not be redistributed or
forwarded by the recipient. The information contained in this e-mail
message is intended only for the use of the individual or entity named
above. If the reader of this message is not the intended recipient or
you have received this communication in error, please immediately
notify us by telephone. Receipt by anyone other than the intended
recipient is not a waiver of any work-product or, if applicable,
attorney-client privilege.

This e-mail does not constitute an offering of any security. Such an
offering may only be made by means of a private placement memorandum
or other disclosure document. Nothing in this e-mail constitutes
investment advice. Past performance is not indicative of future
results. All e-mail to and from Millburn Ridgefield Corporation and
its affiliates is monitored, stored and made available to regulators
if requested.
######################################################################