Week to date function
От | Ireneusz Pluta |
---|---|
Тема | Week to date function |
Дата | |
Msg-id | 4BAB2C15.8090406@wp.pl обсуждение исходный текст |
Ответы |
Re: Week to date function
|
Список | pgsql-sql |
<font face="Courier New, Courier, monospace">Hello,<br /><br /> is there any standard function, or a concise solution basedon set of them, returning a set of dates included in a week of given year and week number? <br /> I ended up with creatingmy own function as in the example below, but I am curious if I am not opening an open door. <br /><br /> Thanks<br/><br /> Irek.<br /><br /> CREATE OR REPLACE FUNCTION week2date(double precision, double precision) RETURNS SETOFdate<br /> AS<br /> $_$<br /> SELECT day<br /> FROM (<br /> SELECT to_char(day, 'IYYY')::integer AS iyyy,<br/> to_char(day, 'IW' )::integer AS iw,<br /> day<br /> FROM (<br /> SELECT start + generate_series(0, n) AS day<br /> FROM (<br /> SELECT start,<br /> (stop - start)::integer AS n<br /> FROM (<br /> SELECT (to_date($1::text,'YYYY'::text) - interval '3 days')::date AS start,<br /> (to_date($1::text,'YYYY'::text) + interval '1 year 3 days')::date AS stop<br /> ) ss<br /> ) aa<br /> ) bb<br /> ) cc<br /> WHERE iw = $2 AND iyyy = $1<br /> ORDER<br /> BY day<br /> $_$<br/> LANGUAGE SQL<br /> IMMUTABLE<br /> ;<br /><br /> SELECT week2date(date_part('year', now()), date_part('week', now()));<br/> week2date<br /> ------------<br /> 2010-03-22<br /> 2010-03-23<br /> 2010-03-24<br /> 2010-03-25<br /> 2010-03-26<br /> 2010-03-27<br /> 2010-03-28<br /> (7 rows)<br /><br /> SELECT week2date(2009, 53);<br /> week2date<br/> ------------<br /> 2009-12-28<br /> 2009-12-29<br /> 2009-12-30<br /> 2009-12-31<br /> 2010-01-01<br/> 2010-01-02<br /> 2010-01-03<br /> (7 rows)<br /><br /> SELECT week2date(2010, 1);<br /> week2date<br/> ------------<br /> 2010-01-04<br /> 2010-01-05<br /> 2010-01-06<br /> 2010-01-07<br /> 2010-01-08<br/> 2010-01-09<br /> 2010-01-10<br /> (7 rows)<br /><br /></font>
В списке pgsql-sql по дате отправления: