Обсуждение: getting julian number from date in libpq

We're trying to convert dates properly for R.

R stores dates internally as integers, using Unix date style --  
starting date is January 1, 1970.

Postgresql stores dates as Julian days, but using libpq, I don't see  
how to get out a date quickly as a julian day. If I use PQgetvalue,  
my date is automagically converted into whatever my DATESTYLE is at  
the time. How can I just get out the internal julian date? I could  
use this to convert to R dates much easier than using an ISO date  
format.

Thanks for any help.

######################################################################
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.
######################################################################

On Thu, Sep 08, 2005 at 02:12:33PM -0400, Norman Yamada wrote:
> We're trying to convert dates properly for R.
> 
> R stores dates internally as integers, using Unix date style --  
> starting date is January 1, 1970.
> 
> Postgresql stores dates as Julian days, but using libpq, I don't see  
> how to get out a date quickly as a julian day. If I use PQgetvalue,  
> my date is automagically converted into whatever my DATESTYLE is at  
> the time. How can I just get out the internal julian date?

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:

SELECT current_date - '1970-01-01';?column? 
----------   13034
(1 row)

-- 
Michael Fuhr

Norman Yamada <nyamada@millburncorp.com> writes:
> Postgresql stores dates as Julian days, but using libpq, I don't see  
> how to get out a date quickly as a julian day. If I use PQgetvalue,  
> my date is automagically converted into whatever my DATESTYLE is at  
> the time. How can I just get out the internal julian date?

Something involving EXTRACT(EPOCH FROM timestamp) will probably solve
your problem.
        regards, tom lane

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.
######################################################################