cjj2010 commented on code in PR #4703:
URL: https://github.com/apache/calcite/pull/4703#discussion_r2646491310
##########
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:
> Could you please post the relevant PG documentation?
This pr and jira have doc link，We will find that PG in the document converts
the time to the current session time zone and then calculates it
https://www.postgresql.org/docs/current/functions-datetime.html
--
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]
