pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWeek to date function
10+ messages / 5 participants
[nested] [flat]
* Week to date function
@ 2010-03-25 09:25 Ireneusz Pluta <ipluta@wp.pl>
0 siblings, 1 reply; 10+ messages in thread
From: Ireneusz Pluta @ 2010-03-25 09:25 UTC (permalink / raw)
To: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
<font face="Courier New, Courier, monospace">Hello,<br>
<br>
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? <br>
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. <br>
<br>
Thanks<br>
<br>
Irek.<br>
<br>
CREATE OR REPLACE FUNCTION week2date(double precision, double
precision) RETURNS SETOF date<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>
</body>
</html>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Week to date function
@ 2010-03-25 09:44 Sergey Konoplev <gray.ru@gmail.com>
parent: Ireneusz Pluta <ipluta@wp.pl>
0 siblings, 1 reply; 10+ messages in thread
From: Sergey Konoplev @ 2010-03-25 09:44 UTC (permalink / raw)
To: Ireneusz Pluta <ipluta@wp.pl>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Week to date function
@ 2010-03-26 10:48 Ireneusz Pluta <ipluta@wp.pl>
parent: Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Ireneusz Pluta @ 2010-03-26 10:48 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: pgsql-sql
Sergey Konoplev pisze:
> 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;
>
>
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Week to date function
@ 2010-03-26 11:02 Sergey Konoplev <gray.ru@gmail.com>
parent: Ireneusz Pluta <ipluta@wp.pl>
1 sibling, 1 reply; 10+ messages in thread
From: Sergey Konoplev @ 2010-03-26 11:02 UTC (permalink / raw)
To: Ireneusz Pluta <ipluta@wp.pl>; +Cc: pgsql-sql
> 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
I think it is better to use date_part('year', day) instead of
to_char(...). And may be it is worth to do raise exception when
incorrect week specified.
--
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Week to date function
@ 2010-03-26 11:47 Ireneusz Pluta <ipluta@wp.pl>
parent: Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Ireneusz Pluta @ 2010-03-26 11:47 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: pgsql-sql
Sergey Konoplev pisze:
>> 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
>>
>
> I think it is better to use date_part('year', day) instead of
> to_char(...).
this might cut first or last week in the year, like this:
SELECT your_week2date(2009, 52) ;
your_week2date
----------------
2009-12-28
2009-12-29
2009-12-30
2009-12-31
2010-01-01 cut
2010-01-02 cut
2010-01-03 cut
(7 rows)
which is not what I want when playing with isoweeks.
> And may be it is worth to do raise exception when
> incorrect week specified.
>
but, maybe, controlled by an additonal parameter saying if one prefers
to have exception or rather an empty resultset
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Week to date function
@ 2010-03-27 18:08 Jorge Godoy <jgodoy@gmail.com>
parent: Ireneusz Pluta <ipluta@wp.pl>
1 sibling, 1 reply; 10+ messages in thread
From: Jorge Godoy @ 2010-03-27 18:08 UTC (permalink / raw)
To: Ireneusz Pluta <ipluta@wp.pl>; +Cc: Sergey Konoplev <gray.ru@gmail.com>; pgsql-sql
Are you sure?
http://en.wikipedia.org/wiki/ISO_8601
Week dates
Main article: ISO week date <http://en.wikipedia.org/wiki/ISO_week_date;
*YYYY-Www* *or* *YYYYWww* *YYYY-Www-D* *or* *YYYYWwwD*
Week date representations are in the format as shown in the box to the
right. [YYYY] indicates the *ISO week-numbering year* which is slightly
different to the calendar year (see below). [Www] is the *week
number*prefixed by the letter 'W', from W01 through W53. [D] is the
*weekday number*, from 1 through 7, beginning with Monday and ending with
Sunday. This form is popular in the manufacturing industries.
There are mutually equivalent descriptions of week 01:
- the week with the year's first Thursday in it (the formal ISO
definition),
- the week with 4 January in it,
- the first week with the majority (four or more) of its days in
the starting year, and
- the week starting with the Monday in the period 29 December – 4
January.
*If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week
01.* If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53
of the previous year (there is no week 00). 28 December is always in the
last week of its year.
The week number can be described by counting the Thursdays: week 12 contains
the 12th Thursday of the year.
In 2009, January 1st. happened on a Thursday.
As Jan 1st, 2010 happened on a Friday, it was on week 53 of 2009.
--
Jorge Godoy <jgodoy@gmail.com>
On Fri, Mar 26, 2010 at 07:48, Ireneusz Pluta <ipluta@wp.pl> wrote:
>
> 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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Week to date function
@ 2010-03-27 20:22 Ireneusz Pluta <ipluta@wp.pl>
parent: Jorge Godoy <jgodoy@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ireneusz Pluta @ 2010-03-27 20:22 UTC (permalink / raw)
To: Jorge Godoy <jgodoy@gmail.com>; +Cc: Sergey Konoplev <gray.ru@gmail.com>; pgsql-sql
Jorge Godoy pisze:
> Are you sure?
>
> http://en.wikipedia.org/wiki/ISO_8601
snip
>
> As Jan 1st, 2010 happened on a Friday, it was on week 53 of 2009.
you are right, thanks for pointing that out.
I didn't check the opposite way like this:
select date_part('week', '2010-01-01'::date);
date_part
-----------
53
I need to recheck my code.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Week to date function
@ 2010-03-27 21:31 Hiltibidal, Rob <Rob.Hiltibidal@argushealth.com>
parent: Ireneusz Pluta <ipluta@wp.pl>
0 siblings, 2 replies; 10+ messages in thread
From: Hiltibidal, Rob @ 2010-03-27 21:31 UTC (permalink / raw)
To: Ireneusz Pluta <ipluta@wp.pl>; Jorge Godoy <jgodoy@gmail.com>; +Cc: Sergey Konoplev <gray.ru@gmail.com>; pgsql-sql
Uhhhh only 52 calendar weeks in a year... I'm almost sure that is the
norm
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Ireneusz Pluta
Sent: Saturday, March 27, 2010 3:22 PM
To: Jorge Godoy
Cc: Sergey Konoplev; pgsql-sql@postgresql.org
Subject: Re: [SQL] Week to date function
Jorge Godoy pisze:
> Are you sure?
>
> http://en.wikipedia.org/wiki/ISO_8601
snip
>
> As Jan 1st, 2010 happened on a Friday, it was on week 53 of 2009.
you are right, thanks for pointing that out.
I didn't check the opposite way like this:
select date_part('week', '2010-01-01'::date);
date_part
-----------
53
I need to recheck my code.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:postmaster@argushealth.com. Thank you.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Week to date function
@ 2010-03-28 01:51 Jorge Godoy <jgodoy@gmail.com>
parent: Hiltibidal, Rob <Rob.Hiltibidal@argushealth.com>
1 sibling, 0 replies; 10+ messages in thread
From: Jorge Godoy @ 2010-03-28 01:51 UTC (permalink / raw)
To: Hiltibidal, Rob <Rob.Hiltibidal@argushealth.com>; +Cc: Ireneusz Pluta <ipluta@wp.pl>; Sergey Konoplev <gray.ru@gmail.com>; pgsql-sql
It isn't. The norm is defined by ISO 8601.
There is a lot of "fun" into date calculations.
The rule is to counts the number of Thursdays on a year to get the correct
number of weeks.
What bothers me more is that here it is cultural that weeks start on Sunday,
but ISO 8601 says they start on Monday. In Portuguese, the word for Monday
can also be translated as "Second", as in "Second day". How to say that the
second day is in fact the first? :-)
--
Jorge Godoy <jgodoy@gmail.com>
On Sat, Mar 27, 2010 at 18:31, Hiltibidal, Rob <
Rob.Hiltibidal@argushealth.com> wrote:
> Uhhhh only 52 calendar weeks in a year... I'm almost sure that is the
> norm
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Week to date function
@ 2010-03-31 11:44 Jasen Betts <jasen@xnet.co.nz>
parent: Hiltibidal, Rob <Rob.Hiltibidal@argushealth.com>
1 sibling, 0 replies; 10+ messages in thread
From: Jasen Betts @ 2010-03-31 11:44 UTC (permalink / raw)
To: pgsql-sql
On 2010-03-27, Hiltibidal, Rob <Rob.Hiltibidal@argushealth.com> wrote:
> Uhhhh only 52 calendar weeks in a year... I'm almost sure that is the
> norm
All hours have 60 minutes
All weeks have 7 days
All years have 12 months
all else is variable.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2010-03-31 11:44 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-25 09:25 Week to date function Ireneusz Pluta <ipluta@wp.pl>
2010-03-25 09:44 ` Sergey Konoplev <gray.ru@gmail.com>
2010-03-26 10:48 ` Ireneusz Pluta <ipluta@wp.pl>
2010-03-26 11:02 ` Sergey Konoplev <gray.ru@gmail.com>
2010-03-26 11:47 ` Ireneusz Pluta <ipluta@wp.pl>
2010-03-27 18:08 ` Jorge Godoy <jgodoy@gmail.com>
2010-03-27 20:22 ` Ireneusz Pluta <ipluta@wp.pl>
2010-03-27 21:31 ` Hiltibidal, Rob <Rob.Hiltibidal@argushealth.com>
2010-03-28 01:51 ` Jorge Godoy <jgodoy@gmail.com>
2010-03-31 11:44 ` Jasen Betts <jasen@xnet.co.nz>
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