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-automations opened this issue Sep 21, 2017 · 11 comments
Closed

Add builtin functions FIRST_DAY and LAST_DAY [CORE5620] #5886

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

Comments

@firebird-automations
Copy link
Collaborator

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

OF WEEK may be also useful sometimes

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

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
Development

No branches or pull requests

2 participants