xiedeyantu commented on code in PR #4712:
URL: https://github.com/apache/calcite/pull/4712#discussion_r2652041955
##########
core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java:
##########
@@ -7491,4 +7493,91 @@ private enum PartToExtract {
AUTHORITY,
USERINFO;
}
+
+ /** SQL {@code AGE(timestamp1, timestamp2)} function. */
+ private static String age(long timestamp1, long timestamp2) {
Review Comment:
Please take a look at this code and see if it meets your requirements. I
tested it locally, and it passed your test cases. The current code
implementation seems somewhat unclear.
```
public static String age(long timestamp1, long timestamp2) {
ZonedDateTime zdt1 = Instant.ofEpochMilli(timestamp1)
.atZone(ZoneOffset.UTC);
ZonedDateTime zdt2 = Instant.ofEpochMilli(timestamp2)
.atZone(ZoneOffset.UTC);
Period period = Period.between(zdt2.toLocalDate(), zdt1.toLocalDate());
Duration duration = Duration.between(zdt2, zdt1);
int years = period.getYears();
int months = period.getMonths();
int days = period.getDays();
long totalSeconds = duration.getSeconds();
long hours = (totalSeconds / 3600) % 24;
long minutes = (totalSeconds / 60) % 60;
long seconds = totalSeconds % 60;
long millis = duration.toMillis() % 1000;
return String.format(Locale.ROOT,
"%d years %d mons %d days %d hours %d mins %.1f secs",
years, months, days, hours, minutes, seconds + millis / 1000.0
);
}
```
##########
babel/src/test/java/org/apache/calcite/test/BabelTest.java:
##########
@@ -490,4 +490,59 @@ private void checkSqlResult(String funLibrary, String
query, String result) {
.query(query)
.returns(result);
}
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7337";>[CALCITE-7337]
+ * Add age function (enabled in PostgreSQL library)</a>. */
+ @Test void testAgeFunction() {
+ checkSqlResult("postgresql",
+ "SELECT AGE(timestamp '2023-12-25', timestamp '2020-01-01') FROM
(VALUES (1)) t",
+ "EXPR$0=3 years 11 mons 24 days 0 hours 0 mins 0.0 secs\n");
+
+ checkSqlResult("postgresql",
+ "SELECT AGE(timestamp '2023-01-01', timestamp '2023-01-01') FROM
(VALUES (1)) t",
+ "EXPR$0=0 years 0 mons 0 days 0 hours 0 mins 0.0 secs\n");
+
+ checkSqlResult("postgresql",
+ "SELECT AGE(timestamp '2020-01-01', timestamp '2023-12-25') FROM
(VALUES (1)) t",
+ "EXPR$0=-3 years -11 mons -24 days 0 hours 0 mins 0.0 secs\n");
+
+ checkSqlResult("postgresql",
+ "SELECT AGE(timestamp '2023-02-01', timestamp '2023-01-31') FROM
(VALUES (1)) t",
+ "EXPR$0=0 years 0 mons 1 days 0 hours 0 mins 0.0 secs\n");
+
+ checkSqlResult("postgresql",
+ "SELECT AGE(timestamp '2023-12-26 14:30:00', timestamp '2023-12-25
14:30:00') FROM (VALUES (1)) t",
+ "EXPR$0=0 years 0 mons 1 days 0 hours 0 mins 0.0 secs\n");
+
+ checkSqlResult("postgresql",
+ "SELECT AGE(timestamp '2023-12-25 00:00:00', timestamp '2020-01-01
23:59:59') FROM (VALUES (1)) t",
+ "EXPR$0=3 years 11 mons 23 days 0 hours 0 mins 1.0 secs\n");
+
Review Comment:
I think we should add another test case.
```
checkSqlResult("postgresql",
"SELECT AGE(timestamp '2023-12-25 00:00:00.101', timestamp
'2020-01-01 23:59:59.202') FROM (VALUES (1)) t",
"EXPR$0=3 years 11 mons 23 days 0 hours 0 mins 0.9 secs\n");
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
