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

Add builtin functions FIRST_DAY and LAST_DAY [CORE5620] #5886

Closed
firebird-issue-importer opened this issue Sep 21, 2017 · 11 comments
Closed

Add builtin functions FIRST_DAY and LAST_DAY [CORE5620] #5886

firebird-issue-importer opened this issue Sep 21, 2017 · 11 comments

Comments

@firebird-issue-importer

Submitted by: @asfernandes

Votes: 5

Firebird lacks very commonly used functions to manage dates. They can be added as UDF or PSQL functions, but since they're very common and need to be registered in every database it would be good to have them as bultin functions.

I suggest these two functions:

FIRST_DAY( OF { MONTH | YEAR | WEEK } FROM <date or timestamp> )
LAST_DAY( OF { MONTH | YEAR | WEEK } FROM <date or timestamp> )

FIRST_DAY(of month from date '2017-09-15') -- 2017-09-01
FIRST_DAY(of year from date '2017-09-15') -- 2017-01-01
FIRST_DAY(of week from timestamp '2017-11-01 12:13:14.5678') -- 2017-10-29 12:13:14.5678

LAST_DAY(of month from date '2017-09-15') -- 2017-09-30
LAST_DAY(of year from date '2017-09-15') -- 2017-12-31
LAST_DAY(of week from timestamp '2017-11-01 12:13:14.5678') -- 2017-11-04 12:13:14.5678

First day of week is Sunday and last is Saturday.

Commits: a2556be

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 21, 2017

Commented by: Omacht András (aomacht)

Don't forget about leap years.

LAST_DAY('2000-02-05') -- 2000.02.29
LAST_DAY('2001-02-05') -- 2001.02.28
LAST_DAY('2004-02-05') -- 2004.02.29
LAST_DAY('2100-02-05') -- 2100.02.28

If we already need it, I suggest to publish it as function:

IS_LEAP_YEAR(integer)

IS_LEAP_YEAR(2000) -- 1 or true
IS_LEAP_YEAR(2001) -- 0 or false
IS_LEAP_YEAR(2004) -- 1 or true
IS_LEAP_YEAR(2100) -- 0 or false

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 21, 2017

Commented by: @AlexPeshkoff

OF WEEK may be also useful sometimes

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 21, 2017

Commented by: @mrotteveel

Of WEEK might be problematic:
- what is the last day of the week (is it Saturday like in the US, or Sunday like in (a lot of?) Europe?)
- do we always use the same (eg Sunday, which I believe - but not sure - is specified in ISO-8601), or do we use the locale of the server, etc ,etc?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 21, 2017

Commented by: @asfernandes

AFAIK there is already EXTRACT(WEEK ...) so rules could be the same.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 21, 2017

Commented by: @asfernandes

And there is this warning in our docs:

----
Be careful when combining WEEK and YEAR results. For instance, 30 December 2008 lies in week 1 of 2009, so "extract (week from date '30 Dec 2008')" returns 1. However, extracting YEAR always gives the calendar year, which is 2008. In this case, WEEK and YEAR are at odds with each other. The same happens when the first days of January belong to the last week of the previous year.
----

But I see no other way if we implement LAST_DAY / FIRST_DAY OF WEEK.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 22, 2017

Commented by: @livius2

Good functions - but do you really think that default should be e.g. month?
Better will be always specify like is in Extract and other functions.
Functions should be fully descriptive when are used.

and if you consider already to add functions please look here :)
CORE5623 date
CORE4029 bit

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 31, 2017

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 1, 2017

Modified by: @asfernandes

description: Firebird lacks very commonly used functions to manage dates. They can be added as UDF or PSQL functions, but since they're very common and need to be registered in every database it would be good to have them as bultin functions.

I suggest these two functions:

LAST_DAY( <date or timestamp> [ OF { MONTH | YEAR } )
FIRST_DAY( <date or timestamp> [ OF { MONTH | YEAR } )

If "OF ..." clause is omitted, it assumes OF MONTH.

LAST_DAY(date '2017-09-15') -- 2017-09-30
LAST_DAY(date '2017-09-15' of month) -- 2017-09-30
LAST_DAY(date '2017-09-15' of year) -- 2017-12-31

FIRST_DAY(date '2017-09-15') -- 2017-09-01
FIRST_DAY(date '2017-09-15' of month) -- 2017-09-01
FIRST_DAY(date '2017-09-15' of year) -- 2017-01-01

=>

Firebird lacks very commonly used functions to manage dates. They can be added as UDF or PSQL functions, but since they're very common and need to be registered in every database it would be good to have them as bultin functions.

I suggest these two functions:

FIRST_DAY( OF { MONTH | YEAR | WEEK } FROM <date or timestamp> )
LAST_DAY( OF { MONTH | YEAR | WEEK } FROM <date or timestamp> )

FIRST_DAY(of month from date '2017-09-15') -- 2017-09-01
FIRST_DAY(of year from date '2017-09-15') -- 2017-01-01
FIRST_DAY(of week from timestamp '2017-11-01 12:13:14.5678') -- 2017-10-29 12:13:14.5678

LAST_DAY(of month from date '2017-09-15') -- 2017-09-30
LAST_DAY(of year from date '2017-09-15') -- 2017-12-31
LAST_DAY(of week from timestamp '2017-11-01 12:13:14.5678') -- 2017-11-04 12:13:14.5678

First day of week is Sunday and last is Saturday.

summary: Add builtin functions LAST_DAY and FIRST_DAY => Add builtin functions FIRST_DAY and LAST_DAY

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 1, 2017

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 9, 2017

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 9, 2017

Modified by: @pavel-zotov

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
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants