Is it possible to have support for WEEK numbers in FB
built-in functions?

It would be nice to improve the EXTRACT() function to
include support for WEEK. like EXTRACT(WEEK FROM DATE)

This is very useful for financial data analysis.

I also can't find support for this in any UDF library.

Regards,

Luis Madaleno

Description

SFID: 1225686#
Submitted By: madaleno
Hi,
Is it possible to have support for WEEK numbers in FB
built-in functions?
It would be nice to improve the EXTRACT() function to
include support for WEEK. like EXTRACT(WEEK FROM DATE)
This is very useful for financial data analysis.
I also can't find support for this in any UDF library.
Regards,
Luis Madaleno

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.

Alice F. Bird added a comment - 14/Jun/06 09:41 AM Date: 2005-07-29 13:55
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.

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.

Dmitry Yemanov added a comment - 17/Jun/06 12:58 PM 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.

madaleno added a comment - 19/Jun/06 04:12 AM 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.

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).

Dmitry Yemanov added a comment - 30/Jul/07 04:17 AM 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).

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.

Jeroen Sparenberg added a comment - 03/Aug/07 05:50 AM 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.