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.

Sign up for GitHub

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

Jump to bottom

SQL: add DATEPART function #46372

Closed
astefan opened this issue Sep 5, 2019 · 3 comments · Fixed by #47206
Closed

SQL: add DATEPART function #46372

astefan opened this issue Sep 5, 2019 · 3 comments · Fixed by #47206
Assignees
@matriv
Labels
:Analytics/SQL SQL querying >feature

Comments

@astefan
Copy link
Contributor

astefan commented Sep 5, 2019 •
edited
Loading

DATEPART function receives 2 or 3 parameters:

  • first parameter is a string which indicates which "part" of a DATE or DATETIME field is needed. Example: 'day', 'year', 'month' etc
  • the second parameter is the date/datetime field itself (or a literal)
  • the third parameter is optional and is either Sunday or Monday that indicates the start of the week for the respective time zone in which the date is considered to be in.

The returned value is of type INTEGER.
@elasticmachine
Copy link
Collaborator

Pinging @elastic/es-search

@astefan
Copy link
Contributor Author

astefan commented Sep 5, 2019

As an example for month part:
CONVERT(TRUNCATE(EXTRACT(MONTH FROM my_date_field),0), SQL_BIGINT)

@matriv matriv self-assigned this Sep 12, 2019
matriv added a commit to matriv/elasticsearch that referenced this issue Sep 27, 2019
@matriv
SQL: Implement DATE_PART function
ec28f65 
DATE_PART(<datetime unit>, <date/datetime>) is a function that allows
the user to extract the specified unit from a date/datetime field
similar to the EXTRACT (<datetime unit> FROM <date/datetime>) but
with different names and aliases for the units and it also provides more
options like `DATE_PART('tzoffset', datetimeField)`.

Implemented following the SQL server's spec: https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017
with the difference that the <datetime unit> argument is either a
literal single quoted string or gets a value from a table field, whereas
in SQL server keywords are used (unquoted identifiers) and it's not
possible to use a value coming for a table column.

Closes: elastic#46372
@matriv matriv mentioned this issue Sep 27, 2019
Merged
matriv added a commit that referenced this issue Oct 1, 2019
@matriv
SQL: Implement DATE_PART function (#47206)
ead743d 
]DATE_PART(<datetime unit>, <date/datetime>) is a function that allows
the user to extract the specified unit from a date/datetime field
similar to the EXTRACT (<datetime unit> FROM <date/datetime>) but
with different names and aliases for the units and it also provides more
options like `DATE_PART('tzoffset', datetimeField)`.

Implemented following the SQL server's spec: https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017
with the difference that the <datetime unit> argument is either a
literal single quoted string or gets a value from a table field, whereas
in SQL server keywords are used (unquoted identifiers) and it's not
possible to use a value coming for a table column.

Closes: #46372
matriv added a commit that referenced this issue Oct 1, 2019
@matriv
SQL: Implement DATE_PART function (#47206)
f792dbf 
DATE_PART(<datetime unit>, <date/datetime>) is a function that allows
the user to extract the specified unit from a date/datetime field
similar to the EXTRACT (<datetime unit> FROM <date/datetime>) but
with different names and aliases for the units and it also provides more
options like `DATE_PART('tzoffset', datetimeField)`.

Implemented following the SQL server's spec: https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017
with the difference that the <datetime unit> argument is either a
literal single quoted string or gets a value from a table field, whereas
in SQL server keywords are used (unquoted identifiers) and it's not
possible to use a value coming for a table column.

Closes: #46372
(cherry picked from commit ead743d)
@matriv
Copy link
Contributor

matriv commented Oct 1, 2019

master : ead743d
7.x : f792dbf

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@matriv matriv

Labels
:Analytics/SQL SQL querying >feature
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

SQL: Implement DATE_PART function matriv/elasticsearch
3 participants
@astefan @matriv @elasticmachine