Lists: | pgsql-general |
---|
From: | "Chris Hoover" <revoohc(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Help with date math |
Date: | 2007-07-20 15:08:57 |
Message-ID: | 1d219a6f0707200808m61d49693oe925c1626bf27fd5@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
I need some help. I am trying to replicate a function from Sybase ASA, and
am having difficulty.
I need to be able to subtract 2 date (or timestamps) and return the results
expressed in days, weeks, month, quarters, or years. How do I do this?
I believe Postgres is returning the number of days when you subtract to
days.
i.e. postgres=# select current_date - '2007/01/01';
?column?
----------
200
(1 row)
However, I can not figure out how to get the results expressed as 7 months,
or 2 quarters, x weeks, or 0 years. I have tried to use date_part, but
since the subtraction is returning an integer, it is not happy.
Any help would be greatly appreciated.
Chris
P.S.
I'm trying to replicate Sybase's datediff function.
From: | Reid Thompson <reid(dot)thompson(at)ateb(dot)com> |
---|---|
To: | Chris Hoover <revoohc(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Help with date math |
Date: | 2007-07-21 23:19:30 |
Message-ID: | 46A29482.5080905@ateb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Chris Hoover wrote:
> I need some help. I am trying to replicate a function from Sybase
> ASA, and am having difficulty.
>
> I need to be able to subtract 2 date (or timestamps) and return the
> results expressed in days, weeks, month, quarters, or years. How do I
> do this?
>
> I believe Postgres is returning the number of days when you subtract
> to days.
> i.e. postgres=# select current_date - '2007/01/01';
> ?column?
> ----------
> 200
> (1 row)
>
>
>
> However, I can not figure out how to get the results expressed as 7
> months, or 2 quarters, x weeks, or 0 years. I have tried to use
> date_part, but since the subtraction is returning an integer, it is
> not happy.
>
> Any help would be greatly appreciated.
>
> Chris
>
> P.S.
>
> I'm trying to replicate Sybase's datediff function.
postgres=# select age(current_date,'2007/01/01');
age
----------------
6 mons 20 days
(1 row)
http://www.postgresql.org/docs/8.2/static/functions-datetime.html
From: | Reid Thompson <reid(dot)thompson(at)ateb(dot)com> |
---|---|
To: | Chris Hoover <revoohc(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Help with date math |
Date: | 2007-07-21 23:22:55 |
Message-ID: | 46A2954F.6090802@ateb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Chris Hoover wrote:
> I need some help. I am trying to replicate a function from Sybase
> ASA, and am having difficulty.
>
> I need to be able to subtract 2 date (or timestamps) and return the
> results expressed in days, weeks, month, quarters, or years. How do I
> do this?
>
> I believe Postgres is returning the number of days when you subtract
> to days.
> i.e. postgres=# select current_date - '2007/01/01';
> ?column?
> ----------
> 200
> (1 row)
>
>
>
> However, I can not figure out how to get the results expressed as 7
> months, or 2 quarters, x weeks, or 0 years. I have tried to use
> date_part, but since the subtraction is returning an integer, it is
> not happy.
>
> Any help would be greatly appreciated.
>
> Chris
>
> P.S.
>
> I'm trying to replicate Sybase's datediff function.
postgres=# select date_part('month', age(current_date, '2007/01/01'));
date_part
-----------
6
(1 row)
From: | Ragnar <gnari(at)hive(dot)is> |
---|---|
To: | Chris Hoover <revoohc(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Help with date math |
Date: | 2007-07-21 23:42:39 |
Message-ID: | 1185061359.5778.230.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On fös, 2007-07-20 at 11:08 -0400, Chris Hoover wrote:
> I need some help. I am trying to replicate a function from Sybase
> ASA, and am having difficulty.
>
> I need to be able to subtract 2 date (or timestamps) and return the
> results expressed in days, weeks, month, quarters, or years. How do I
> do this?
>
> I believe Postgres is returning the number of days when you subtract
> to days.
>
> However, I can not figure out how to get the results expressed as 7
> months,
would not 6 months be more logical?
> or 2 quarters, x weeks, or 0 years.
have you tried to use extract() ?
there is an old compatibility project on pgfoundry that has a
datediff function, but i have no idea how well it copies the
mssql behaviour.
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/mssqlsupport/datetimefunctions.sql?rev=1.1.1.1&content-type=text/x-cvsweb-markup
it looks a bit simplistic to me, but maybe that is exactly what
you need.
gnari