Skip to content

Commit 72be0b5

Browse files
matrivmatriv
authored
SQL: Implement DATETIME_FORMAT function for date/time formatting (#54832)
Implement DATETIME_FORMAT(<date/datetime/time>, ) function which allows for formatting a timestamp to the specified format. The patterns allowed as those of java.time.format.DateTimeFormatter. Related to #53714
1 parent 93c6d77 commit 72be0b5

20 files changed

Lines changed: 789 additions & 53 deletions

File tree

‎docs/reference/sql/functions/date-time.asciidoc‎

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -404,6 +404,48 @@ include-tagged::{sql-specs}/docs/docs.csv-spec[dateDiffDateTimeMinutes]
404404
include-tagged::{sql-specs}/docs/docs.csv-spec[dateDiffDateMinutes]
405405
--------------------------------------------------
406406

407+
[[sql-functions-datetime-datetimeformat]]
408+
==== `DATETIME_FORMAT`
409+
410+
.Synopsis:
411+
[source, sql]
412+
--------------------------------------------------
413+
DATETIME_FORMAT(
414+
date_exp/datetime_exp/time_exp, <1>
415+
string_exp) <2>
416+
--------------------------------------------------
417+
418+
*Input*:
419+
420+
<1> date/datetime/time expression
421+
<2> format pattern
422+
423+
*Output*: string
424+
425+
*Description*: Returns the date/datetime/time as a string using the format specified in the 2nd argument. The formatting
426+
pattern used is the one from
427+
https://docs.oracle.com/en/java/javase/14/docs/api/java.base/java/time/format/DateTimeFormatter.html[`java.time.format.DateTimeFormatter`].
428+
If any of the two arguments is `null` or the pattern is an empty string `null` is returned.
429+
430+
NOTE::
431+
If the 1st argument is of type `time`, then pattern specified by the 2nd argument cannot contain date related units
432+
(e.g. 'dd', 'MM', 'YYYY', etc.). If it contains such units an error is returned.
433+
434+
[source, sql]
435+
--------------------------------------------------
436+
include-tagged::{sql-specs}/docs/docs.csv-spec[dateTimeFormatDate]
437+
--------------------------------------------------
438+
439+
[source, sql]
440+
--------------------------------------------------
441+
include-tagged::{sql-specs}/docs/docs.csv-spec[dateTimeFormatDateTime]
442+
--------------------------------------------------
443+
444+
[source, sql]
445+
--------------------------------------------------
446+
include-tagged::{sql-specs}/docs/docs.csv-spec[dateTimeFormatTime]
447+
--------------------------------------------------
448+
407449
[[sql-functions-datetime-part]]
408450
==== `DATE_PART/DATEPART`
409451

‎docs/reference/sql/functions/index.asciidoc‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,7 @@
5353
** <<sql-functions-current-timestamp>>
5454
** <<sql-functions-datetime-add>>
5555
** <<sql-functions-datetime-diff>>
56+
** <<sql-functions-datetime-datetimeformat>>
5657
** <<sql-functions-datetime-part>>
5758
** <<sql-functions-datetime-trunc>>
5859
** <<sql-functions-datetime-day>>

‎x-pack/plugin/sql/qa/src/main/resources/command.csv-spec‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,7 @@ CURTIME |SCALAR
4444
DATEADD |SCALAR
4545
DATEDIFF |SCALAR
4646
DATEPART |SCALAR
47+
DATETIME_FORMAT |SCALAR
4748
DATETRUNC |SCALAR
4849
DATE_ADD |SCALAR
4950
DATE_DIFF |SCALAR
@@ -60,8 +61,8 @@ DAY_OF_WEEK |SCALAR
6061
DAY_OF_YEAR |SCALAR
6162
DOM |SCALAR
6263
DOW |SCALAR
63-
DOY |SCALAR
64-
HOUR |SCALAR
64+
DOY |SCALAR
65+
HOUR |SCALAR
6566
HOUR_OF_DAY |SCALAR
6667
IDOW |SCALAR
6768
ISODAYOFWEEK |SCALAR

‎x-pack/plugin/sql/qa/src/main/resources/datetime.csv-spec‎

Lines changed: 104 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -476,6 +476,110 @@ null | 430
476476
F | 391
477477
;
478478

479+
selectDateTimeFormat
480+
schema::df_date:s|df_datetime:s|df_time:s
481+
SELECT DATETIME_FORMAT('2020-04-05T11:22:33.123Z'::date, 'dd/MM/YYYY HH:mm:ss.SSS') AS df_date,
482+
DATETIME_FORMAT('2020-04-05T11:22:33.123Z'::datetime, 'dd/MM/YYYY HH:mm:ss.SS') AS df_datetime,
483+
DATETIME_FORMAT('11:22:33.123456789Z'::time, 'HH:mm:ss.SS') AS df_time;
484+
485+
df_date | df_datetime | df_time
486+
------------------------+------------------------+----------------
487+
05/04/2020 00:00:00.000 | 05/04/2020 11:22:33.12 | 11:22:33.12
488+
;
489+
490+
selectDateTimeFormatWithField
491+
schema::birth_date:ts|df_birth_date1:s|df_birth_date2:s
492+
SELECT birth_date, DATETIME_FORMAT(birth_date, 'MM/dd/YYYY') AS df_birth_date1, DATETIME_FORMAT(birth_date, concat(gender, 'M/dd')) AS df_birth_date2
493+
FROM test_emp WHERE gender = 'M' AND emp_no BETWEEN 10037 AND 10052 ORDER BY emp_no;
494+
495+
birth_date | df_birth_date1 | df_birth_date2
496+
-------------------------+----------------+----------------
497+
1963-07-22 00:00:00.000Z | 07/22/1963 | 07/22
498+
1960-07-20 00:00:00.000Z | 07/20/1960 | 07/20
499+
1959-10-01 00:00:00.000Z | 10/01/1959 | 10/01
500+
null | null | null
501+
null | null | null
502+
null | null | null
503+
null | null | null
504+
null | null | null
505+
1958-05-21 00:00:00.000Z | 05/21/1958 | 05/21
506+
1953-07-28 00:00:00.000Z | 07/28/1953 | 07/28
507+
1961-02-26 00:00:00.000Z | 02/26/1961 | 02/26
508+
;
509+
510+
dateTimeFormatWhere
511+
schema::birth_date:ts|df_birth_date:s
512+
SELECT birth_date, DATETIME_FORMAT(birth_date, 'MM') AS df_birth_date FROM test_emp
513+
WHERE DATETIME_FORMAT(birth_date, 'MM')::integer > 10 ORDER BY emp_no LIMIT 10;
514+
515+
birth_date | df_birth_date
516+
-------------------------+---------------
517+
1959-12-03 00:00:00.000Z | 12
518+
1953-11-07 00:00:00.000Z | 11
519+
1952-12-24 00:00:00.000Z | 12
520+
1963-11-26 00:00:00.000Z | 11
521+
1956-12-13 00:00:00.000Z | 12
522+
1956-11-14 00:00:00.000Z | 11
523+
1962-12-29 00:00:00.000Z | 12
524+
1961-11-02 00:00:00.000Z | 11
525+
1952-11-13 00:00:00.000Z | 11
526+
1962-11-26 00:00:00.000Z | 11
527+
;
528+
529+
dateTimeFormatOrderBy
530+
schema::birth_date:ts|df_birth_date:s
531+
SELECT birth_date, DATETIME_FORMAT(birth_date, 'MM/dd/YYYY') AS df_birth_date FROM test_emp ORDER BY 2 DESC NULLS LAST LIMIT 10;
532+
533+
birth_date | df_birth_date
534+
-------------------------+---------------
535+
1962-12-29 00:00:00.000Z | 12/29/1962
536+
1959-12-25 00:00:00.000Z | 12/25/1959
537+
1952-12-24 00:00:00.000Z | 12/24/1952
538+
1960-12-17 00:00:00.000Z | 12/17/1960
539+
1956-12-13 00:00:00.000Z | 12/13/1956
540+
1959-12-03 00:00:00.000Z | 12/03/1959
541+
1957-12-03 00:00:00.000Z | 12/03/1957
542+
1963-11-26 00:00:00.000Z | 11/26/1963
543+
1962-11-26 00:00:00.000Z | 11/26/1962
544+
1962-11-19 00:00:00.000Z | 11/19/1962
545+
;
546+
547+
dateTimeFormatGroupBy
548+
schema::count:l|df_birth_date:s
549+
SELECT count(*) AS count, DATETIME_FORMAT(birth_date, 'MM') AS df_birth_date FROM test_emp GROUP BY df_birth_date ORDER BY 1 DESC, 2 DESC;
550+
551+
count | df_birth_date
552+
-------+---------------
553+
10 | 09
554+
10 | 05
555+
10 | null
556+
9 | 10
557+
9 | 07
558+
8 | 11
559+
8 | 04
560+
8 | 02
561+
7 | 12
562+
7 | 06
563+
6 | 08
564+
6 | 01
565+
2 | 03
566+
;
567+
568+
dateTimeFormatHaving
569+
schema::max:ts|df_birth_date:s
570+
SELECT MAX(birth_date) AS max, DATETIME_FORMAT(birth_date, 'MM') AS df_birth_date FROM test_emp GROUP BY df_birth_date
571+
HAVING DATETIME_FORMAT(MAX(birth_date), 'dd')::integer > 20 ORDER BY 1 DESC;
572+
573+
max | df_birth_date
574+
-------------------------+---------------
575+
1963-11-26 00:00:00.000Z | 11
576+
1963-07-22 00:00:00.000Z | 07
577+
1963-03-21 00:00:00.000Z | 03
578+
1962-12-29 00:00:00.000Z | 12
579+
1961-05-30 00:00:00.000Z | 05
580+
1961-02-26 00:00:00.000Z | 02
581+
;
582+
479583
selectDateTruncWithDateTime
480584
schema::dt_hour:ts|dt_min:ts|dt_sec:ts|dt_millis:s|dt_micro:s|dt_nano:s
481585
SELECT DATE_TRUNC('hour', '2019-09-04T11:22:33.123Z'::datetime) as dt_hour, DATE_TRUNC('minute', '2019-09-04T11:22:33.123Z'::datetime) as dt_min,

‎x-pack/plugin/sql/qa/src/main/resources/docs/docs.csv-spec‎

Lines changed: 32 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -240,6 +240,7 @@ CURTIME |SCALAR
240240
DATEADD |SCALAR
241241
DATEDIFF |SCALAR
242242
DATEPART |SCALAR
243+
DATETIME_FORMAT |SCALAR
243244
DATETRUNC |SCALAR
244245
DATE_ADD |SCALAR
245246
DATE_DIFF |SCALAR
@@ -256,8 +257,8 @@ DAY_OF_WEEK |SCALAR
256257
DAY_OF_YEAR |SCALAR
257258
DOM |SCALAR
258259
DOW |SCALAR
259-
DOY |SCALAR
260-
HOUR |SCALAR
260+
DOY |SCALAR
261+
HOUR |SCALAR
261262
HOUR_OF_DAY |SCALAR
262263
IDOW |SCALAR
263264
ISODAYOFWEEK |SCALAR
@@ -2548,6 +2549,35 @@ SELECT DATE_DIFF('minutes', '2019-09-04'::date, '2015-08-17T22:33:11.567Z'::date
25482549
// end::dateDiffDateMinutes
25492550
;
25502551

2552+
dateTimeFormatDate
2553+
// tag::dateTimeFormatDate
2554+
SELECT DATETIME_FORMAT(CAST('2020-04-05' AS DATE), 'dd/MM/YYYY') AS "date";
2555+
2556+
date
2557+
------------------
2558+
05/04/2020
2559+
// end::dateTimeFormatDate
2560+
;
2561+
2562+
dateTimeFormatDateTime
2563+
// tag::dateTimeFormatDateTime
2564+
SELECT DATETIME_FORMAT(CAST('2020-04-05T11:22:33.987654' AS DATETIME), 'dd/MM/YYYY HH:mm:ss.SS') AS "datetime";
2565+
2566+
datetime
2567+
------------------
2568+
05/04/2020 11:22:33.98
2569+
// end::dateTimeFormatDateTime
2570+
;
2571+
2572+
dateTimeFormatTime
2573+
// tag::dateTimeFormatTime
2574+
SELECT DATETIME_FORMAT(CAST('11:22:33.987' AS TIME), 'HH mm ss.S') AS "time";
2575+
2576+
time
2577+
------------------
2578+
11 22 33.9
2579+
// end::dateTimeFormatTime
2580+
;
25512581

25522582
datePartDateTimeYears
25532583
// tag::datePartDateTimeYears

‎x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/expression/function/SqlFunctionRegistry.java‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@
3232
import org.elasticsearch.xpack.sql.expression.function.scalar.datetime.DateAdd;
3333
import org.elasticsearch.xpack.sql.expression.function.scalar.datetime.DateDiff;
3434
import org.elasticsearch.xpack.sql.expression.function.scalar.datetime.DatePart;
35+
import org.elasticsearch.xpack.sql.expression.function.scalar.datetime.DateTimeFormat;
3536
import org.elasticsearch.xpack.sql.expression.function.scalar.datetime.DateTrunc;
3637
import org.elasticsearch.xpack.sql.expression.function.scalar.datetime.DayName;
3738
import org.elasticsearch.xpack.sql.expression.function.scalar.datetime.DayOfMonth;
@@ -168,6 +169,7 @@ private static FunctionDefinition[][] functions() {
168169
def(DateAdd.class, DateAdd::new, "DATEADD", "DATE_ADD", "TIMESTAMPADD", "TIMESTAMP_ADD"),
169170
def(DateDiff.class, DateDiff::new, "DATEDIFF", "DATE_DIFF", "TIMESTAMPDIFF", "TIMESTAMP_DIFF"),
170171
def(DatePart.class, DatePart::new, "DATEPART", "DATE_PART"),
172+
def(DateTimeFormat.class, DateTimeFormat::new, "DATETIME_FORMAT"),
171173
def(DateTrunc.class, DateTrunc::new, "DATETRUNC", "DATE_TRUNC"),
172174
def(HourOfDay.class, HourOfDay::new, "HOUR_OF_DAY", "HOUR"),
173175
def(IsoDayOfWeek.class, IsoDayOfWeek::new, "ISO_DAY_OF_WEEK", "ISODAYOFWEEK", "ISODOW", "IDOW"),
@@ -262,4 +264,4 @@ private static FunctionDefinition[][] functions() {
262264
}
263265
};
264266
}
265-
}
267+
}

