Thread: extract (dow/week from date)
The extract (dow from date) function returns 0 for Sunday (nice).
My problem is that Sunday is the last day of the week according to
extract (week from date). Is it the expected behavior?
teste=# create table dates (date timestamp);
CREATE TABLE
teste=# insert into dates values ('2005-08-08');
INSERT 0 1
teste=# insert into dates values ('2005-08-09');
INSERT 0 1
teste=# insert into dates values ('2005-08-10');
INSERT 0 1
teste=# insert into dates values ('2005-08-11');
INSERT 0 1
teste=# insert into dates values ('2005-08-12');
INSERT 0 1
teste=# insert into dates values ('2005-08-13');
INSERT 0 1
teste=# insert into dates values ('2005-08-14');
INSERT 0 1
teste=# select date, extract (week from date) as week, extract (dow
from date) as dow
teste-# from dates
teste-# order by date;
date | week | dow
---------------------+------+-----
2005-08-08 00:00:00 | 32 | 1
2005-08-09 00:00:00 | 32 | 2
2005-08-10 00:00:00 | 32 | 3
2005-08-11 00:00:00 | 32 | 4
2005-08-12 00:00:00 | 32 | 5
2005-08-13 00:00:00 | 32 | 6
2005-08-14 00:00:00 | 32 | 0
(7 rows)
In mysql the date functions work as I need it:
order by yearweek(day, 2) desc, dayofweek(day);
Regards,
Clodoaldo Pinto
Attachment
Uh, you are ordering by 'date', not column 3, try ORDER BY 3.
---------------------------------------------------------------------------
Clodoaldo Pinto wrote:
> The extract (dow from date) function returns 0 for Sunday (nice).
>
> My problem is that Sunday is the last day of the week according to
> extract (week from date). Is it the expected behavior?
>
> teste=# create table dates (date timestamp);
> CREATE TABLE
> teste=# insert into dates values ('2005-08-08');
> INSERT 0 1
> teste=# insert into dates values ('2005-08-09');
> INSERT 0 1
> teste=# insert into dates values ('2005-08-10');
> INSERT 0 1
> teste=# insert into dates values ('2005-08-11');
> INSERT 0 1
> teste=# insert into dates values ('2005-08-12');
> INSERT 0 1
> teste=# insert into dates values ('2005-08-13');
> INSERT 0 1
> teste=# insert into dates values ('2005-08-14');
> INSERT 0 1
> teste=# select date, extract (week from date) as week, extract (dow
> from date) as dow
> teste-# from dates
> teste-# order by date;
> date | week | dow
> ---------------------+------+-----
> 2005-08-08 00:00:00 | 32 | 1
> 2005-08-09 00:00:00 | 32 | 2
> 2005-08-10 00:00:00 | 32 | 3
> 2005-08-11 00:00:00 | 32 | 4
> 2005-08-12 00:00:00 | 32 | 5
> 2005-08-13 00:00:00 | 32 | 6
> 2005-08-14 00:00:00 | 32 | 0
> (7 rows)
>
> In mysql the date functions work as I need it:
> order by yearweek(day, 2) desc, dayofweek(day);
>
> Regards,
> Clodoaldo Pinto
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
2005/8/20, Bruce Momjian <pgman@candle.pha.pa.us>: > > Uh, you are ordering by 'date', not column 3, try ORDER BY 3. > I'm ordering by date just to show that sunday, the 0th day of the week, is the last day of a given week, which is not what I need. I'm migrating a site from mysql to postgres and the logic of the table at the page works for Sunday as the first day of the week. I will make the pg query produce the same behavior as the mysql query but the pg function behavior seems confusing. Note how are the dates sorted at the table: http://fahstats.com/tp.php?t=13802 Regards, Clodoaldo Pinto 2005/8/20, Bruce Momjian <pgman@candle.pha.pa.us>: > > Uh, you are ordering by 'date', not column 3, try ORDER BY 3. > > --------------------------------------------------------------------------- > > Clodoaldo Pinto wrote: > > The extract (dow from date) function returns 0 for Sunday (nice). > > > > My problem is that Sunday is the last day of the week according to > > extract (week from date). Is it the expected behavior? > > > > teste=# create table dates (date timestamp); > > CREATE TABLE > > teste=# insert into dates values ('2005-08-08'); > > INSERT 0 1 > > teste=# insert into dates values ('2005-08-09'); > > INSERT 0 1 > > teste=# insert into dates values ('2005-08-10'); > > INSERT 0 1 > > teste=# insert into dates values ('2005-08-11'); > > INSERT 0 1 > > teste=# insert into dates values ('2005-08-12'); > > INSERT 0 1 > > teste=# insert into dates values ('2005-08-13'); > > INSERT 0 1 > > teste=# insert into dates values ('2005-08-14'); > > INSERT 0 1 > > teste=# select date, extract (week from date) as week, extract (dow > > from date) as dow > > teste-# from dates > > teste-# order by date; > > date | week | dow > > ---------------------+------+----- > > 2005-08-08 00:00:00 | 32 | 1 > > 2005-08-09 00:00:00 | 32 | 2 > > 2005-08-10 00:00:00 | 32 | 3 > > 2005-08-11 00:00:00 | 32 | 4 > > 2005-08-12 00:00:00 | 32 | 5 > > 2005-08-13 00:00:00 | 32 | 6 > > 2005-08-14 00:00:00 | 32 | 0 > > (7 rows) > > > > In mysql the date functions work as I need it: > > order by yearweek(day, 2) desc, dayofweek(day); > > > > Regards, > > Clodoaldo Pinto > > [ Attachment, skipping... ] > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 >
On Sat, Aug 20, 2005 at 09:03:32PM -0400, Bruce Momjian wrote:
>
> Uh, you are ordering by 'date', not column 3, try ORDER BY 3.
I think the point is that extract(week) is inconsistent with
extract(dow) with respect to what week a sunday is on.
> Clodoaldo Pinto wrote:
> > The extract (dow from date) function returns 0 for Sunday (nice).
> >
> > My problem is that Sunday is the last day of the week according to
> > extract (week from date). Is it the expected behavior?
> >
> > teste=# create table dates (date timestamp);
> > CREATE TABLE
> > teste=# insert into dates values ('2005-08-08');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-09');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-10');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-11');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-12');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-13');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-14');
> > INSERT 0 1
> > teste=# select date, extract (week from date) as week, extract (dow
> > from date) as dow
> > teste-# from dates
> > teste-# order by date;
> > date | week | dow
> > ---------------------+------+-----
> > 2005-08-08 00:00:00 | 32 | 1
> > 2005-08-09 00:00:00 | 32 | 2
> > 2005-08-10 00:00:00 | 32 | 3
> > 2005-08-11 00:00:00 | 32 | 4
> > 2005-08-12 00:00:00 | 32 | 5
> > 2005-08-13 00:00:00 | 32 | 6
> > 2005-08-14 00:00:00 | 32 | 0
> > (7 rows)
> >
> > In mysql the date functions work as I need it:
> > order by yearweek(day, 2) desc, dayofweek(day);
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
<inflex> really, I see PHP as like a strange amalgamation of C, Perl, Shell
<crab> inflex: you know that "amalgam" means "mixture with mercury",
more or less, right?
<crab> i.e., "deadly poison"
On Sat, 20 Aug 2005, Bruce Momjian wrote:
> Uh, you are ordering by 'date', not column 3, try ORDER BY 3.
That's not really the issue. The issue is that our definition of date of
week and week of year are somewhat inconsistent with each other. We appear
to be doing week of year per ISO-8601, but what the descriptions I've seen
of that use days 1-7 for Monday-Sunday, whereas we're apparently giving
0-6 for Sunday-Saturday. This means that sorting by (week of year, day of
week) will sort Sundays oddly (since it would for example below sort the
14th before the 8th).
> ---------------------------------------------------------------------------
>
> Clodoaldo Pinto wrote:
> > The extract (dow from date) function returns 0 for Sunday (nice).
> >
> > My problem is that Sunday is the last day of the week according to
> > extract (week from date). Is it the expected behavior?
> >
> > teste=# create table dates (date timestamp);
> > CREATE TABLE
> > teste=# insert into dates values ('2005-08-08');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-09');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-10');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-11');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-12');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-13');
> > INSERT 0 1
> > teste=# insert into dates values ('2005-08-14');
> > INSERT 0 1
> > teste=# select date, extract (week from date) as week, extract (dow
> > from date) as dow
> > teste-# from dates
> > teste-# order by date;
> > date | week | dow
> > ---------------------+------+-----
> > 2005-08-08 00:00:00 | 32 | 1
> > 2005-08-09 00:00:00 | 32 | 2
> > 2005-08-10 00:00:00 | 32 | 3
> > 2005-08-11 00:00:00 | 32 | 4
> > 2005-08-12 00:00:00 | 32 | 5
> > 2005-08-13 00:00:00 | 32 | 6
> > 2005-08-14 00:00:00 | 32 | 0
> > (7 rows)
> >
> > In mysql the date functions work as I need it:
> > order by yearweek(day, 2) desc, dayofweek(day);
Clodoaldo Pinto <clodoaldo.pinto@gmail.com> writes:
> I'm ordering by date just to show that sunday, the 0th day of the
> week, is the last day of a given week, which is not what I need.
extract(week) follows the ISO definition of week, which is pretty
strange anyway, but in particular it says that weeks start on Monday.
extract(dow) follows a different convention. There's not a lot we
can do about this --- we're certainly not going to change extract(week),
and I can't see changing extract(dow) either.
If you feel that weeks start on Sunday you should ignore extract(week).
regards, tom lane
On Sat, 20 Aug 2005, Tom Lane wrote: > Clodoaldo Pinto <clodoaldo.pinto@gmail.com> writes: > > I'm ordering by date just to show that sunday, the 0th day of the > > week, is the last day of a given week, which is not what I need. > > extract(week) follows the ISO definition of week, which is pretty > strange anyway, but in particular it says that weeks start on Monday. > extract(dow) follows a different convention. There's not a lot we > can do about this --- we're certainly not going to change extract(week), > and I can't see changing extract(dow) either. Instead of change the existing ones, couldn't we add a new extract format for "iso day of week" that returns 1-7 for monday-sunday that would be consistent with the week definition?
On Sat, Aug 20, 2005 at 08:49:27PM -0700, Stephan Szabo wrote: > On Sat, 20 Aug 2005, Tom Lane wrote: > > > Clodoaldo Pinto <clodoaldo.pinto@gmail.com> writes: > > > I'm ordering by date just to show that sunday, the 0th day of the > > > week, is the last day of a given week, which is not what I need. > > > > extract(week) follows the ISO definition of week, which is pretty > > strange anyway, but in particular it says that weeks start on Monday. > > extract(dow) follows a different convention. There's not a lot we > > can do about this --- we're certainly not going to change extract(week), > > and I can't see changing extract(dow) either. > > Instead of change the existing ones, couldn't we add a new extract format > for "iso day of week" that returns 1-7 for monday-sunday that would be > consistent with the week definition? AFAIR the to_char() function uses the 1-7 convention. -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "I personally became interested in Linux while I was dating an English major who wouldn't know an operating system if it walked up and bit him." (Val Henson)
2005/8/21, Stephan Szabo <sszabo@megazone.bigpanda.com>:
> On Sat, 20 Aug 2005, Tom Lane wrote:
>
> > Clodoaldo Pinto <clodoaldo.pinto@gmail.com> writes:
> > > I'm ordering by date just to show that sunday, the 0th day of the
> > > week, is the last day of a given week, which is not what I need.
> >
> > extract(week) follows the ISO definition of week, which is pretty
> > strange anyway, but in particular it says that weeks start on Monday.
> > extract(dow) follows a different convention. There's not a lot we
> > can do about this --- we're certainly not going to change extract(week),
> > and I can't see changing extract(dow) either.
>
> Instead of change the existing ones, couldn't we add a new extract format
> for "iso day of week" that returns 1-7 for monday-sunday that would be
> consistent with the week definition?
>
It would work for me. The problem is not if is sunday or monday the
first day of the week, but to make all days of the week from extract
(dow) (or a new extract (isodow)) fit into the same week from extract
(week). It does not happen now:
drop table dates;
create table dates (date timestamp);
insert into dates values ('2004-12-31');
insert into dates values ('2005-01-01');
insert into dates values ('2005-01-02');
insert into dates values ('2005-01-03');
insert into dates values ('2005-01-04');
insert into dates values ('2005-01-05');
insert into dates values ('2005-01-06');
insert into dates values ('2005-01-07');
insert into dates values ('2005-01-08');
insert into dates values ('2005-01-09');
select date,
to_char (date, 'Dy') as cday,
extract (week from date) as eweek,
extract (dow from date) as edow,
to_char (date, 'WW')::int as cweek,
to_char (date, 'D')::int as cdow
from dates
order by date;
date | cday | eweek | edow | cweek | cdow
---------------------+------+-------+------+-------+------
2004-12-31 00:00:00 | Fri | 53 | 5 | 53 | 6
2005-01-01 00:00:00 | Sat | 53 | 6 | 1 | 7
2005-01-02 00:00:00 | Sun | 53 | 0 | 1 | 1
2005-01-03 00:00:00 | Mon | 1 | 1 | 1 | 2
2005-01-04 00:00:00 | Tue | 1 | 2 | 1 | 3
2005-01-05 00:00:00 | Wed | 1 | 3 | 1 | 4
2005-01-06 00:00:00 | Thu | 1 | 4 | 1 | 5
2005-01-07 00:00:00 | Fri | 1 | 5 | 1 | 6
2005-01-08 00:00:00 | Sat | 1 | 6 | 2 | 7
2005-01-09 00:00:00 | Sun | 1 | 0 | 2 | 1
(10 rows)
There is the same mismatch in to_char ('WW') related to to_char ('D')
Of course it would be even better if we could pass parameters to the
functions changing its behavior such as sunday/monday as the first day
or 0-1 as the first day.
Regards, Clodoaldo Pinto
On Sun, 21 Aug 2005, Clodoaldo Pinto wrote:
> 2005/8/21, Stephan Szabo <sszabo@megazone.bigpanda.com>:
> > On Sat, 20 Aug 2005, Tom Lane wrote:
> >
> > > Clodoaldo Pinto <clodoaldo.pinto@gmail.com> writes:
> > > > I'm ordering by date just to show that sunday, the 0th day of the
> > > > week, is the last day of a given week, which is not what I need.
> > >
> > > extract(week) follows the ISO definition of week, which is pretty
> > > strange anyway, but in particular it says that weeks start on Monday.
> > > extract(dow) follows a different convention. There's not a lot we
> > > can do about this --- we're certainly not going to change extract(week),
> > > and I can't see changing extract(dow) either.
> >
> > Instead of change the existing ones, couldn't we add a new extract format
> > for "iso day of week" that returns 1-7 for monday-sunday that would be
> > consistent with the week definition?
> >
> It would work for me. The problem is not if is sunday or monday the
> first day of the week, but to make all days of the week from extract
> (dow) (or a new extract (isodow)) fit into the same week from extract
> (week).
I think something like:
(CASE WHEN extract(dow from date) = 0 THEN 7 else extract(dow from date))
would give consistent results right now, but that's terribly ugly
(although could be hidden in a function). Of course, even if it were to
be added, it'd probably not make it until 8.2, so you'd probably need a
workaround in the meantime anyway.
> There is the same mismatch in to_char ('WW') related to to_char ('D')
I'd think you'd want to be comparing IW and D, but it'd presumably still
be broken since sunday is 1 rather than monday. Since that function is
supposed to emulate Oracle in alot of places IIRC, so it'd be interesting
to see what Oracle does on those dates.
> Of course it would be even better if we could pass parameters to the
> functions changing its behavior such as sunday/monday as the first day
> or 0-1 as the first day.
I don't personally see that as better, really.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> I think something like:
> (CASE WHEN extract(dow from date) = 0 THEN 7 else extract(dow from date))
It's really not that hard:
(extract(dow from date) + 6) % 7
You can rotate to any week-start day you like by substituting different
things for "6".
regards, tom lane
On Sun, 21 Aug 2005, Tom Lane wrote: > Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > > I think something like: > > (CASE WHEN extract(dow from date) = 0 THEN 7 else extract(dow from date)) > > It's really not that hard: > > (extract(dow from date) + 6) % 7 > > You can rotate to any week-start day you like by substituting different > things for "6". Yeah, mod is probably better and more general than case. It's still fairly ugly to require a non-trivial expression to get something that's consistent with the way that extract(week from date) works. ;) For anyone who is considering using this, to get an actual iso day of week, I think you'll need to add 1 at the end since that appears to use a 1-7 range. If you're just using it for sorting or the like, you can get away with not bothering.
On Sun, Aug 21, 2005 at 08:00:45AM -0300, Clodoaldo Pinto wrote: > Of course it would be even better if we could pass parameters to the > functions changing its behavior such as sunday/monday as the first day > or 0-1 as the first day. FWIW, it seems most things that support changing first day of the week to be something other than <default> do it through a global setting. ISTM that would be much less error prone than having to make sure you used it in all your function calls (not to mention a lot less typing...) -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> Yeah, mod is probably better and more general than case. It's still fairly
> ugly to require a non-trivial expression to get something that's
> consistent with the way that extract(week from date) works. ;)
> For anyone who is considering using this, to get an actual iso day of
> week, I think you'll need to add 1 at the end since that appears to use a
> 1-7 range. If you're just using it for sorting or the like, you can get
> away with not bothering.
Yeah. If we were to invent an extract(isodow) option, I'd be inclined
to make it return 1-7 Mon-Sun. So it'd actually produce the same value
as extract(dow), except on Sundays.
regards, tom lane
2005/8/21, Tom Lane <tgl@sss.pgh.pa.us>:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > I think something like:
> > (CASE WHEN extract(dow from date) = 0 THEN 7 else extract(dow from date))
>
> It's really not that hard:
>
> (extract(dow from date) + 6) % 7
>
> You can rotate to any week-start day you like by substituting different
> things for "6".
>
> regards, tom lane
>
Not obvious as extract (isodow) but good enough for me. Thanks.
But then i also need to order by year-week the same way mysql's
yearweek (date, 3) so i did:
drop table dates;
create table dates (date timestamp);
insert into dates values ('1990-01-01');
insert into dates values ('1990-12-31');
insert into dates values ('1991-01-01');
insert into dates values ('1991-12-31');
insert into dates values ('1992-01-01');
insert into dates values ('1992-12-31');
insert into dates values ('1993-01-01');
insert into dates values ('1993-12-31');
insert into dates values ('1994-01-01');
insert into dates values ('1994-12-31');
insert into dates values ('1995-01-01');
insert into dates values ('1995-12-31');
insert into dates values ('1996-01-01');
insert into dates values ('1996-12-31');
insert into dates values ('1997-01-01');
insert into dates values ('1997-12-31');
insert into dates values ('1998-01-01');
insert into dates values ('1998-12-31');
insert into dates values ('1999-01-01');
insert into dates values ('1999-12-31');
insert into dates values ('2000-01-01');
insert into dates values ('2000-12-31');
insert into dates values ('2001-01-01');
insert into dates values ('2001-12-31');
insert into dates values ('2002-01-01');
insert into dates values ('2002-12-31');
insert into dates values ('2003-01-01');
insert into dates values ('2003-12-31');
insert into dates values ('2004-01-01');
insert into dates values ('2004-12-31');
insert into dates values ('2005-01-01');
insert into dates values ('2005-01-02');
insert into dates values ('2005-01-03');
insert into dates values ('2005-01-04');
insert into dates values ('2005-01-05');
insert into dates values ('2005-01-06');
insert into dates values ('2005-01-07');
insert into dates values ('2005-01-08');
insert into dates values ('2005-01-09');
select date,
to_char (date, 'Dy') as cday,
extract (year from date - cast (((extract (dow from date) +6)::int %
7 -3)::text || ' day' as interval)) as yearweek,
extract (week from date) as eweek,
(extract (dow from date) +6)::int % 7 as edow
from dates
order by date;
date | cday | yearweek | eweek | edow
---------------------+------+----------+-------+------
1990-01-01 00:00:00 | Mon | 1990 | 1 | 0
1990-12-31 00:00:00 | Mon | 1991 | 1 | 0
1991-01-01 00:00:00 | Tue | 1991 | 1 | 1
1991-12-31 00:00:00 | Tue | 1992 | 1 | 1
1992-01-01 00:00:00 | Wed | 1992 | 1 | 2
1992-12-31 00:00:00 | Thu | 1992 | 53 | 3
1993-01-01 00:00:00 | Fri | 1992 | 53 | 4
1993-12-31 00:00:00 | Fri | 1993 | 52 | 4
1994-01-01 00:00:00 | Sat | 1993 | 52 | 5
1994-12-31 00:00:00 | Sat | 1994 | 52 | 5
1995-01-01 00:00:00 | Sun | 1994 | 52 | 6
1995-12-31 00:00:00 | Sun | 1995 | 52 | 6
1996-01-01 00:00:00 | Mon | 1996 | 1 | 0
1996-12-31 00:00:00 | Tue | 1997 | 1 | 1
1997-01-01 00:00:00 | Wed | 1997 | 1 | 2
1997-12-31 00:00:00 | Wed | 1998 | 1 | 2
1998-01-01 00:00:00 | Thu | 1998 | 1 | 3
1998-12-31 00:00:00 | Thu | 1998 | 53 | 3
1999-01-01 00:00:00 | Fri | 1998 | 53 | 4
1999-12-31 00:00:00 | Fri | 1999 | 52 | 4
2000-01-01 00:00:00 | Sat | 1999 | 52 | 5
2000-12-31 00:00:00 | Sun | 2000 | 52 | 6
2001-01-01 00:00:00 | Mon | 2001 | 1 | 0
2001-12-31 00:00:00 | Mon | 2002 | 1 | 0
2002-01-01 00:00:00 | Tue | 2002 | 1 | 1
2002-12-31 00:00:00 | Tue | 2003 | 1 | 1
2003-01-01 00:00:00 | Wed | 2003 | 1 | 2
2003-12-31 00:00:00 | Wed | 2004 | 1 | 2
2004-01-01 00:00:00 | Thu | 2004 | 1 | 3
2004-12-31 00:00:00 | Fri | 2004 | 53 | 4
2005-01-01 00:00:00 | Sat | 2004 | 53 | 5
2005-01-02 00:00:00 | Sun | 2004 | 53 | 6
2005-01-03 00:00:00 | Mon | 2005 | 1 | 0
2005-01-04 00:00:00 | Tue | 2005 | 1 | 1
2005-01-05 00:00:00 | Wed | 2005 | 1 | 2
2005-01-06 00:00:00 | Thu | 2005 | 1 | 3
2005-01-07 00:00:00 | Fri | 2005 | 1 | 4
2005-01-08 00:00:00 | Sat | 2005 | 1 | 5
2005-01-09 00:00:00 | Sun | 2005 | 1 | 6
(39 rows)
I am not sure it is bullet proof.
If no one comes up with something simpler, it looks like extract
(yearweek) would be welcome.
Regards, Clodoaldo Pinto
2005/8/21, Jim C. Nasby <jnasby@pervasive.com>: > On Sun, Aug 21, 2005 at 08:00:45AM -0300, Clodoaldo Pinto wrote: > > Of course it would be even better if we could pass parameters to the > > functions changing its behavior such as sunday/monday as the first day > > or 0-1 as the first day. > > FWIW, it seems most things that support changing first day of the week > to be something other than <default> do it through a global setting. > ISTM that would be much less error prone than having to make sure you > used it in all your function calls (not to mention a lot less typing...) > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com 512-569-9461 > Yes, I think you are correct. Regards, Clodoaldo Pinto
There were two hints by readers about this yearweek issue at the 7.4.8 manual: http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html The first by Daniel Grace <graced AT monroe.wednet.edu> 21 May 2004 0:39:19 CREATE OR REPLACE FUNCTION yearweek(TIMESTAMP WITH TIME ZONE) RETURNS INT LANGUAGE plpgsql IMMUTABLE STRICT AS ' DECLARE t TIMESTAMP; BEGIN t := $1::date - EXTRACT(dow FROM $1::date)::int; RETURN EXTRACT(year FROM t)*100 + EXTRACT(week FROM t); END;'; And the second by Wolfgang Diestelkamp <wolfgang AT dndata.de> 09 Mar 2005 15:44:05 CREATE OR REPLACE FUNCTION yearweek(TIMESTAMP) RETURNS INT LANGUAGE plpgsql IMMUTABLE STRICT AS ' DECLARE t TIMESTAMP; d INTEGER; BEGIN d := EXTRACT(dow FROM $1::date)::int; t := $1::date - CASE WHEN d = 0 THEN 6 ELSE d - 1 END; RETURN EXTRACT(year FROM t)*100 + EXTRACT(week FROM t); END;'; While this second attempt improved on the iso day of the week it was broken about the year. Here is the comparison of my yearweek () function and the previous two. The isodow () uses the construct suggested by Tom Lane. create or replace function isodow (timestamp with time zone) returns int language plpgsql immutable strict as ' declare begin return (extract (dow from $1) +6)::int % 7; end;'; create or replace function yearweek (timestamp with time zone) returns int language plpgsql immutable strict as ' declare date timestamp with time zone = $1; fyear integer; begin fyear := extract (year from date - ((isodow (date) -3)::text || \' day\')::interval); return fyear * 100 + extract (week from date); end;'; drop table dates; create table dates (date timestamp); insert into dates values ('1990-01-01'); insert into dates values ('1990-12-31'); insert into dates values ('1991-01-01'); insert into dates values ('1991-12-31'); insert into dates values ('1992-01-01'); insert into dates values ('1992-12-31'); insert into dates values ('1993-01-01'); insert into dates values ('1993-12-31'); insert into dates values ('1994-01-01'); insert into dates values ('1994-12-31'); insert into dates values ('1995-01-01'); insert into dates values ('1995-12-31'); insert into dates values ('1996-01-01'); insert into dates values ('1996-12-31'); insert into dates values ('1997-01-01'); insert into dates values ('1997-12-31'); insert into dates values ('1998-01-01'); insert into dates values ('1998-12-31'); insert into dates values ('1999-01-01'); insert into dates values ('1999-12-31'); insert into dates values ('2000-01-01'); insert into dates values ('2000-12-31'); insert into dates values ('2001-01-01'); insert into dates values ('2001-12-31'); insert into dates values ('2002-01-01'); insert into dates values ('2002-12-31'); insert into dates values ('2003-01-01'); insert into dates values ('2003-12-31'); insert into dates values ('2004-01-01'); insert into dates values ('2004-12-31'); insert into dates values ('2005-01-01'); insert into dates values ('2005-01-02'); insert into dates values ('2005-01-03'); insert into dates values ('2005-01-04'); insert into dates values ('2005-01-05'); insert into dates values ('2005-01-06'); insert into dates values ('2005-01-07'); insert into dates values ('2005-01-08'); insert into dates values ('2005-01-09'); select date, to_char (date, 'Dy') as cday, isodow (date) as isod, yearweek (date) as yw_Clo, yearweek_Daniel (date) as yw_Dan, yearweek_Wolfgang (date) as yw_Wol from dates where yearweek (date) <> yearweek_Wolfgang (date) order by date; date | cday | isod | yw_clo | yw_dan | yw_wol ---------------------+------+------+--------+--------+-------- 1990-12-31 00:00:00 | Mon | 0 | 199101 | 199052 | 199001 1991-01-01 00:00:00 | Tue | 1 | 199101 | 199052 | 199001 1991-12-31 00:00:00 | Tue | 1 | 199201 | 199152 | 199101 1992-01-01 00:00:00 | Wed | 2 | 199201 | 199152 | 199101 1996-12-31 00:00:00 | Tue | 1 | 199701 | 199652 | 199601 1997-01-01 00:00:00 | Wed | 2 | 199701 | 199652 | 199601 1997-12-31 00:00:00 | Wed | 2 | 199801 | 199752 | 199701 1998-01-01 00:00:00 | Thu | 3 | 199801 | 199752 | 199701 2001-12-31 00:00:00 | Mon | 0 | 200201 | 200152 | 200101 2002-01-01 00:00:00 | Tue | 1 | 200201 | 200152 | 200101 2002-12-31 00:00:00 | Tue | 1 | 200301 | 200252 | 200201 2003-01-01 00:00:00 | Wed | 2 | 200301 | 200252 | 200201 2003-12-31 00:00:00 | Wed | 2 | 200401 | 200352 | 200301 2004-01-01 00:00:00 | Thu | 3 | 200401 | 200352 | 200301 (14 rows) Regards, Clodoaldo Pinto