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

Extend date part extraction and date formating functions to support more formats like "quarter", "iso year/week" #3831

monetdb-team opened this issue Nov 30, 2020 · 0 comments


Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2015-10-22 08:44:28 +0200
From: Sherzod Mutalov <>
To: Martin van Dinther <<martin.van.dinther>>
Version: -- development
CC: martin.van.dinther

Last updated: 2018-03-29 15:39:25 +0200

Comment 21377

Date: 2015-10-22 08:44:28 +0200
From: Sherzod Mutalov <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:39.0) Gecko/20100101 Firefox/39.0
Build Identifier:

MonetDB doesn't support some date part formatting like quarter or iso year/weak. Popular RDBMS includes that functionality (see additional information). For now, as solution we use:
((extract(month from current_timestamp) - 1) / 3 + 1)

Reproducible: Always

Steps to Reproduce:

  1. select extract(quarter from current_timestamp)
  2. select timestamp_to_str(current_timestamp, '%Q');

Actual Results:

  1. syntax error, unexpected IDENT in: "select extract(quarter"

  2. server crash

Expected Results:

quarter of a date (1-4)


  1. SQL Server: DATEPART(Q, )
  2. PostgreSQL: DATE_PART('quarter', )
  3. Oracle: TO_CHAR(, 'Q')

Comment 25562

Date: 2017-08-10 14:21:35 +0200
From: MonetDB Mercurial Repository <>

Changeset eb347d3c0321 made by Martin van Dinther in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=eb347d3c0321

Changeset description:

Addendum to changeset [https//devmonetdborg/hg/MonetDB?cmd=changeset;node=1523b559564f](;node=https//devmonetdborg/hg/MonetDB?cmd=changeset;node=1523b559564f) 
Added the words quarter and week to the non_reserved_word list to avoid upgrade problems for users which have objects named quarter or week.
This allows me to undo some changes made in pg_regress tests where column quarter or function calls to week or quarter were made.
Also extended test extract_quarter_week_from_date.Bug-3831.sql with tests of new scalar function: quarter(x).
Also updated ODBC driver mapping for scalar function quarter(), which can now map to quarter() directly instead of the workaround using month().
Also moved ChangeLog message to the prooer sql/ChangeLog as this is related only to sql part.

Comment 25563

Date: 2017-08-10 15:20:31 +0200
From: Martin van Dinther <<martin.van.dinther>>

Added support for SQL scalar functions:
quarter ( date_expr ),
quarter ( timestamp_expr ) and
quarter ( timestamptz_expr ).
and for extracting the quarter (number between 1 and 4) from
a date or a timestamp or a timestamp with timezone in SQL:
EXTRACT ( QUARTER FROM my_date_expr ).

Also added support for extracting the week (number between 1 and 53) from
a date or a timestamp or a timestamp with timezone in SQL:
EXTRACT ( WEEK FROM my_date_expr ).

The scalar functions week() and weekofyear() already existed.

Comment 26327

Date: 2018-03-29 15:39:25 +0200
From: @sjoerdmullender

The Mar2018 version has been released.

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

Successfully merging a pull request may close this issue.

None yet
1 participant