pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedgetting julian number from date in libpq
4+ messages / 3 participants
[nested] [flat]
* getting julian number from date in libpq
@ 2005-09-08 18:12 Norman Yamada <nyamada@millburncorp.com>
2005-09-08 19:37 ` Re: getting julian number from date in libpq Michael Fuhr <mike@fuhr.org>
2005-09-08 19:45 ` Re: getting julian number from date in libpq Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Norman Yamada @ 2005-09-08 18:12 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: Yasser El-Zein <yelzein@millburncorp.com>
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.
######################################################################
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: getting julian number from date in libpq
2005-09-08 18:12 getting julian number from date in libpq Norman Yamada <nyamada@millburncorp.com>
@ 2005-09-08 19:37 ` Michael Fuhr <mike@fuhr.org>
2005-09-08 19:51 ` Re: getting julian number from date in libpq Norman Yamada <nyamada@millburncorp.com>
1 sibling, 1 reply; 4+ messages in thread
From: Michael Fuhr @ 2005-09-08 19:37 UTC (permalink / raw)
To: Norman Yamada <nyamada@millburncorp.com>; +Cc: pgsql-interfaces@postgresql.org, Yasser El-Zein <yelzein@millburncorp.com>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: getting julian number from date in libpq
2005-09-08 18:12 getting julian number from date in libpq Norman Yamada <nyamada@millburncorp.com>
2005-09-08 19:37 ` Re: getting julian number from date in libpq Michael Fuhr <mike@fuhr.org>
@ 2005-09-08 19:51 ` Norman Yamada <nyamada@millburncorp.com>
0 siblings, 0 replies; 4+ messages in thread
From: Norman Yamada @ 2005-09-08 19:51 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; pgsql-interfaces; +Cc: Yasser El-Zein <yelzein@millburncorp.com>
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.
######################################################################
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: getting julian number from date in libpq
2005-09-08 18:12 getting julian number from date in libpq Norman Yamada <nyamada@millburncorp.com>
@ 2005-09-08 19:45 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-09-08 19:45 UTC (permalink / raw)
To: Norman Yamada <nyamada@millburncorp.com>; +Cc: pgsql-interfaces@postgresql.org, Yasser El-Zein <yelzein@millburncorp.com>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-09-08 19:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-08 18:12 getting julian number from date in libpq Norman Yamada <nyamada@millburncorp.com>
2005-09-08 19:37 ` Michael Fuhr <mike@fuhr.org>
2005-09-08 19:51 ` Norman Yamada <nyamada@millburncorp.com>
2005-09-08 19:45 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox