Sergey Konoplev pisze:
On 25 March 2010 12:25, Ireneusz Pluta <ipl...@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;
Yes, much smarter.
However, would give the same results on (year=2009, week=53) and
(year=2010, week=1). In fact, 2009 did not have week 53.
I wrapped it into a function with additional isoyear check and now seems OK.
Thanks
CREATE OR REPLACE FUNCTION your_week2date(double precision, double
precision) RETURNS SETOF date
AS
$_$
SELECT day
FROM (
SELECT (
date_trunc('week', ($1::text||'-01-01')::date)
+ ($2::text||' week')::interval
+ ( d::text||' day')::interval
)::date AS day
FROM generate_series(0, 6) AS d
) alias
WHERE to_char(day, 'IYYY')::integer = $1
ORDER
BY 1
$_$
LANGUAGE SQL
IMMUTABLE
;
SELECT week2date1(date_part('year', now()), date_part('week', now()));
week2date1
------------
2010-03-22
2010-03-23
2010-03-24
2010-03-25
2010-03-26
2010-03-27
2010-03-28
(7 rows)
SELECT your_week2date(2009, 52) ;
your_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 your_week2date(2009, 53) ;
your_week2date
----------------
(0 rows)
SELECT your_week2date(2010, 1) ;
your_week2date
----------------
2010-01-04
2010-01-05
2010-01-06
2010-01-07
2010-01-08
2010-01-09
2010-01-10
(7 rows)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
