pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Date Foo.
2+ messages / 2 participants
[nested] [flat]
* Re: Date Foo.
@ 2004-02-20 20:25 Brian Knox <laotse@aol.net>
0 siblings, 1 reply; 2+ messages in thread
From: Brian Knox @ 2004-02-20 20:25 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: pgsql-sql
Sorry, Not looking for a way to extract a month from a timestamp. I'm
looking for a way to convert an interval from days to months. I'm not
sure after digging into it that there is a way to handle it in SQL, as
the interval that results from subtracting one timestamp from another is
not away of what months the interval spans, so there'd be no proper way
to take month lengths into account.
I gave up on the sql and used Date::Calc from Perl. Thanks for your
answer anyway.
scott.marlowe wrote:
> On Thu, 19 Feb 2004, Brian Knox wrote:
>
>
>>( sorry if this is a repeat, my mail server is being wonky today )
>>
>>I'm looking for a way, within SQL, given a starting date and an ending
>>date, to get back the number of months between the start and end date.
>>If I "SELECT end_date - start_date", I get back an interval in days; I
>>need months.
>
>
> Maybe date_part?
>
> select date_part('month','2004-08-02 12:00:00'::timestamp) -
> date_part('month','2004-05-01 12:00:00'::timestamp);
>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Date Foo.
@ 2004-02-20 21:18 scott.marlowe <scott.marlowe@ihs.com>
parent: Brian Knox <laotse@aol.net>
0 siblings, 0 replies; 2+ messages in thread
From: scott.marlowe @ 2004-02-20 21:18 UTC (permalink / raw)
To: Brian Knox <laotse@aol.net>; +Cc: pgsql-sql
I'm still not sure what you were looking for.
If you have, say, March 16th, and the next date is August 23rd, do you
want to count March, April, May, Jun, July, August = 6? Or do you want to
count the number of 30 day periods?
Using date_part gets you the first one.
Plus, since months can be 28, 29, 30, or 31 days long, how exactly does
one convert 58 days to months? Without knowing the month the start and
end dates have, you wouldn't know for sure if it was two or three. and
what about parts of months, a week in the end of march, all of april, and
the first two weeks of May, is that two months or three?
I guess my point is that I'm not sure what question you were asking, so
I'm not sure how Perl's Date::Calc did better or worse than would
postgresql.
On Fri, 20 Feb 2004, Brian Knox wrote:
> Sorry, Not looking for a way to extract a month from a timestamp. I'm
> looking for a way to convert an interval from days to months. I'm not
> sure after digging into it that there is a way to handle it in SQL, as
> the interval that results from subtracting one timestamp from another is
> not away of what months the interval spans, so there'd be no proper way
> to take month lengths into account.
>
> I gave up on the sql and used Date::Calc from Perl. Thanks for your
> answer anyway.
>
> scott.marlowe wrote:
> > On Thu, 19 Feb 2004, Brian Knox wrote:
> >
> >
> >>( sorry if this is a repeat, my mail server is being wonky today )
> >>
> >>I'm looking for a way, within SQL, given a starting date and an ending
> >>date, to get back the number of months between the start and end date.
> >>If I "SELECT end_date - start_date", I get back an interval in days; I
> >>need months.
> >
> >
> > Maybe date_part?
> >
> > select date_part('month','2004-08-02 12:00:00'::timestamp) -
> > date_part('month','2004-05-01 12:00:00'::timestamp);
> >
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-02-20 21:18 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-20 20:25 Re: Date Foo. Brian Knox <laotse@aol.net>
2004-02-20 21:18 ` scott.marlowe <scott.marlowe@ihs.com>
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