2005/8/21, Jim C. Nasby [EMAIL PROTECTED]:
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
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
2005/8/21, Stephan Szabo [EMAIL PROTECTED]:
On Sat, 20 Aug 2005, Tom Lane wrote:
Clodoaldo Pinto [EMAIL PROTECTED] 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
On Sun, 21 Aug 2005, Clodoaldo Pinto wrote:
2005/8/21, Stephan Szabo [EMAIL PROTECTED]:
On Sat, 20 Aug 2005, Tom Lane wrote:
Clodoaldo Pinto [EMAIL PROTECTED] 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
Stephan Szabo [EMAIL PROTECTED] 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.
On Sun, 21 Aug 2005, Tom Lane wrote:
Stephan Szabo [EMAIL PROTECTED] 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
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
Stephan Szabo [EMAIL PROTECTED] 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
2005/8/21, Tom Lane [EMAIL PROTECTED]:
Stephan Szabo [EMAIL PROTECTED] 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
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');
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
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
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
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,
Clodoaldo Pinto [EMAIL PROTECTED] 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
On Sat, 20 Aug 2005, Tom Lane wrote:
Clodoaldo Pinto [EMAIL PROTECTED] 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
On Sat, Aug 20, 2005 at 08:49:27PM -0700, Stephan Szabo wrote:
On Sat, 20 Aug 2005, Tom Lane wrote:
Clodoaldo Pinto [EMAIL PROTECTED] 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.
17 matches
Mail list logo