This is an automated email from the ASF dual-hosted git repository.
zhenchen pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 951c02de62 [CALCITE-7337] Add age function (enabled in PostgreSQL
library)
951c02de62 is described below
commit 951c02de6244e73e9fe73e07ed5a2c9cf499a0c9
Author: Terran <[email protected]>
AuthorDate: Mon Dec 29 12:00:30 2025 +0800
[CALCITE-7337] Add age function (enabled in PostgreSQL library)
---
.../java/org/apache/calcite/test/BabelTest.java | 44 ++++++++
.../calcite/adapter/enumerable/RexImpTable.java | 3 +
.../org/apache/calcite/runtime/SqlFunctions.java | 111 +++++++++++++++++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 17 ++++
.../org/apache/calcite/util/BuiltInMethod.java | 3 +-
site/_docs/reference.md | 1 +
.../org/apache/calcite/test/SqlOperatorTest.java | 108 ++++++++++++++++++++
7 files changed, 286 insertions(+), 1 deletion(-)
diff --git a/babel/src/test/java/org/apache/calcite/test/BabelTest.java
b/babel/src/test/java/org/apache/calcite/test/BabelTest.java
index 09da755508..769975c4a8 100644
--- a/babel/src/test/java/org/apache/calcite/test/BabelTest.java
+++ b/babel/src/test/java/org/apache/calcite/test/BabelTest.java
@@ -490,4 +490,48 @@ 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\n");
+
+ checkSqlResult("postgresql",
+ "SELECT AGE(timestamp '2023-01-01', timestamp '2023-01-01') FROM
(VALUES (1)) t",
+ "EXPR$0=00:00:00\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\n");
+
+ checkSqlResult("postgresql",
+ "SELECT AGE(timestamp '2023-02-01', timestamp '2023-01-31') FROM
(VALUES (1)) t",
+ "EXPR$0=1 day\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=1 day\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 00:00:01\n");
+
+ 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 00:00:00.899\n");
+
+ checkSqlResult("postgresql",
+ "SELECT AGE(timestamp '2023-12-25 12:00:00.500', timestamp '2023-12-25
12:00:00.000') FROM (VALUES (1)) t",
+ "EXPR$0=00:00:00.5\n");
+
+ CalciteAssert.that()
+ .with(CalciteConnectionProperty.PARSER_FACTORY,
+ SqlBabelParserImpl.class.getName() + "#FACTORY")
+ .with(CalciteConnectionProperty.FUN, "postgresql")
+ .query("SELECT AGE(timestamp '2023-12-25') FROM (VALUES (1)) t")
+ .runs();
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index bbe2743ac7..103e91919f 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -134,6 +134,7 @@
import static org.apache.calcite.sql.fun.SqlInternalOperators.THROW_UNLESS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ACOSD;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ACOSH;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.AGE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ARRAY;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ARRAYS_OVERLAP;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ARRAYS_ZIP;
@@ -1029,6 +1030,8 @@ void populate2() {
define(FORMAT_TIME, datetimeFormatImpl);
define(FORMAT_TIMESTAMP, datetimeFormatImpl);
+ defineMethod(AGE, BuiltInMethod.AGE.method, NullPolicy.STRICT);
+
// Boolean operators
define(IS_NULL, new IsNullImplementor());
define(IS_NOT_NULL, new IsNotNullImplementor());
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 52ea3bbef5..50530aef67 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -60,6 +60,7 @@
import org.apache.commons.codec.binary.Hex;
import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.codec.language.Soundex;
+import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.math3.util.CombinatoricsUtils;
import org.apache.commons.text.StringEscapeUtils;
import org.apache.commons.text.similarity.LevenshteinDistance;
@@ -99,11 +100,13 @@
import java.text.Normalizer;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
+import java.time.Duration;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.OffsetDateTime;
+import java.time.Period;
import java.time.ZoneId;
import java.time.ZoneOffset;
import java.time.ZonedDateTime;
@@ -7503,4 +7506,112 @@ private enum PartToExtract {
AUTHORITY,
USERINFO;
}
+
+ /** SQL {@code AGE(timestamp1, timestamp2)} function. */
+ private static String age(long timestamp1, long timestamp2) {
+ // Convert timestamps to ZonedDateTime objects using UTC to avoid timezone
issues
+ Instant instant1 = Instant.ofEpochMilli(timestamp1);
+ Instant instant2 = Instant.ofEpochMilli(timestamp2);
+
+ ZonedDateTime dateTime1 = ZonedDateTime.ofInstant(instant1,
ZoneOffset.UTC);
+ ZonedDateTime dateTime2 = ZonedDateTime.ofInstant(instant2,
ZoneOffset.UTC);
+
+ // Check if the original timestamps are in the correct order
+ boolean isNegative = timestamp1 < timestamp2;
+
+ // Ensure dateTime1 is later than dateTime2 for consistent calculation
+ if (dateTime1.isBefore(dateTime2)) {
+ ZonedDateTime temp = dateTime1;
+ dateTime1 = dateTime2;
+ dateTime2 = temp;
+ }
+
+ // Calculate period (years, months, days)
+ Period period = Period.between(dateTime2.toLocalDate(),
dateTime1.toLocalDate());
+
+ // Calculate duration (hours, minutes, seconds, milliseconds)
+ Duration duration = Duration.between(dateTime2, dateTime1);
+
+ // Adjust for possible day overflow when time part is negative
+ if (dateTime1.toLocalTime().isBefore(dateTime2.toLocalTime())) {
+ period = period.minusDays(1);
+ duration = duration.plusDays(1);
+ }
+
+ // Extract components
+ int years = period.getYears();
+ int months = period.getMonths();
+ int days = period.getDays();
+
+ long hours = duration.toHours() % 24;
+ long minutes = duration.toMinutes() % 60;
+ long seconds = duration.getSeconds() % 60;
+ long millis = duration.toMillis() % 1000;
+
+ // Apply negative sign if needed
+ if (isNegative) {
+ years = -years;
+ months = -months;
+ days = -days;
+ }
+
+ StringBuilder sb = new StringBuilder();
+ if (years != 0) {
+ sb =
+ Math.abs(years) > 1 ? sb.append(years).append(" years ")
+ : sb.append(years).append(" year ");
+ }
+ if (months != 0) {
+ sb =
+ Math.abs(months) > 1 ? sb.append(months).append(" mons ")
+ : sb.append(months).append(" mon ");
+ }
+ if (days != 0) {
+ sb =
+ Math.abs(days) > 1 ? sb.append(days).append(" days ")
+ : sb.append(days).append(" day ");
+ }
+
+
+ // Add negative sign if needed for time part
+ if (isNegative && (hours != 0 || minutes != 0 || seconds != 0)) {
+ sb.append("-");
+ }
+ if (millis != 0) {
+ String millisString = BigDecimal.valueOf(millis)
+ .divide(BigDecimal.valueOf(1000))
+ .stripTrailingZeros()
+ .toPlainString().substring(2);
+ sb.append(
+ String.format(Locale.ROOT, "%02d:%02d:%02d.%s", hours, minutes,
seconds,
+ millisString));
+ } else if (ObjectUtils.isNotEmpty(sb)
+ && hours == 0 && minutes == 0 && seconds == 0 && millis == 0) {
+ return sb.toString().trim();
+ } else {
+ sb.append(String.format(Locale.ROOT, "%02d:%02d:%02d", hours, minutes,
seconds));
+ }
+ return sb.toString().trim();
+ }
+
+ /** SQL {@code AGE(timestamp1, timestamp2)} function. Supports 1 or 2
timestamp arguments. */
+ public static String age(long... timestamps) {
+ if (timestamps.length == 0) {
+ throw new IllegalArgumentException("AGE function requires at least one
timestamp argument");
+ }
+
+ if (timestamps.length == 1) {
+ // Single parameter version: calculate age relative to current time
+ long timestamp = timestamps[0];
+ // Use the actual current timestamp (including time component) in UTC
+ long currentTimestamp = Instant.now().toEpochMilli();
+ // Call the two-parameter version with current timestamp and input
timestamp
+ return age(currentTimestamp, timestamp);
+ } else if (timestamps.length == 2) {
+ // Two parameter version: calculate age between two timestamps
+ return age(timestamps[0], timestamps[1]);
+ } else {
+ throw new IllegalArgumentException("AGE function supports only 1 or 2
timestamp arguments");
+ }
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index 83f8787fd5..947223b682 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -2784,4 +2784,21 @@ private static RelDataType
deriveTypeMapFromEntries(SqlOperatorBinding opBinding
public static final SqlFunction RANDOM = SqlStdOperatorTable.RAND
.withName("RANDOM")
.withOperandTypeChecker(OperandTypes.NILADIC);
+
+ /**
+ * AGE function for PostgreSQL.
+ * Returns a human-readable VARCHAR describing the interval between
+ * one or two timestamps (for example,
+ * "3 years 11 mons 24 days 0 hours 0 mins 0.0 secs").
+ *
+ * @see <a
href="https://www.postgresql.org/docs/current/functions-datetime.html";>PostgreSQL
AGE</a>
+ */
+ @LibraryOperator(libraries = {POSTGRESQL}, exceptLibraries = {REDSHIFT})
+ public static final SqlBasicFunction AGE =
+ SqlBasicFunction.create("AGE",
+ ReturnTypes.VARCHAR_NULLABLE,
+ OperandTypes.or(
+ OperandTypes.family(SqlTypeFamily.TIMESTAMP),
+ OperandTypes.family(SqlTypeFamily.TIMESTAMP,
SqlTypeFamily.TIMESTAMP)),
+ SqlFunctionCategory.TIMEDATE);
}
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index cfeb4fe77c..221ff5f713 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -981,7 +981,8 @@ public enum BuiltInMethod {
ImmutableBitSet.class),
FUNCTIONAL_DEPENDENCY_DETERMINANTS(FunctionalDependency.class,
"determinants",
ImmutableBitSet.class),
- FUNCTIONAL_DEPENDENCY_GET_FDS(FunctionalDependency.class, "getFDs");
+ FUNCTIONAL_DEPENDENCY_GET_FDS(FunctionalDependency.class, "getFDs"),
+ AGE(SqlFunctions.class, "age", long[].class);
@SuppressWarnings("ImmutableEnumChecker")
public final Method method;
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index b922253fe0..2668cbf83c 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -3076,6 +3076,7 @@ ### Dialect-specific Operators
| b | TO_CODE_POINTS(string) | Converts *string* to an
array of integers that represent code points or extended ASCII character values
| o p r h | TO_DATE(string, format) | Converts *string* to
a date using the format *format*
| o p r | TO_TIMESTAMP(string, format) | Converts *string* to a
timestamp using the format *format*
+| p | AGE(timestamp1 [, timestamp2 ]) | Returns a formatted
string representing the difference between timestamps (for example, "3 years 11
mons 24 days 0 hours 0 mins 0.0 secs"), not an interval type. With one
argument, returns the difference between the current timestamp at midnight UTC
and the specified timestamp. With two arguments, returns the difference between
*timestamp1* and *timestamp2*
| b o p r s | TRANSLATE(expr, fromString, toString) | Returns *expr* with all
occurrences of each character in *fromString* replaced by its corresponding
character in *toString*. Characters in *expr* that are not in *fromString* are
not replaced
| b | TRUNC(numeric1 [, integer2 ]) | Truncates *numeric1* to
optionally *integer2* (if not specified 0) places right to the decimal point
| q | TRY_CAST(value AS type) | Converts *value* to
*type*, returning NULL if conversion fails
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index b27c188620..5530349604 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -6948,6 +6948,114 @@ void checkRegexpExtract(SqlOperatorFixture f0,
FunctionAlias functionAlias) {
f.checkNull("json_storage_size(cast(null as varchar))");
}
+ /** 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 testAgePg() {
+ final SqlOperatorFixture f0 = fixture();
+ f0.checkFails("^age(timestamp '2023-12-25', timestamp '2020-01-01')^",
+ "No match found for function signature AGE\\(<TIMESTAMP>,
<TIMESTAMP>\\)", false);
+
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.POSTGRESQL);
+
+ // Test illegal timestamp argument
+ f.checkFails("age(^timestamp 'abc'^, timestamp '2023-12-25')",
+ "Illegal TIMESTAMP literal 'abc': not in format 'yyyy-MM-dd HH:mm:ss'",
+ false);
+ f.checkFails("age(timestamp '2023-12-25', ^timestamp 'invalid-date'^)",
+ "Illegal TIMESTAMP literal 'invalid-date': not in format 'yyyy-MM-dd
HH:mm:ss'",
+ false);
+ f.checkFails("age(^timestamp '2023-12-25 25:61:61'^, timestamp
'2023-12-25')",
+ "Illegal TIMESTAMP literal '2023-12-25 25:61:61': not in format
'yyyy-MM-dd HH:mm:ss'",
+ false);
+ f.checkFails("age(^timestamp '2023-02-30'^, timestamp '2023-12-25')",
+ "Illegal TIMESTAMP literal '2023-02-30': not in format 'yyyy-MM-dd
HH:mm:ss'",
+ false);
+ f.checkFails("age(^timestamp ''^)",
+ "Illegal TIMESTAMP literal '': not in format 'yyyy-MM-dd HH:mm:ss'",
+ false);
+ f.checkFails("age(^timestamp '2023-13-25 12:00:00'^)",
+ "Illegal TIMESTAMP literal '2023-13-25 12:00:00': not in format
'yyyy-MM-dd HH:mm:ss'",
+ false);
+
+ // Test two timestamp arguments
+ f.checkScalar("age(timestamp '2023-12-25', timestamp '2020-01-01')",
+ "3 years 11 mons 24 days",
+ "VARCHAR NOT NULL");
+ f.checkScalar("age(timestamp '2023-01-01', timestamp '2023-01-01')",
+ "00:00:00",
+ "VARCHAR NOT NULL");
+ f.checkScalar("age(timestamp '2020-01-01', timestamp '2023-12-25')",
+ "-3 years -11 mons -24 days",
+ "VARCHAR NOT NULL");
+ f.checkScalar("age(timestamp '2023-02-01', timestamp '2023-01-31')",
+ "1 day",
+ "VARCHAR NOT NULL");
+ f.checkScalar("age(timestamp '2023-12-26 14:30:00', timestamp '2023-12-25
14:30:00')",
+ "1 day",
+ "VARCHAR NOT NULL");
+ f.checkScalar("age(timestamp '2023-12-25 00:00:00', timestamp '2020-01-01
23:59:59')",
+ "3 years 11 mons 23 days 00:00:01",
+ "VARCHAR NOT NULL");
+
+ // Test single timestamp argument (relative to current time)
+ f.checkType("age(timestamp '2023-12-25')", "VARCHAR NOT NULL");
+
+ // NULL value tests
+ f.checkNull("age(null, timestamp '2023-12-25')");
+ f.checkNull("age(timestamp '2023-12-25', null)");
+ f.checkNull("age(null, null)");
+ f.checkNull("age(null)");
+
+ // Boundary date tests (Unix epoch time)
+ f.checkScalar("age(timestamp '1970-01-01', timestamp '1970-01-01')",
+ "00:00:00",
+ "VARCHAR NOT NULL");
+ f.checkScalar("age(timestamp '1970-01-02', timestamp '1970-01-01')",
+ "1 day",
+ "VARCHAR NOT NULL");
+
+ // Time boundary tests (start and end of day)
+ f.checkScalar("age(timestamp '2023-12-25 23:59:59', timestamp '2023-12-25
00:00:00')",
+ "23:59:59",
+ "VARCHAR NOT NULL");
+ f.checkScalar("age(timestamp '2023-12-26 00:00:00', timestamp '2023-12-25
23:59:59')",
+ "00:00:01",
+ "VARCHAR NOT NULL");
+
+ // Leap year tests
+ f.checkScalar("age(timestamp '2024-02-29', timestamp '2023-02-28')",
+ "1 year 1 day",
+ "VARCHAR NOT NULL");
+ f.checkScalar("age(timestamp '2024-03-01', timestamp '2023-02-28')",
+ "1 year 2 days",
+ "VARCHAR NOT NULL");
+
+ // Month boundary tests (across months)
+ f.checkScalar("age(timestamp '2023-03-01', timestamp '2023-02-01')",
+ "1 mon",
+ "VARCHAR NOT NULL");
+ f.checkScalar("age(timestamp '2023-03-31', timestamp '2023-02-28')",
+ "1 mon 3 days",
+ "VARCHAR NOT NULL");
+
+ // Year boundary tests
+ f.checkScalar("age(timestamp '2024-01-01', timestamp '2023-01-01')",
+ "1 year",
+ "VARCHAR NOT NULL");
+ f.checkScalar("age(timestamp '2024-01-01', timestamp '2023-12-31')",
+ "1 day",
+ "VARCHAR NOT NULL");
+
+ // Actual execution test for single parameter version
+ f.checkType("age(timestamp '1970-01-01')", "VARCHAR NOT NULL");
+
+ // Millisecond precision tests
+ f.checkScalar("age(timestamp '2023-12-25 12:00:00.500', timestamp
'2023-12-25 12:00:00.000')",
+ "00:00:00.5",
+ "VARCHAR NOT NULL");
+ }
+
@Test void testJsonType() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlLibraryOperators.JSON_TYPE, VmName.EXPAND);
