[SQL] Weeks elapsed to Months elapsed conversion
Hi all, I have a simple question (tried googling but found no answers). How do I convert weeks elapsed into months elapsed? I have data that contains duration in weeks (without any other date values such as year and so on) for example a week value of 14 and I would like to convert the 14 weeks to 3 months (some lose of accuracy expected). Expected tests may be: 14 weeks yields 3 months. 1 weeks yields 0 months. Is there such a function. The rudimentary solution (may reduce lose of accuracy) am thinking of is to add the weeks to the value returned by clock_timestamp() then subtract clock_timestamp() from it. The question now is how to convert the returned value to months elapsed as opposed to days elapsed. Allan. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Weeks elapsed to Months elapsed conversion
Approx. 52 weeks in a year, thus 13 weeks in 3 months. select numberofweeks/13*3 as numberofmonths from yourtable or if you want whole months returned select floor(numberofweeks/13*3) as numberofmonths from yourtable >>> Allan Kamau <[EMAIL PROTECTED]> 2008-05-30 11:21 >>> Hi all, I have a simple question (tried googling but found no answers). How do I convert weeks elapsed into months elapsed? I have data that contains duration in weeks (without any other date values such as year and so on) for example a week value of 14 and I would like to convert the 14 weeks to 3 months (some lose of accuracy expected). Expected tests may be: 14 weeks yields 3 months. 1 weeks yields 0 months. Is there such a function. The rudimentary solution (may reduce lose of accuracy) am thinking of is to add the weeks to the value returned by clock_timestamp() then subtract clock_timestamp() from it. The question now is how to convert the returned value to months elapsed as opposed to days elapsed. Allan. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Weeks elapsed to Months elapsed conversion
On Fri, May 30, 2008 at 4:21 AM, Allan Kamau <[EMAIL PROTECTED]> wrote: > Expected tests may be: > 14 weeks yields 3 months. > 1 weeks yields 0 months. Assuming 4 week months: SELECT 14 / 4 AS "3 months", 1 / 4 AS "0 months" 3 months | 0 months --+-- 3 |0 (1 row) -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Weeks elapsed to Months elapsed conversion
Allan Kamau wrote:
Hi all,
I have a simple question (tried googling but found no answers). How do I
convert weeks elapsed into months elapsed?
I have data that contains duration in weeks (without any other date
values such as year and so on) for example a week value of 14 and I
would like to convert the 14 weeks to 3 months (some lose of accuracy
expected).
Expected tests may be:
14 weeks yields 3 months.
1 weeks yields 0 months.
Are these stored as intervals?
=> SELECT extract(month from justify_days('14 weeks'::interval));
date_part
---
3
(1 row)
=> SELECT extract(month from justify_days('1 week'::interval));
date_part
---
0
(1 row)
If you just have a number (e.g. 14) then you can just do something like:
=> SELECT round(14 * 7 / 30);
round
---
3
(1 row)
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Weeks elapsed to Months elapsed conversion
> > Hi all, > I have a simple question (tried googling but found no > answers). How do I convert weeks elapsed into months elapsed? > I have data that contains duration in weeks (without any > other date values such as year and so on) for example a week > value of 14 and I would like to convert the 14 weeks to 3 > months (some lose of accuracy expected). > Expected tests may be: > 14 weeks yields 3 months. > 1 weeks yields 0 months. > If accuracy isnt a issue probably floor() could suite you: months=select floor(weeks/4); Regards, Fernando. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
