ZonedDateTime won't parse seconds from db (Long TimeZone)

[fokot]

We should be able to parse seconds as well, otherwise it fails:

java.time.format.DateTimeParseException:` Text '1900-01-01 06:55:25+06:55:25' could not be parsed, unparsed text found at index 25
        at java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:1952)
        at java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1851)
        at java.time.ZonedDateTime.parse(ZonedDateTime.java:597)
        at com.github.tminglei.slickpg.PgDate2Support$Date2DateTimeFormatters.$anonfun$fromZonedDateTimeOrInfinity$1(PgDate2Support.scala:79)
        at com.github.tminglei.slickpg.PgDate2Support$Date2DateTimeFormatters.$anonfun$fromInfinitable$1(PgDate2Support.scala:69)
        at com.github.tminglei.slickpg.utils.PgCommonJdbcTypes$GenericJdbcType.getValue(PgCommonJdbcTypes.scala:22)
        at slick.jdbc.SpecializedJdbcResultConverter$$anon$2.read(SpecializedJdbcResultConverters.scala:45)
        at slick.jdbc.SpecializedJdbcResultConverter$$anon$2.read(SpecializedJdbcResultConverters.scala:43)
        at slick.relational.ProductResultConverter.read(ResultConverter.scala:53)
        at slick.relational.ProductResultConverter.read(ResultConverter.scala:43)
        at slick.relational.TypeMappingResultConverter.read(ResultConverter.scala:133)
        at slick.jdbc.JdbcInvokerComponent$QueryInvokerImpl.extractValue(JdbcInvokerComponent.scala:36)
        at slick.jdbc.StatementInvoker$$anon$1.extractValue(StatementInvoker.scala:66)
        at slick.jdbc.PositionedResultIterator.fetchNext(PositionedResult.scala:176)
        at slick.util.ReadAheadIterator.update(ReadAheadIterator.scala:28)
        at s

One can simulate this issue like

• run server in Singapore timezone (-Duser.timezone=Asia/Singapore) and store date 1900-01-01-00:00:00
• try to read it - now it fails

Singapore really had timezone UTC+6:55:25 plus 6 hour 55 minutes and 25 seconds in 1900 :D https://www.timeanddate.com/time/zone/singapore/singapore

[tminglei]

@fokot this is the date formatter definition in PgDate2Support:

    val date2TzDateTimeFormatter =
      new DateTimeFormatterBuilder()
        .append(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
        .optionalStart()
        .appendFraction(ChronoField.NANO_OF_SECOND,0,6,true)
        .optionalEnd()
        .appendOffset("+HH:mm","+00")
        .toFormatter()

Here the offset was set with format +HH:mm.

You can try to override it with +HH:mm:ss.

[fokot]

I resolved it by moving to OffsetDateTime. I wanted to override it but didn't find a way how, it is hard accessible when I just subclass it like

trait ExtendedPostgresProfile extends ExPostgresProfile
  with PgArraySupport
  with PgJsonSupport
  with PgCirceJsonSupport
  with PgDate2Support
  //  with PgRangeSupport
  //  with PgHStoreSupport
  //  with PgSearchSupport
  //  with PgNetSupport
  //  with PgLTreeSupport
{

[tminglei]

try to do it like this:

trait MyPostgresProfile extends ExPostgresProfile
                          with PgArraySupport
                          with PgDateSupport
                          with PgDate2Support
                          with PgJsonSupport
                          with PgNetSupport
                          with PgLTreeSupport
                          with PgRangeSupport
                          with PgHStoreSupport
                          with PgSearchSupport {

  override val pgjson = "jsonb"
  ///
  override val api: API = new API {}

  trait API extends super.API with ArrayImplicits
                              with SimpleDateTimeImplicits
                              with DateTimeImplicits
                              with SimpleJsonImplicits
                              with NetImplicits
                              with LTreeImplicits
                              with RangeImplicits
                              with HStoreImplicits
                              with SearchImplicits
                              with SearchAssistants {
    override val date2TzDateTimeFormatter =
      new DateTimeFormatterBuilder()
        .append(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
        .optionalStart()
        .appendFraction(ChronoField.NANO_OF_SECOND,0,6,true)
        .optionalEnd()
        .appendOffset("+HH:mm:ss","+00")
        .toFormatter()

  }
}

[asdcdow]

Unfortunately, the offset pattern +HH:mm:ss isn't supported. We only get +HH:MM:ss which lets the timezone offset have 2 or 3 sections but not 1. I think to parse this in a way that won't produce errors, we need to handle all 3 cases.

[asdcdow]

I ran in to the same problem when using PST as a time zone for dates before 1883 (see here for more details). I was able to get past this with the following workaround:

trait MyPostgresProfile extends ExPostgresProfile
                         with PgArraySupport
                         with PgDateSupport
                         with PgDate2Support
                         with PgJsonSupport
                         with PgNetSupport
                         with PgLTreeSupport
                         with PgRangeSupport
                         with PgHStoreSupport
                         with PgSearchSupport {

 override val pgjson = "jsonb"
 ///
 override val api: API = new API {}

 trait API extends super.API with ArrayImplicits
                             with SimpleDateTimeImplicits
                             with DateTimeImplicits
                             with SimpleJsonImplicits
                             with NetImplicits
                             with LTreeImplicits
                             with RangeImplicits
                             with HStoreImplicits
                             with SearchImplicits
                             with SearchAssistants {
   val date2TzDateTimeWithLongTzFormatter = new DateTimeFormatterBuilder()
       .append(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
       .optionalStart()
       .appendFraction(ChronoField.NANO_OF_SECOND,0,6,true)
       .optionalEnd()
       .appendOffset("+HH:MM:ss","+00")
       .toFormatter()
   override protected val fromZonedDateTimeOrInfinity: String => ZonedDateTime = fromInfinitable(
     LocalDateTime.MAX.atZone(ZoneId.of("UTC")), LocalDateTime.MIN.atZone(ZoneId.of("UTC")),
     input => Try(ZonedDateTime.parse(input, date2TzDateTimeFormatter))
         .getOrElse(ZonedDateTime.parse(input, date2TzDateTimeWithLongTzFormatter))
   )
   override implicit val date2TzTimestamp1TypeMapper: JdbcType[ZonedDateTime] = new GenericJdbcType[ZonedDateTime]("timestamptz",
       fromZonedDateTimeOrInfinity, toZonedDateTimeOrInfinity, hasLiteralForm=false)
 }
}

You may need to do a similar override for toZonedDateTimeOrInfinity if you want to persist dates with the longer time zones.

I wasn't able to make the previously suggested solution work because it appears to be impossible to produce a single DateTimeFormatter that can parse both offsets like +08 and offsets like +08:00:00.

[tminglei]

👍