From: Sergey Konoplev <gray.ru@gmail.com>
To: Ireneusz Pluta <ipluta@wp.pl>
Cc: pgsql-sql@postgresql.org
Subject: Re: Week to date function
Date: Thu, 25 Mar 2010 12:44:38 +0300
Message-ID: <c3a7de1f1003250244p440b8244t57319b742fa680f0@mail.gmail.com> (raw)
In-Reply-To: <4BAB2C15.8090406@wp.pl>
References: <4BAB2C15.8090406@wp.pl>
On 25 March 2010 12:25, Ireneusz Pluta <ipluta@wp.pl> wrote:
> Hello,
>
> is there any standard function, or a concise solution based on set of them,
> returning a set of dates included in a week of given year and week number?
> I ended up with creating my own function as in the example below, but I am
> curious if I am not opening an open door.
Try to think of something like this?
SELECT
date_trunc('week', '2010-01-01'::date) +
'12 week'::interval +
(d::text||' day')::interval
FROM generate_series(0, 6) AS d;
>
> Thanks
>
> Irek.
>
> CREATE OR REPLACE FUNCTION week2date(double precision, double precision)
> RETURNS SETOF date
> AS
> $_$
> SELECT day
> FROM (
> SELECT to_char(day, 'IYYY')::integer AS iyyy,
> to_char(day, 'IW' )::integer AS iw,
> day
> FROM (
> SELECT start + generate_series(0, n) AS day
> FROM (
> SELECT start,
> (stop - start)::integer AS n
> FROM (
> SELECT (to_date($1::text, 'YYYY'::text) - interval
> '3 days')::date AS start,
> (to_date($1::text, 'YYYY'::text) + interval '1 year
> 3 days')::date AS stop
> ) ss
> ) aa
> ) bb
> ) cc
> WHERE iw = $2 AND iyyy = $1
> ORDER
> BY day
> $_$
> LANGUAGE SQL
> IMMUTABLE
> ;
>
> SELECT week2date(date_part('year', now()), date_part('week', now()));
> week2date
> ------------
> 2010-03-22
> 2010-03-23
> 2010-03-24
> 2010-03-25
> 2010-03-26
> 2010-03-27
> 2010-03-28
> (7 rows)
>
> SELECT week2date(2009, 53);
> week2date
> ------------
> 2009-12-28
> 2009-12-29
> 2009-12-30
> 2009-12-31
> 2010-01-01
> 2010-01-02
> 2010-01-03
> (7 rows)
>
> SELECT week2date(2010, 1);
> week2date
> ------------
> 2010-01-04
> 2010-01-05
> 2010-01-06
> 2010-01-07
> 2010-01-08
> 2010-01-09
> 2010-01-10
> (7 rows)
>
>
--
Sergey Konoplev
Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gray.ru@gmail.com, ipluta@wp.pl
Subject: Re: Week to date function
In-Reply-To: <c3a7de1f1003250244p440b8244t57319b742fa680f0@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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