cjj2010 commented on code in PR #4689:
URL: https://github.com/apache/calcite/pull/4689#discussion_r2633439410
##########
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:
The current approach is to convert the incoming time and the current system
time into UTC before performing calculations
--
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]
