Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

EXTRACT(WEEK FROM DATE) [CORE663] #1029

Closed
firebird-automations opened this issue Jun 22, 2005 · 17 comments
Closed

EXTRACT(WEEK FROM DATE) [CORE663] #1029

firebird-automations opened this issue Jun 22, 2005 · 17 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: madaleno (madaleno)

Attachments:
fbudf.zip

Votes: 1

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

Commits: b5a23c1

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: madaleno (madaleno)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Aki Nieminen (whome)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: lacak (lacak)

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

@firebird-automations
Copy link
Collaborator Author

Commented by: PAscal (legrand_legrand_63)

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/

@firebird-automations
Copy link
Collaborator Author

Commented by: madaleno (madaleno)

Is there any news about this topic?
Will this be implemented in 2.1?

Thanks,

Luis

@firebird-automations
Copy link
Collaborator Author

Commented by: lacak (lacak)

implementation of weekofyear UDF

@firebird-automations
Copy link
Collaborator Author

Modified by: lacak (lacak)

Attachment: fbudf.zip [ 10510 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.1 Beta 2 [ 10190 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Jeroen Sparenberg (jsparenberg)

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 10687 ] => Firebird [ 15067 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

Q/A tested

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants