|
Date: 2005-07-24 17:10
Sender: dimitr Logged In: YES user_id=61270 EXTRACT is a standard function and SQL99 doesn't declare a WEEK option there. I'd prefer to add an appropriate UDF (into ib_udf) instead. I'm afraid we have a problem here.AFAIK, there are different algorithms of counting week numbers in various countries, so our solution will never satisfy everybody. The only related standard I'm aware of is ISO-8601, but I doubt it's practically useful, as it may count Jan-01 as either week 1 of the new year or week 52 of the previous year, depending on week days alignment.
So my position is that everybody interested should implement the week number UDF they're comfortable with. Provided with enough pressure, I could agree to add either the absolute week number counting or the ISO implementation to the standard UDF library, but I'm still against extention of EXTRACT. My opinion is that FB should implement the ISO standard like other DBMS have.
So EXTRACT should be extended to support this. If a standard exists, what's the problem to do this? People not liking the ISO results should implement a UDF in that case. I'm no talking about implementing something weird here. I'm talking of a very useful function for finantial use. Finantial reports are mostly week based. And since today we live in a global society, these kind of info have to follow the standards. I can't produce a report with country specific week numbers to be available in the www, for example. Globalization is here so using standards will help many users and will be very useful. Believe me. If going the UDF way, the should also be UDFs for YEAR and MONTH to keep things uniform. It would be very confusing for users having to write diferent code for similar things. I have seen systems where both ISO compliant and "US" compliant week functions are implemented:
ExtractWeek(date) ISO.ExtractWeek(date) I always prefer embedded functions instead of separate UDF installations to maintain consistent look&feel across the community. I would like vote for this function too. :-)
I add some comparasion to other DBMS : ORACLE: TO_CHAR(d,'IW') MS SQL : DATEPART(week,datum) select datepart(week, '2005-01-01'); >1 select datepart(week, '2006-01-01'); >1 select datepart(week, '2006-01-02'); >1 select datepart(week, '2007-01-01'); >1 MySQL : WEEK(date[,mode]), WEEKOFYEAR()=WEEK(date,3) -> ISO8601 compliant SELECT week('2005-01-01',3), weekofyear('2005-01-01'); >53 SELECT week('2006-01-01',3), weekofyear('2006-01-01'); >52 SELECT week('2006-01-02',3), weekofyear('2006-01-02'); >1 SELECT week('2007-01-01',3), weekofyear('2007-01-01'); >1 PostgreSQL: EXTRACT(WEEK FROM datum) -> ISO8601 compliant select extract(week from date'2005-01-01'); >53 select extract(week from date'2006-01-01'); >52 select extract(week from date'2006-01-02'); >1 select extract(week from date'2007-01-01'); >1 So I guess, that FB can follow PostgreSQL and MySQL a) add external function week() or weekofyear() to fbudf or b) extend extract() same way as PostgreSQL And UDF emulating Oracle TO_CHAR with formats YYYY,YY,MON,MM,DDD,IW,DAY,D,HH24,HH,MI,SS is already available in fn_to_chard at http://perso.orange.fr/Udf4ORA/
It seems that the majority wants to see this function returning an ISO-based week number and implemented as the built-in function. Adriano, if you don't have objections yourself, please take care of it for v2.1 Beta 2.
And, BTW, if we're about extending EXTRACT, the Yaffil's MILLISECONDS clause could also be added at the same time (PostgreSQL also supports this in EXTRACT). There is an alternative for calculating the weeknumbers. But it only works for within one year. The sql select line looks like this:
"SELECT CAST(((DATEFIELD - cast(:PARAM_STARTOFYEAR as integer) - cast('1900-01-01' as date)) / 7) + 0.5 as INTEGER) as weeknumber" Within the above line, the parameter PARAM_STARTOFYEAR needs to be calculated outside of your query. For 2006 it should be 2006-01-02 according to our calender. The best part of this setup is that no standard is required. Now I will try to explain the mySQL line as the weeknumber is calculated: - Substracting the date field with the PARAM_STARTOFYEAR will result in the number of days in the current year in date format. - Substracting with cast('1900-01-01' as date) will convert the #DaysInCurrentYears from date format to integer/timestamp format. (Don't know why though :) - Dividing by 7, because of the number of days in a week, will result in a possible weeknumber value between 0 and 52 (#Days(0) gives week 0 and #Days(365) gives week 52. - Because the first week should be number 1, the weeknumber needs to be increased. Since the weeknumber is actually a float instead of an integer, the weeknumber is 'only' increased by 0.5 to 'abuse' the round functionality of casting the weeknumber to integer. With kind regards, Jeroen Sparenberg. |
|||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
New Feature
Closed
Major
EXTRACT(WEEK FROM DATE)
Sender: madaleno
Logged In: YES
user_id=1301234
Well I guess FireBird already has some SQL features that are
not complaint with the SQL standard and this one would be
more an extension then something out of the standard.
Any way a UDF would also be nice, but for the user point of
view, extending the EXTRACT() method would be easier to
understand, unless wwe also have a YEAR and MONTH UDF function.