caicancai commented on code in PR #4703:
URL: https://github.com/apache/calcite/pull/4703#discussion_r2645271118
##########
core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java:
##########
@@ -7445,4 +7448,83 @@ private enum PartToExtract {
AUTHORITY,
USERINFO;
}
+
+ /** SQL {@code AGE(timestamp1, timestamp2)} function. */
+ public 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 totalHours = duration.toHours();
+ long totalMinutes = duration.toMinutes();
+ long totalSeconds = duration.getSeconds();
+ long totalMillis = duration.toMillis();
+
+ long hours = totalHours % 24;
+ long minutes = totalMinutes % 60;
+ long seconds = totalSeconds % 60;
+ long millis = totalMillis % 1000;
+
+ // Apply negative sign if needed
+ if (isNegative) {
+ years = -years;
+ months = -months;
+ days = -days;
+ hours = -hours;
+ minutes = -minutes;
+ seconds = -seconds;
+ }
+
+ StringBuilder sb = new StringBuilder();
+ sb.append(years).append(" years ")
+ .append(months).append(" mons ")
+ .append(days).append(" days ")
+ .append(hours).append(" hours ")
+ .append(minutes).append(" mins ")
+ .append(String.format(Locale.ROOT, "%.1f secs", seconds + millis /
1000.0));
+ return sb.toString();
+ }
+
+ /** SQL {@code AGE(timestamp)} function. */
+ public static String age(long timestamp) {
+ // Get current date at midnight in UTC
+ long currentTimestamp = Instant.now()
+ .atZone(ZoneOffset.UTC)
Review Comment:
Does it have to be UTC?
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -6948,6 +6948,105 @@ 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]
+ * There is no age function that supports pgSQL</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 two timestamp arguments
+ f.checkScalar("age(timestamp '2023-12-25', timestamp '2020-01-01')",
+ "3 years 11 mons 24 days 0 hours 0 mins 0.0 secs",
+ "VARCHAR NOT NULL");
+
+ f.checkScalar("age(timestamp '2023-01-01', timestamp '2023-01-01')",
+ "0 years 0 mons 0 days 0 hours 0 mins 0.0 secs",
+ "VARCHAR NOT NULL");
+
+ f.checkScalar("age(timestamp '2020-01-01', timestamp '2023-12-25')",
+ "-3 years -11 mons -24 days 0 hours 0 mins 0.0 secs",
+ "VARCHAR NOT NULL");
+
+ f.checkScalar("age(timestamp '2023-02-01', timestamp '2023-01-31')",
+ "0 years 0 mons 1 days 0 hours 0 mins 0.0 secs",
+ "VARCHAR NOT NULL");
+
+ f.checkScalar("age(timestamp '2023-12-26 14:30:00', timestamp '2023-12-25
14:30:00')",
+ "0 years 0 mons 1 days 0 hours 0 mins 0.0 secs",
+ "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 0 hours 0 mins 1.0 secs",
+ "VARCHAR NOT NULL");
+
+ // Test single timestamp argument (relative to current time)
+ f.checkType("age(timestamp '2023-12-25')", "VARCHAR NOT NULL");
+
+
Review Comment:
Extra blank lines
--
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]