‎x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/expression/function/scalar/Processors.java‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,7 @@
1515
import org.elasticsearch.xpack.sql.expression.function.scalar.datetime.DateAddProcessor;
1616
import org.elasticsearch.xpack.sql.expression.function.scalar.datetime.DateDiffProcessor;
1717
import org.elasticsearch.xpack.sql.expression.function.scalar.datetime.DatePartProcessor;
18+
import org.elasticsearch.xpack.sql.expression.function.scalar.datetime.DateTimeFormatProcessor;
1819
import org.elasticsearch.xpack.sql.expression.function.scalar.datetime.DateTimeProcessor;
1920
import org.elasticsearch.xpack.sql.expression.function.scalar.datetime.DateTruncProcessor;
2021
import org.elasticsearch.xpack.sql.expression.function.scalar.datetime.NamedDateTimeProcessor;
@@ -84,6 +85,7 @@ public static List<NamedWriteableRegistry.Entry> getNamedWriteables() {
8485
entries.add(new Entry(Processor.class, DateDiffProcessor.NAME, DateDiffProcessor::new));
8586
entries.add(new Entry(Processor.class, DatePartProcessor.NAME, DatePartProcessor::new));
8687
entries.add(new Entry(Processor.class, DateTruncProcessor.NAME, DateTruncProcessor::new));
88+
entries.add(new Entry(Processor.class, DateTimeFormatProcessor.NAME, DateTimeFormatProcessor::new));
8789
// math
8890
entries.add(new Entry(Processor.class, BinaryMathProcessor.NAME, BinaryMathProcessor::new));
8991
entries.add(new Entry(Processor.class, BinaryOptionalMathProcessor.NAME, BinaryOptionalMathProcessor::new));
@@ -103,4 +105,4 @@ public static List<NamedWriteableRegistry.Entry> getNamedWriteables() {
103105
entries.add(new Entry(Processor.class, StDistanceProcessor.NAME, StDistanceProcessor::new));
104106
return entries;
105107
}
106-
}
108+
}

