Add implementation of `now`, `sysdate`, `localtime` and similar functions

[Yury-Fridlyand]

Signed-off-by: Yury Fridlyand [email protected]

Description

New functions added:

• now()
• current_timestamp()
• localtimestamp()
• localtime()
• sysdate()
• curtime()
• current_time()
• curdate()
• current_date()

New signatures:

• now() : datetime
• now(int) : datetime
• current_timestamp() : datetime
• current_timestamp(int) : datetime
• current_timestamp : datetime
• localtimestamp() : datetime
• localtimestamp(int) : datetime
• localtimestamp : datetime
• localtime() : datetime
• localtime(int) : datetime
• localtime : datetime
• sysdate() : datetime
• sysdate(int) : datetime
• curtime() : time
• curtime(int) : time
• current_time() : time
• current_time : time
• current_time(int) : time
• curdate() : date
• current_date() : date
• current_date : date

Tests

SQL

select now(), now(0), now(4), current_timestamp(), current_timestamp(0), current_timestamp(5), localtimestamp(), localtimestamp(1), localtimestamp(2), localtime(), localtime(0), sysdate(), sysdate(3), curdate(), curtime(), curtime(3), curtime(0), current_time(), current_date(), CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP;

fetched rows / total rows = 1/1
-[ RECORD 1 ]-------------------------
now()                   | 2022-07-20 20:15:25.262409
now(0)                  | 2022-07-20 20:15:25.000000
now(4)                  | 2022-07-20 20:15:25.262400
current_timestamp()     | 2022-07-20 20:15:25.262409
current_timestamp(0)    | 2022-07-20 20:15:25.000000
current_timestamp(5)    | 2022-07-20 20:15:25.262400
localtimestamp()        | 2022-07-20 20:15:25.262409
localtimestamp(1)       | 2022-07-20 20:15:25.200000
localtimestamp(2)       | 2022-07-20 20:15:25.260000
localtime()             | 2022-07-20 20:15:25.262409
localtime(0)            | 2022-07-20 20:15:25.000000
sysdate()               | 2022-07-20 20:15:26.468210
sysdate(3)              | 2022-07-20 20:15:26.468000
curdate()               | 2022-07-20
curtime()               | 20:15:26.469603
curtime(3)              | 20:15:26.469000
curtime(0)              | 20:15:26.000000
current_time()          | 20:15:26.469746
current_date()          | 2022-07-20
CURRENT_DATE            | 2022-07-20
CURRENT_TIME            | 20:15:26.469869
CURRENT_TIMESTAMP       | 2022-07-20 20:15:25.262409
LOCALTIME               | 2022-07-20 20:15:25.262409
LOCALTIMESTAMP          | 2022-07-20 20:15:25.262409

PPL

source=calcs | top 1 zzz | eval `now()` = now(), `now(0)` = now(0), `now(4)` = now(4), `current_timestamp()` = current_timestamp(), `current_timestamp(0)` = current_timestamp(0), `current_timestamp(5)` = current_timestamp(5), `localtimestamp()` = localtimestamp(), `localtimestamp(1)` = localtimestamp(1), `localtimestamp(2)` = localtimestamp(2), `localtime()` = localtime(), `localtime(0)` = localtime(0), `sysdate()` = sysdate(), `sysdate(3)` = sysdate(3), `curdate()` = curdate(), `curtime()` = curtime(), `curtime(3)` = curtime(3), `curtime(0)` = curtime(0), `current_time()` = current_time(), `current_date()` = current_date(), `CURRENT_DATE` = CURRENT_DATE, `CURRENT_TIME` = CURRENT_TIME, `CURRENT_TIMESTAMP` = CURRENT_TIMESTAMP, `LOCALTIME` = LOCALTIME, `LOCALTIMESTAMP` = LOCALTIMESTAMP;

