Same get query returns differnet results when DTS change occur

[kafkapre]

I am storing Items that have timestamp property with type java.time.Instant . I have also stored items that occurred during Daylight saving time (DST) switch (eg. 2019.03.31). I noticed that when I query items over interval when DST happened it returns me different results.

For example, I store items below:

Item(3,2019-03-31T01:59:38.848Z,12)
Item(4,2019-03-31T02:00:03.377Z,13)
Item(7,2019-03-31T02:59:55.770Z,16)
Item(8,2019-03-31T03:00:07.335Z,17)

Then the first five queries return me exactly same values as I stored.
But the sixth and next queries return me values below

Item(3,2019-03-31T01:59:38.848Z,12)
Item(4,2019-03-31T03:00:03.377Z,13) // this should have 2019-03-31T02:00:03.377Z
Item(7,2019-03-31T03:59:55.770Z,16) // this should have 2019-03-31T02:59:55.770Z
Item(8,2019-03-31T03:00:07.335Z,17)

I am using:
Java 11
Scala 2.13.1
Slick 3.3.2
Postgres 9.6

Why query returns me different results. How to fix it? Thanks.

I wrote a simple example to reproduce this issue.

create_table.sql --------------------------------------------

CREATE SCHEMA IF NOT EXISTS test_schema;

CREATE TABLE test_schema.items (
    id SERIAL NOT NULL PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL,
    value INT NOT NULL
);
CREATE INDEX items_timestamp_index ON test_schema.items (timestamp);

Item.scala ----------------------------------------------------

import java.time.Instant

case class Item(
    id: Long,
    timestamp: Instant,
    value: Int
)

SlickDal.scala -------------------------------------------------

import java.time.Instant

import akka.Done
import com.typesafe.config.Config
import org.slf4j.{Logger, LoggerFactory}
import slick.jdbc.PostgresProfile.api._
import slick.lifted.ProvenShape

import scala.collection.immutable
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.Future
import scala.util.control.NonFatal

object SlickDal {
  class ItemsTable(tag: Tag) extends Table[Item](tag, Some("test_schema"), "items") {
    def id = column[Long]("id")
    def timestamp = column[Instant]("timestamp")
    def value = column[Int]("value")
    def * : ProvenShape[Item] = (id, timestamp, value) <> ((Item.apply _).tupled, Item.unapply)
  }
}

class SlickDal(config: Config) {

  import SlickDal._

  private val log: Logger = LoggerFactory.getLogger(getClass)
  private val db = slick.jdbc.JdbcBackend.Database.forConfig("", config)
  private val table = TableQuery[ItemsTable]

  def storeItems(items: immutable.Seq[Item]): Future[Done] = {
    val insertActions = DBIO.seq {
      table ++= items
    }
    db.run(insertActions)
      .map(_ => Done)
      .recoverWith { case NonFatal(e) => log.error("items store failed", e); Future.failed(e) }
  }

  def getItems(from: Instant, to: Instant): Future[Vector[Item]] = {
    val query = table
      .filter(o => o.timestamp >= from && o.timestamp <= to)
      .sortBy(o => (o.timestamp, o.id))
      .result
    db.run(query)
      .map(_.toVector)
      .recoverWith { case NonFatal(e) => log.error("items fetch failed", e); Future.failed(e) }
  }

}

SlickDalTest.scala --------------------------------------------

import java.time.Instant
import akka.Done
import com.typesafe.config.ConfigFactory
import org.scalatest.FunSuite
import scala.concurrent.Await
import scala.concurrent.duration._

class SlickDalTest extends FunSuite {
  private val config = ConfigFactory.load()
  private val timeout = 5.minutes
  private val dwh = new SlickDal(config.getConfig("mydb"))

  private def storeItems(): Done = Await.result(dwh.storeItems(
    Vector(
      Item(1, Instant.parse("2019-03-31T01:59:04.138Z"), 10),
      Item(2, Instant.parse("2019-03-31T01:59:24.027Z"), 11),
      Item(3, Instant.parse("2019-03-31T01:59:38.848Z"), 12),
      Item(4, Instant.parse("2019-03-31T02:00:03.377Z"), 13),
      Item(5, Instant.parse("2019-03-31T02:00:05.682Z"), 14),
      Item(6, Instant.parse("2019-03-31T02:00:13.445Z"), 15),
      Item(7, Instant.parse("2019-03-31T02:59:55.770Z"), 16),
      Item(8, Instant.parse("2019-03-31T03:00:07.335Z"), 17),
      Item(9, Instant.parse("2019-03-31T03:00:14.392Z"), 18),
    )), timeout)

  test("demonstrate issue") {
    storeItems() // TODO comment this for second and later runs

    def getItems(i: Int): Unit = {
      val res = Await.result(dwh.getItems(
        Instant.parse("2019-03-31T01:55:00.683Z"),
        Instant.parse("2019-03-31T03:05:24.683Z"),
      ), timeout)

      println("")
      println(s"Round $i ---------------")
      res.foreach(println(_))
    }

    for (i <- 1 until 8) {
      getItems(i)
    }
  }

}