‎x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/expression/function/scalar/datetime/BinaryDateTimeDatePartFunction.java‎

Lines changed: 92 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,92 @@
1+
/*
2+
* Copyright Elasticsearch B.V. and/or licensed to Elasticsearch B.V. under one
3+
* or more contributor license agreements. Licensed under the Elastic License;
4+
* you may not use this file except in compliance with the Elastic License.
5+
*/
6+
package org.elasticsearch.xpack.sql.expression.function.scalar.datetime;
7+
8+
import org.elasticsearch.xpack.ql.expression.Expression;
9+
import org.elasticsearch.xpack.ql.expression.Expressions;
10+
import org.elasticsearch.xpack.ql.tree.Source;
11+
12+
import java.time.ZoneId;
13+
import java.util.List;
14+
import java.util.Objects;
15+
16+
import static org.elasticsearch.common.logging.LoggerMessageFormat.format;
17+
import static org.elasticsearch.xpack.ql.expression.TypeResolutions.isString;
18+
19+
/**
20+
* Abstract super class for functions like {@link DateTrunc} and {@link DatePart}
21+
* which require an argument denoting a unit of date/time.
22+
*/
23+
public abstract class BinaryDateTimeDatePartFunction extends BinaryDateTimeFunction {
24+
25+
public BinaryDateTimeDatePartFunction(Source source, Expression datePart, Expression timestamp, ZoneId zoneId) {
26+
super(source, datePart, timestamp, zoneId);
27+
}
28+
29+
@Override
30+
protected TypeResolution resolveType() {
31+
TypeResolution resolution = isString(left(), sourceText(), Expressions.ParamOrdinal.FIRST);
32+
if (resolution.unresolved()) {
33+
return resolution;
34+
}
35+
36+
if (left().foldable()) {
37+
String datePartValue = (String) left().fold();
38+
if (datePartValue != null && resolveDateTimeField(datePartValue) == false) {
39+
List<String> similar = findSimilarDateTimeFields(datePartValue);
40+
if (similar.isEmpty()) {
41+
return new TypeResolution(
42+
format(
43+
null,
44+
"first argument of [{}] must be one of {} or their aliases; found value [{}]",
45+
sourceText(),
46+
validDateTimeFieldValues(),
47+
Expressions.name(left())
48+
)
49+
);
50+
} else {
51+
return new TypeResolution(
52+
format(
53+
null,
54+
"Unknown value [{}] for first argument of [{}]; did you mean {}?",
55+
Expressions.name(left()),
56+
sourceText(),
57+
similar
58+
)
59+
);
60+
}
61+
}
62+
}
63+
64+
return TypeResolution.TYPE_RESOLVED;
65+
}
66+
67+
protected abstract boolean resolveDateTimeField(String dateTimeField);
68+
69+
protected abstract List<String> findSimilarDateTimeFields(String dateTimeField);
70+
71+
protected abstract List<String> validDateTimeFieldValues();
72+
73+
@Override
74+
public int hashCode() {
75+
return Objects.hash(super.hashCode(), zoneId());
76+
}
77+
78+
@Override
79+
public boolean equals(Object o) {
80+
if (this == o) {
81+
return true;
82+
}
83+
if (o == null || getClass() != o.getClass()) {
84+
return false;
85+
}
86+
if (!super.equals(o)) {
87+
return false;
88+
}
89+
BinaryDateTimeDatePartFunction that = (BinaryDateTimeDatePartFunction) o;
90+
return zoneId().equals(that.zoneId());
91+
}
92+
}

0 commit comments

Comments
 (0)