fetched rows / total rows = 1/1
-[ RECORD 1 ]-------------------------
zzz                     | f
now()                   | 2022-07-20 20:17:22.451338
now(0)                  | 2022-07-20 20:17:22.000000
now(4)                  | 2022-07-20 20:17:22.451300
current_timestamp()     | 2022-07-20 20:17:22.451338
current_timestamp(0)    | 2022-07-20 20:17:22.000000
current_timestamp(5)    | 2022-07-20 20:17:22.451330
localtimestamp()        | 2022-07-20 20:17:22.451338
localtimestamp(1)       | 2022-07-20 20:17:22.400000
localtimestamp(2)       | 2022-07-20 20:17:22.450000
localtime()             | 2022-07-20 20:17:22.451338
localtime(0)            | 2022-07-20 20:17:22.000000
sysdate()               | 2022-07-20 20:17:22.760505
sysdate(3)              | 2022-07-20 20:17:22.760000
curdate()               | 2022-07-20
curtime()               | 20:17:22.760584
curtime(3)              | 20:17:22.760000
curtime(0)              | 20:17:22.000000
current_time()          | 20:17:22.760626
current_date()          | 2022-07-20
CURRENT_DATE            | 2022-07-20
CURRENT_TIME            | 20:17:22.760693
CURRENT_TIMESTAMP       | 2022-07-20 20:17:22.451338
LOCALTIME               | 2022-07-20 20:17:22.451338
LOCALTIMESTAMP          | 2022-07-20 20:17:22.451338

Implementation details

NOW and SYSDATE difference According to MySQL standard

NOW() returns a constant time that indicates the time at which the statement began to execute.

SYSDATE() returns the time at which it executes.

I had to save and then to pick time when plugin receives a query. QueryContext was chosen to store this unless a better candidate introduced.

Parser update

1. datetimeConstantLiteral was added to SQL and PPL parsers. It lists pseudo-constants which actually invoke corresponding functions without (). So CURRENT_DATE is shortcut for CURRENT_DATE() and so on.
2. AstExpressionBuilder were updated to call a function when datetimeConstantLiteral met.
3. AstExpressionBuilder in PPL is updated a bit by adding visitFunction method and removing some duplicated code. Copied from AstExpressionBuilder in SQL.
4. ANTLR rules were copied from SQL to PPL.

Doctests Doctests for new functions always fail, because test validate exact string match for result. I added docs, but disabled doctests for them.

Issues Resolved

• #722
• #93
• #46
• #260

See also

• https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
• https://github.com/opensearch-project/sql/pull/171

Check List

• [x] New functionality includes testing.
  • [x] All tests pass, including unit test, integration test and doctest
• [x] New functionality has been documented.
  • [x] New functionality has javadoc added
  • [x] New functionality has user manual doc added
• [x] Commits are signed per the DCO using --signoff

By submitting this pull request, I confirm that my contribution is made under the terms of the Apache 2.0 license. For more information on following Developer Certificate of Origin and signing off your commits, please check here.

[codecov-commenter]

Codecov Report

Merging #754 (bd79364) into 2.x (929ebfe) will increase coverage by 0.04%. The diff coverage is 100.00%.

@@             Coverage Diff              @@
##                2.x     #754      +/-   ##
============================================
+ Coverage     94.83%   94.87%   +0.04%     
- Complexity     2916     2956      +40     
============================================
  Files           291      291              
  Lines          7795     7869      +74     
  Branches        567      572       +5     
============================================
+ Hits           7392     7466      +74     
  Misses          349      349              
  Partials         54       54              

Flag	Coverage Δ
query-workbench	62.76% <ø> (ø)
sql-engine	97.80% <100.00%> (+0.02%)	:arrow_up:

Flags with carried forward coverage won't be shown. Click here to find out more.

Impacted Files	Coverage Δ
...a/org/opensearch/sql/analysis/AnalysisContext.java	100.00% <100.00%> (ø)
...rg/opensearch/sql/analysis/ExpressionAnalyzer.java	100.00% <100.00%> (ø)
...c/main/java/org/opensearch/sql/expression/DSL.java	100.00% <100.00%> (ø)
...arch/sql/expression/datetime/DateTimeFunction.java	100.00% <100.00%> (ø)
...h/sql/expression/function/BuiltinFunctionName.java	100.00% <100.00%> (ø)
...pensearch/sql/ppl/parser/AstExpressionBuilder.java	100.00% <100.00%> (ø)
...pensearch/sql/sql/parser/AstExpressionBuilder.java	100.00% <100.00%> (ø)

Help us with your feedback. Take ten seconds to tell us how you rate us. Have a feature suggestion? Share it here.

[MaxKsyunz]

@Yury-Fridlyand please update the target branch from main to 2.x