如何在R2JDBC中解析PostgreSQL的'Infinity'时间戳？

当使用R2JDBC连接PostgreSQL时，若数据库字段包含infinity或-infinity时间戳，会触发如下解析错误：

java.time.format.DateTimeParseException: Text 'infinity' could not be parsed at index 0
    at java.base/java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:2052) ~[na:na]
    at java.base/java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1954) ~[na:na]
    at io.r2dbc.postgresql.codec.PostgresqlDateTimeFormatter.parse(PostgresqlDateTimeFormatter.java:103)

解决方案 #

• 自定义Codec处理极值时间
  实现R2JDBC的Codec接口，针对时间类型（如LocalDateTime、OffsetDateTime）添加对infinity和-infinity的判断逻辑：

  import io.r2dbc.postgresql.codec.Codec;
  import io.r2dbc.postgresql.codec.PostgresqlColumnMetadata;
  import io.r2dbc.postgresql.message.Format;
  import reactor.core.publisher.Mono;
  import java.time.LocalDateTime;
  import java.util.List;
  
  public class InfinityTimestampCodec implements Codec<LocalDateTime> {
  
      @Override
      public boolean canDecode(PostgresqlColumnMetadata metadata, Format format, Class<?> type) {
          return type == LocalDateTime.class && "timestamp".equals(metadata.getType().getName());
      }
  
      @Override
      public Mono<LocalDateTime> decode(PostgresqlColumnMetadata metadata, io.r2dbc.postgresql.codec.Value value, Format format, Class<? extends LocalDateTime> type) {
          String text = value.asString();
          if ("infinity".equals(text)) {
              return Mono.just(LocalDateTime.MAX);
          } else if ("-infinity".equals(text)) {
              return Mono.just(LocalDateTime.MIN);
          }
          // 复用原有解析逻辑
          return Mono.just(LocalDateTime.parse(text));
      }
  
      @Override
      public boolean canEncode(Object value) {
          return value instanceof LocalDateTime;
      }
  
      @Override
      public Object encode(Object value, PostgresqlColumnMetadata metadata) {
          LocalDateTime ldt = (LocalDateTime) value;
          if (ldt.equals(LocalDateTime.MAX)) {
              return "infinity";
          } else if (ldt.equals(LocalDateTime.MIN)) {
              return "-infinity";
          }
          return ldt.toString();
      }
  
      @Override
      public List<Class<?>> getTypes() {
          return List.of(LocalDateTime.class);
      }
  }
  

  然后在初始化R2DBC连接时注册该Codec：

  ConnectionFactory connectionFactory = ConnectionFactories.get(ConnectionFactoryOptions.builder()
          .option(ConnectionFactoryOptions.DRIVER, "postgresql")
          .option(ConnectionFactoryOptions.HOST, "your-host")
          .option(ConnectionFactoryOptions.PORT, 5432)
          .option(ConnectionFactoryOptions.DATABASE, "your-db")
          .option(ConnectionFactoryOptions.USER, "user")
          .option(ConnectionFactoryOptions.PASSWORD, "password")
          .option(PostgresqlConnectionFactoryProvider.CODECS, List.of(new InfinityTimestampCodec()))
          .build());
  

• SQL查询层面转换极值
  在查询时通过CASE语句将infinity/-infinity替换为Java时间API可解析的极值：

  SELECT 
      CASE 
          WHEN your_timestamp_col = 'infinity' THEN '9999-12-31 23:59:59'
          WHEN your_timestamp_col = '-infinity' THEN '0001-01-01 00:00:00'
          ELSE your_timestamp_col::text 
      END AS your_timestamp_col
  FROM your_table;
  

• 升级R2JDBC PostgreSQL驱动版本
  部分较新版本的r2dbc-postgresql驱动已原生支持infinity时间戳解析，可尝试升级至最新稳定版（如1.0.4.RELEASE及以上），查看问题是否解决。

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