Issue Details (XML | Word | Printable)

Key: CORE-663
Type: New Feature New Feature
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: madaleno
Votes: 1
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

EXTRACT(WEEK FROM DATE)

Created: 22/Jun/05 12:00 AM   Updated: 29/May/08 05:05 PM
Component/s: UDF
Affects Version/s: None
Fix Version/s: 2.1 Beta 2

Time Tracking:
Not Specified

File Attachments: 1. Zip Archive fbudf.zip (9 kB)


SF_ID: 1225686


 Description  « Hide
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

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
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.

Alice F. Bird added a comment - 14/Jun/06 09:41 AM
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.

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.

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.

Aki Nieminen added a comment - 13/Oct/06 03:30 PM
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.

lacak added a comment - 01/Dec/06 01:54 AM
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

PAscal added a comment - 06/Dec/06 01:42 PM
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/

madaleno added a comment - 25/Jan/07 05:31 PM
Is there any news about this topic?
Will this be implemented in 2.1?

Thanks,

Luis

lacak added a comment - 24/Jul/07 07:28 AM
implementation of weekofyear UDF

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

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.

Philippe Makowski added a comment - 29/Apr/08 11:27 AM
Q/A tested