如何将java.sql.Timestamp四舍五入至最近的一天？

问题分析 #

你当前的毫秒计算逻辑本身是正确的，但Timestamp的toString()方法默认使用本地时区输出结果，这会导致你误以为计算出错。比如当你按UTC时区计算出的是2023-02-26T00:00:00Z，toString()会把它转成你本地时区的时间，看起来和预期不符。

解决方案 #

方案1：修正结果的显示方式（基于原有毫秒计算逻辑） #

保持你的毫秒计算逻辑，只需要在打印时强制使用UTC时区输出，就能看到正确的UTC时区结果：

import java.sql.Timestamp;
import java.time.Instant;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;

public class SqlTimestamp {
    private static final DateTimeFormatter UTC_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSSSSSSS")
            .withZone(ZoneId.of("UTC"));

    public static void main(String... strings) {
        Timestamp[] timestamps = {
                new Timestamp(Timestamp.valueOf("2023-02-19 00:00:00.000000000").getTime()),
                new Timestamp(Timestamp.valueOf("2023-02-25 23:59:59.999999999").getTime()),
                new Timestamp(Timestamp.valueOf("2023-02-25 12:00:00.000000000").getTime()),
                new Timestamp(Timestamp.valueOf("2023-02-25 12:29:00.000000000").getTime()),
                new Timestamp(Timestamp.valueOf("2023-02-25 12:30:00.000000000").getTime()),
                new Timestamp(Timestamp.valueOf("2023-02-25 12:29:59.999999999").getTime()),
        };

        long ONE_DAY_MS = 60000 * 60 * 24;
        long HALF_DAY_MS = ONE_DAY_MS / 2;

        for (Timestamp ts : timestamps) {
            System.out.println("Original timestamp (UTC): " + UTC_FORMATTER.format(ts.toInstant()));
            
            // 按小时取整（原逻辑）
            Timestamp roundedHour = Timestamp.from(Instant.ofEpochMilli(((ts.getTime() + 1800000) / (60000 * 60)) * (60000 * 60)));
            System.out.println("Round to nearest hour (UTC): " + UTC_FORMATTER.format(roundedHour.toInstant()));
            
            // 按天取整（原逻辑，修正显示）
            Timestamp roundedDay = Timestamp.from(Instant.ofEpochMilli(((ts.getTime() + HALF_DAY_MS) / ONE_DAY_MS) * ONE_DAY_MS));
            System.out.println("Round to nearest day (UTC): " + UTC_FORMATTER.format(roundedDay.toInstant()));
            System.out.println("---");
        }
    }
}

方案2：使用Java 8+的java.time API（推荐） #

java.sql.Timestamp可以直接转换为Instant（本身就是UTC时区的时间戳），然后通过LocalDate、ZoneOffset等API更直观地实现按天取整，避免手动计算毫秒的误差和时区混淆：

import java.sql.Timestamp;
import java.time.Instant;
import java.time.LocalDate;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;

public class SqlTimestamp {
    private static final DateTimeFormatter UTC_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSSSSSSS")
            .withZone(ZoneOffset.UTC);

    public static void main(String... strings) {
        Timestamp[] timestamps = {
                new Timestamp(Timestamp.valueOf("2023-02-19 00:00:00.000000000").getTime()),
                new Timestamp(Timestamp.valueOf("2023-02-25 23:59:59.999999999").getTime()),
                new Timestamp(Timestamp.valueOf("2023-02-25 12:00:00.000000000").getTime()),
                new Timestamp(Timestamp.valueOf("2023-02-25 12:29:00.000000000").getTime()),
                new Timestamp(Timestamp.valueOf("2023-02-25 12:30:00.000000000").getTime()),
                new Timestamp(Timestamp.valueOf("2023-02-25 12:29:59.999999999").getTime()),
        };

        for (Timestamp ts : timestamps) {
            Instant instant = ts.toInstant();
            System.out.println("Original timestamp (UTC): " + UTC_FORMATTER.format(instant));

            // 按天取整：以UTC的12点为分界，之前的归为当天，之后的归为次日
            LocalDate date = instant.atOffset(ZoneOffset.UTC).toLocalDate();
            Instant roundedInstant;
            if (instant.atOffset(ZoneOffset.UTC).getHour() >= 12) {
                roundedInstant = date.plusDays(1).atStartOfDay(ZoneOffset.UTC).toInstant();
            } else {
                roundedInstant = date.atStartOfDay(ZoneOffset.UTC).toInstant();
            }
            Timestamp roundedDay = Timestamp.from(roundedInstant);
            System.out.println("Round to nearest day (UTC): " + UTC_FORMATTER.format(roundedDay.toInstant()));
            System.out.println("---");
        }
    }
}

关键说明 #

• 旧API的Timestamp.toString()会使用本地时区格式化输出，这是导致你误以为计算错误的核心原因，必须用UTC时区格式化才能看到正确结果。
• Java 8+的java.timeAPI是线程安全且设计更清晰的时间处理方案，推荐替代旧的java.sql.Timestamp直接使用Instant、ZonedDateTime等类。

内容的提问来源于stack exchange，提问作者Oxnard