Thread: year and weeknumbers (proposal included)
hi, last year (2002) the date december 31st is in week 1 of 2003. it is not possible to get this out of a date-column using EXTRACT or to_char. to_char( '2002-12-31', 'WW' ) returns 1, and to_char( '2002-12-31', 'YYYY' ) returns 2002 (as expected)! the format for retrieving the year with respect to week-numbering could be something like IY, IYY, IYYY, IYYYY. to_char( '2002-12-31', 'IYYYY' ) returns 2003. regards, Joost Helberg -- Joost Helberg Technisch Directeur Snow BV http://snow.nl Tel 0418-653333 Fax 0418-653666 Voorzitter VOSN http://www.vosn.nl Tel 0418-653336 Fax 0418-653666 GPG PblKey fprnt= 738C 20AC A545 02AE 6F5D 5A9F D724 EB4B 2B10 150B
On Mon, Jan 27, 2003 at 21:53:27 +0100, joost@snow.nl wrote: > hi, > > last year (2002) the date december 31st is in week 1 of 2003. > > it is not possible to get this out of a date-column using EXTRACT or > to_char. > to_char( '2002-12-31', 'WW' ) returns 1, and > to_char( '2002-12-31', 'YYYY' ) returns 2002 (as expected)! It is POSSIBLE to do. You can use the fact that the Thursday of a week is always in the same year as the week number refers to, to get the correct year. It isn't pretty though and it probably is a good idea to have a simple way to get the year corresponding to an ISO week.
Hi, Counting weeks is a very special things because it is not the same in all countries. As far as I have been told Polish people count weeks not the way Austrians do it. On my calendar it says that the first week of the year starts on January 1st. As far as I remember other countries such as Poland start to when the first full week starts. Maybe we should verify and document this somewhere. Regards, Hans joost@snow.nl wrote: >hi, > >last year (2002) the date december 31st is in week 1 of 2003. > >it is not possible to get this out of a date-column using EXTRACT or >to_char. > to_char( '2002-12-31', 'WW' ) returns 1, and > to_char( '2002-12-31', 'YYYY' ) returns 2002 (as expected)! > >the format for retrieving the year with respect to week-numbering >could be something like IY, IYY, IYYY, IYYYY. > > to_char( '2002-12-31', 'IYYYY' ) returns 2003. > >regards, > >Joost Helberg > > -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at <http://cluster.postgresql.at>, www.cybertec.at <http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>
Hans, Just like to_char( date, 'IW' ) refers to ISO Week, I'd like to see a IYYYY for ISO-year, and I wondered whether anyone else was interested, or whether I was missing then point. (IYYYY matches %G in Unix strftime). I know `week' has different meanings in various cultures, there's even weeks of 6 days (well, long ago)! But there's only one ISO definition for week. regards, Joost >>>>> "Hans" writes: > Date: Thu, 30 Jan 2003 15:15:14 +0100 > From: Hans-Jürgen Schönig <postgres@cybertec.at> >To: joost@snow.nl, pgsql-bugs@postgresql.org > Subject: Re: [BUGS] year and weeknumbers (proposal included) > Hi, > Counting weeks is a very special things because it is not the same in > all countries. As far as I have been toldPolish people count weeks not > the way Austrians do it. > On my calendar it says that the first week of the year starts on January > 1st. As far as I remember other countriessuch as Poland start to when > the first full week starts. > Maybe we should verify and document this somewhere. > Regards, > Hans > joost@snow.nl wrote: >> hi, >> >> last year (2002) the date december 31st is in week 1 of 2003. >> >> it is not possible to get thisout of a date-column using EXTRACT or >> to_char. >> to_char( '2002-12-31', 'WW' ) returns 1, and >> to_char('2002-12-31', 'YYYY' ) returns 2002 (as expected)! >> >> the format for retrieving the year with respect toweek-numbering >> could be something like IY, IYY, IYYY, IYYYY. >> >> to_char( '2002-12-31', 'IYYYY' ) returns2003. >> >> regards, >> >> Joost Helberg >> >> > -- > *Cybertec Geschwinde u Schoenig* > Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria > Tel: +43/1/913 68 09;+43/664/233 90 75 > www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at > <http://cluster.postgresql.at>,www.cybertec.at > <http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at> > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at oncewith the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Joost Helberg Technisch Directeur Snow BV http://snow.nl Tel 0418-653333 Fax 0418-653666 Voorzitter VOSN http://www.vosn.nl Tel 0418-653336 Fax 0418-653666 GPG PblKey fprnt= 738C 20AC A545 02AE 6F5D 5A9F D724 EB4B 2B10 150B