Issue Details (XML | Word | Printable)

Key: CORE-5620
Type: New Feature New Feature
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Adriano dos Santos Fernandes
Votes: 5
Watchers: 4
Operations

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

Add builtin functions FIRST_DAY and LAST_DAY

Created: 21/Sep/17 11:01 AM   Updated: 09/Nov/17 05:59 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 4.0 Beta 1

QA Status: Done successfully


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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Omacht András added a comment - 21/Sep/17 11:36 AM
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

Alexander Peshkov added a comment - 21/Sep/17 12:35 PM
OF WEEK may be also useful sometimes

Mark Rotteveel added a comment - 21/Sep/17 01:56 PM
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?

Adriano dos Santos Fernandes added a comment - 21/Sep/17 02:23 PM
AFAIK there is already EXTRACT(WEEK ...) so rules could be the same.

Adriano dos Santos Fernandes added a comment - 21/Sep/17 02:25 PM
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.

Karol Bieniaszewski added a comment - 22/Sep/17 06:16 AM
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 :)
CORE-5623 date
CORE-4029 bit