drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Serge Harnyk (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DRILL-4864) Add ANSI format for date/time functions
Date Wed, 31 Aug 2016 10:29:20 GMT

     [ https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Serge Harnyk updated DRILL-4864:
--------------------------------
    Description: 
The TO_DATE() is exposing the Joda string formatting conventions into the SQL layer. This
is not following SQL conventions used by ANSI and many other database engines on the market.

Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI datetime format
and returns string that represents equal Joda format.
Add new session option "drill.exec.fn.to_date_format" that can be one of two values - "JODA"(default)
and "ANSI".
If option is set to "JODA" queries with to_date() function would work in usual way.
If option is set to "ANSI" second argument would be wrapped with ansi_to_joda() function,
that allows user to use ANSI datetime format
Wrapping is used in to_date(), to_time() and to_timestamp() functions.
Table of joda and ansi patterns which may be replaced
||	Pattern name	||	Ansi format	||	JodaTime format	||
|	FULL NAME OF DAY	|	day	|	EEEE	|
|	DAY OF YEAR	|	ddd	|	D	|
|	DAY OF MONTH	|	dd	|	d	|
|	DAY OF WEEK	|	d	|	e	|
|	NAME OF MONTH	|	month	|	MMMM	|
|	ABR NAME OF MONTH	|	mon	|	MMM	|
|	FULL ERA NAME	|	ee	|	G	|
|	NAME OF DAY	|	dy	|	E	|
|	TIME ZONE	|	tz	|	TZ	|
|	HOUR 12 	|	hh	|	h	|
|	HOUR 12 	|	hh12	|	h	|
|	HOUR 24	|	hh24	|	H	|
|	MINUTE OF HOUR	|	mi	|	m	|
|	SECOND OF MINUTE	|	ss	|	s	|
|	MILLISECOND OF MINUTE	|	ms	|	S	|
|	WEEK OF YEAR	|	ww	|	w	|
|	MONTH	|	mm	|	MM	|
|	HALFDAY AM	|	am	|	aa	|
|	HALFDAY PM	|	pm	|	aa	|
|	ref.	|	https://www.postgresql.org/docs/8.2/static/functions-formatting.html	|	http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
|


  was:
The TO_DATE() is exposing the Joda string formatting conventions into the SQL layer. This
is not following SQL conventions used by ANSI and many other database engines on the market.

Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI datetime format
and returns string that represents equal Joda format.
Add new session option "drill.exec.fn.to_date_format" that can be one of two values - "JODA"(default)
and "ANSI".
If option is set to "JODA" queries with to_date() function would work in usual way.
If option is set to "ANSI" second argument would be wrapped with ansi_to_joda() function,
that allows user to use ANSI datetime format
Wrapping is used in to_date(), to_time() and to_timestamp() functions.
||	Pattern name	||	Ansi format	||	JodaTime format	||
|	FULL NAME OF DAY	|	day	|	EEEE	|
|	DAY OF YEAR	|	ddd	|	D	|
|	DAY OF MONTH	|	dd	|	d	|
|	DAY OF WEEK	|	d	|	e	|
|	NAME OF MONTH	|	month	|	MMMM	|
|	ABR NAME OF MONTH	|	mon	|	MMM	|
|	FULL ERA NAME	|	ee	|	G	|
|	NAME OF DAY	|	dy	|	E	|
|	TIME ZONE	|	tz	|	TZ	|
|	HOUR 12 	|	hh	|	h	|
|	HOUR 12 	|	hh12	|	h	|
|	HOUR 24	|	hh24	|	H	|
|	MINUTE OF HOUR	|	mi	|	m	|
|	SECOND OF MINUTE	|	ss	|	s	|
|	MILLISECOND OF MINUTE	|	ms	|	S	|
|	WEEK OF YEAR	|	ww	|	w	|
|	MONTH	|	mm	|	MM	|
|	HALFDAY AM	|	am	|	aa	|
|	HALFDAY PM	|	pm	|	aa	|
|	ref.	|	https://www.postgresql.org/docs/8.2/static/functions-formatting.html	|	http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
|



> Add ANSI format for date/time functions
> ---------------------------------------
>
>                 Key: DRILL-4864
>                 URL: https://issues.apache.org/jira/browse/DRILL-4864
>             Project: Apache Drill
>          Issue Type: Improvement
>    Affects Versions: 1.8.0
>            Reporter: Serge Harnyk
>            Assignee: Serge Harnyk
>             Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL layer.
This is not following SQL conventions used by ANSI and many other database engines on the
market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI datetime format
and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two values -
"JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in usual way.
> If option is set to "ANSI" second argument would be wrapped with ansi_to_joda() function,
that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||	Pattern name	||	Ansi format	||	JodaTime format	||
> |	FULL NAME OF DAY	|	day	|	EEEE	|
> |	DAY OF YEAR	|	ddd	|	D	|
> |	DAY OF MONTH	|	dd	|	d	|
> |	DAY OF WEEK	|	d	|	e	|
> |	NAME OF MONTH	|	month	|	MMMM	|
> |	ABR NAME OF MONTH	|	mon	|	MMM	|
> |	FULL ERA NAME	|	ee	|	G	|
> |	NAME OF DAY	|	dy	|	E	|
> |	TIME ZONE	|	tz	|	TZ	|
> |	HOUR 12 	|	hh	|	h	|
> |	HOUR 12 	|	hh12	|	h	|
> |	HOUR 24	|	hh24	|	H	|
> |	MINUTE OF HOUR	|	mi	|	m	|
> |	SECOND OF MINUTE	|	ss	|	s	|
> |	MILLISECOND OF MINUTE	|	ms	|	S	|
> |	WEEK OF YEAR	|	ww	|	w	|
> |	MONTH	|	mm	|	MM	|
> |	HALFDAY AM	|	am	|	aa	|
> |	HALFDAY PM	|	pm	|	aa	|
> |	ref.	|	https://www.postgresql.org/docs/8.2/static/functions-formatting.html	|	http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message