Re: [HACKERS] Postgresql likes Tuesday...
On Mon, Sep 30, 2002 at 06:31:15PM -0400, Tom Lane wrote:
The middle part of that boils down (as of today) to
regression=# select to_date('402002', 'WW');
to_date
2002-10-01
(1 row)
and Oct 1 (tomorrow) is Tuesday. As to why it picks that day to
represent Week 40 of 2002, it's probably related to the fact that Week 1
of 2002 is converted to
regression=# select to_date('012002', 'WW');
to_date
2002-01-01
(1 row)
which was a Tuesday.
Offhand this seems kinda inconsistent to me --- I'd expect
regression=# select extract(week from date '2002-09-30');
date_part
---
40
(1 row)
to produce 39, not 40, on the grounds that the first day of Week 40
is tomorrow not today. Alternatively, if today is the first day of
Week 40 (as EXTRACT(week) seems to think), then ISTM that the to_date
expression should produce today not tomorrow.
I notice that 2001-12-31 is considered part of the first week of 2002,
which is also pretty surprising:
regression=# select extract(week from date '2001-12-31');
date_part
---
1
(1 row)
Anyone able to check this stuff on Oracle? What exactly are the
boundary points for EXTRACT(week), and does to_date() agree?
Please, read docs -- to_() functions know two versions of number of
week
IW = iso-week
WW = oracle week
test=# select to_date('402002', 'WW');
to_date
2002-10-01
(1 row)
test=# select to_date('402002', 'IW');
to_date
2002-09-30
(1 row)
test=# select to_date('012002', 'WW');
to_date
2002-01-01
(1 row)
test=# select to_date('012002', 'IW');
to_date
2001-12-31
(1 row)
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Postgresql likes Tuesday...
On Mon, Sep 30, 2002 at 05:37:47PM -0400, Rod Taylor wrote:
select to_char(
to_date(
CAST(extract(week from CURRENT_TIMESTAMP) as text)
|| CAST(extract(year from CURRENT_TIMESTAMP) as text)
, 'WW')
, 'FMDay, D');
to_char
Tuesday, 3
(1 row)
The PostgreSQL not loves Thuesday, but WW for year 2002 loves it. Why?
Because 'WW' = (day_of_year - 1) / 7 + 1, other words this year
start on Thuesday (see 01-JAN-2002) and WW start weeks each 7 days
after this first day of year.
If you need human week you must use IW (iso-week) that start every
Monday.
I know there're countries where week start on Sunday, but it's not supported
-- the problem is with 'D' it returns day-of-week for Sunday-based-week.
Your example (I use to_xxx () only, it's more readable):
If you need correct for Sunday-based-week:
select to_char( to_date(to_char(now(), 'IW'), 'IW')-'1d'::interval, 'FMDay,
D');
to_char
---
Sunday, 1
If you need Monday-based-week (ISO week):
test=# select to_char( to_date(to_char(now(), 'IW'), 'IW'), 'FMDay, D');
to_char
---
Monday, 2
'2' is problem -- maybe add to to_xxx() functions 'ID' as day-of-isoweek.
It's really small change I think we can do it for 7.3 too.
What think about it our Toms?
In the Oracle it's same (means WW vs. IW vs. D)
SVRMGR select to_char(to_date('30-SEP-02'), 'WW IW Day D') from dual;
TO_CHAR(TO_DATE('
-
39 40 Monday2
test=# select to_char('30-SEP-02'::date, 'WW IW Day D');
to_char
---
39 40 Monday2
SVRMGR select to_char(to_date('29-SEP-02'), 'WW IW Day D') from dual;
TO_CHAR(TO_DATE('
-
39 39 Sunday1
test=# select to_char('29-SEP-02'::date, 'WW IW Day D');
to_char
---
39 39 Sunday1
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Postgresql likes Tuesday...
Karel Zak [EMAIL PROTECTED] writes: What think about it our Toms? ... In the Oracle it's same (means WW vs. IW vs. D) If it works the same as Oracle then I'm happy with it; that's what it's supposed to do. The real point here seems to be that EXTRACT(week) corresponds to to_date's IW conversion, not WW conversion. This is indeed implied by the docs, but it's not stated plainly (there's just a reference to ISO in each of the relevant pages). Perhaps we need more documentation, or a different layout that would offer a place to put notes like this one. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Postgresql likes Tuesday...
If it's of any use the following link gives some info on different schemes and details on an ISO week numbering standard. http://www.merlyn.demon.co.uk/weekinfo.htm#WkNo Best Regards, Tim Knowles ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Postgresql likes Tuesday...
select to_char( to_date( CAST(extract(week from CURRENT_TIMESTAMP) as text) || CAST(extract(year from CURRENT_TIMESTAMP) as text) , 'WW') , 'FMDay, D'); to_char Tuesday, 3 (1 row) Not that it matters for me at the moment (I care that it's in the week of..), but why does it pick Tuesday? -- Rod Taylor ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Postgresql likes Tuesday...
Rod Taylor [EMAIL PROTECTED] writes:
select to_char(
to_date(
CAST(extract(week from CURRENT_TIMESTAMP) as text)
|| CAST(extract(year from CURRENT_TIMESTAMP) as text)
, 'WW')
, 'FMDay, D');
to_char
Tuesday, 3
(1 row)
Not that it matters for me at the moment (I care that it's in the week
of..), but why does it pick Tuesday?
The middle part of that boils down (as of today) to
regression=# select to_date('402002', 'WW');
to_date
2002-10-01
(1 row)
and Oct 1 (tomorrow) is Tuesday. As to why it picks that day to
represent Week 40 of 2002, it's probably related to the fact that Week 1
of 2002 is converted to
regression=# select to_date('012002', 'WW');
to_date
2002-01-01
(1 row)
which was a Tuesday.
Offhand this seems kinda inconsistent to me --- I'd expect
regression=# select extract(week from date '2002-09-30');
date_part
---
40
(1 row)
to produce 39, not 40, on the grounds that the first day of Week 40
is tomorrow not today. Alternatively, if today is the first day of
Week 40 (as EXTRACT(week) seems to think), then ISTM that the to_date
expression should produce today not tomorrow.
I notice that 2001-12-31 is considered part of the first week of 2002,
which is also pretty surprising:
regression=# select extract(week from date '2001-12-31');
date_part
---
1
(1 row)
Anyone able to check this stuff on Oracle? What exactly are the
boundary points for EXTRACT(week), and does to_date() agree?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgresql likes Tuesday...
On Tue, 2002-10-01 at 03:31, Tom Lane wrote: Offhand this seems kinda inconsistent to me --- I'd expect regression=# select extract(week from date '2002-09-30'); date_part --- 40 (1 row) to produce 39, not 40, on the grounds that the first day of Week 40 is tomorrow not today. Alternatively, if today is the first day of Week 40 (as EXTRACT(week) seems to think), then ISTM that the to_date expression should produce today not tomorrow. I notice that 2001-12-31 is considered part of the first week of 2002, which is also pretty surprising: There are at least 3 different ways to start week numbering: 1. from first week with any days in current year 2. from first full week in current year 3. from first week with thursday in current year perhaps more... I suspect it depends on locale which should be used. --- Hannu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Postgresql likes Tuesday...
Hannu Krosing [EMAIL PROTECTED] writes: On Tue, 2002-10-01 at 03:31, Tom Lane wrote: I notice that 2001-12-31 is considered part of the first week of 2002, which is also pretty surprising: There are at least 3 different ways to start week numbering: ... I suspect it depends on locale which should be used. Perhaps. But I think there are two distinct issues here. One is whether EXTRACT(week) is assigning reasonable week numbers to dates; this depends on your convention for which day is the first of a week as well as your convention for the first week of a year (both possibly should depend on locale as Hannu suggests). The other issue is what to_date(...,'WW') should do to produce a date representing a week number. Shouldn't it always produce the first date of that week? If not, what other conventions make sense? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Postgresql likes Tuesday...
On Tue, 2002-10-01 at 03:49, Tom Lane wrote:
Hannu Krosing [EMAIL PROTECTED] writes:
On Tue, 2002-10-01 at 03:31, Tom Lane wrote:
I notice that 2001-12-31 is considered part of the first week of 2002,
which is also pretty surprising:
There are at least 3 different ways to start week numbering:
...
I suspect it depends on locale which should be used.
Perhaps. But I think there are two distinct issues here. One is
whether EXTRACT(week) is assigning reasonable week numbers to dates;
this depends on your convention for which day is the first of a week
as well as your convention for the first week of a year (both possibly
should depend on locale as Hannu suggests). The other issue is what
to_date(...,'WW') should do to produce a date representing a week
number. Shouldn't it always produce the first date of that week?
Producing middle-of-the week date is least likely to get a date in last
year.
Also should
select to_timestamp('01102002','DDMM');
also produce midday (12:00) for time, instead of current 00:00 ?
-
Hannu
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Postgresql likes Tuesday...
On Mon, Sep 30, 2002 at 06:49:34PM -0400, Tom Lane wrote: | The other issue is what | to_date(...,'WW') should do to produce a date representing a week | number. Shouldn't it always produce the first date of that week? | If not, what other conventions make sense? IMHO, it should choose the Week Ending date. This is usually what all of the companies that I've worked with want to see for the day column. For example, the defect^H^H^H^H^H^H quality reports at Ford Motor in 1993 used a Predo of part by defect by week-ending. Where week ending date was the Sunday following the work week (monday-sunday). In various project data in companies that I've worked with before and after 1993 I've yet to see a weekly report that didn't give the week ending... alhtough some did use Friday or Saturday for the week ending. One hickup with this choice is that you'd probably want the time portion to be 23:59:59.999 so that it includes everything up to the end of the day. Hmm. Clark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
