Skip site navigation (1) Skip section navigation (2)

Re: getting julian number from date in libpq

From: Norman Yamada <nyamada(at)millburncorp(dot)com>
To: Michael Fuhr <mike(at)fuhr(dot)org>, pgsql-interfaces(at)postgresql(dot)org
Cc: Yasser El-Zein <yelzein(at)millburncorp(dot)com>
Subject: Re: getting julian number from date in libpq
Date: 2005-09-08 19:51:44
Message-ID: B21D072E-0181-4F99-858D-5DFEAD60B638@millburncorp.com (view raw or flat)
Thread:
Lists: pgsql-interfaces
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.
######################################################################

In response to

pgsql-interfaces by date

Next:From: Mischa SandbergDate: 2005-09-09 20:44:33
Subject: Change of default type in DBD::Pg 1.42
Previous:From: Tom LaneDate: 2005-09-08 19:45:06
Subject: Re: getting julian number from date in libpq

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group