xuzifu666 commented on code in PR #4689:
URL: https://github.com/apache/calcite/pull/4689#discussion_r2626716266
##########
core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java:
##########
@@ -7445,4 +7448,79 @@ private enum PartToExtract {
AUTHORITY,
USERINFO;
}
+
+ /** SQL {@code AGE(timestamp1, timestamp2)} function. */
+ public static String age(long timestamp1, long timestamp2) {
+ // Convert timestamps to LocalDateTime objects
+ Instant instant1 = Instant.ofEpochMilli(timestamp1);
+ Instant instant2 = Instant.ofEpochMilli(timestamp2);
+
+ LocalDateTime dateTime1 = LocalDateTime.ofInstant(instant1,
ZoneOffset.UTC);
+ LocalDateTime dateTime2 = LocalDateTime.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)) {
+ LocalDateTime 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
+ LocalDateTime now =
LocalDateTime.now(ZoneOffset.UTC).truncatedTo(ChronoUnit.DAYS);
Review Comment:
Just want to know if this PG function differs across different time zones.
The time zone here is fixed UTC.
##########
babel/src/test/java/org/apache/calcite/test/BabelTest.java:
##########
@@ -490,4 +496,45 @@ private void checkSqlResult(String funLibrary, String
query, String result) {
.query(query)
.returns(result);
}
+
+ /**
+ * Test AGE function with timestamp arguments.
+ */
+ @Test void testAgeFunction() {
Review Comment:
It's generally recommended to add related tests to ```SqlOperatorTest```
when adding new functions. Additionally, JIRA link and description should be
added to the test methods; you can refer to other relevant links for more
information.
--
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]
