how to insert interval value to postgres using scala

I am using the below code to insert data to interval data type of a postgres table but I am getting some error.

var tm1 =  LocalDateTime.now()
var tm2 =  LocalDateTime.now()

var milliseconds = tm1.until(tm2, ChronoUnit.MILLIS)

var seconds = (milliseconds / 1000) % 60
var minutes = (milliseconds / (1000*60)) % 60
var hours   = (milliseconds / (1000*60*60)) % 24
var milisec = milliseconds - ((seconds * 1000) + (minutes * 60 * 1000) + (hours * 60 *60 * 1000))

var secondsNew = if (seconds.toString().length() == 1) "0" + seconds else seconds
var minutesNew = if (minutes.toString().length() == 1) "0" + minutes else minutes
var hoursNew = if (hours.toString().length() == 1) "0" + hours else hours
var milisecNew = if (milisec.toString().length() == 1) milisec + "00"  else if (milisec.toString().length() == 2)  milisec + "0" else milisec

var timeString = "%s:%s:%s".format(hoursNew, minutesNew, secondsNew)
var duration = LocalTime.parse(timeString)

var tm = Time.valueOf(DateTimeFormatter.ofPattern("HH:mm:ss").format(duration))

==> insertion

sql = "INSERT into etl.mls_refresh_snapshot(job_name,job_type,job_frequency,
source_table_name,source_count,target_count,job_run_duration,started_at,ended_at) VALUES(?,?,?,?,?,?::interval,?,?)"

var statement : PreparedStatement = conn.prepareStatement(sql)
statement.setTime(6,Time.valueOf(DateTimeFormatter.ofPattern("HH:mm:ss.SSS").format(duration)))
.......
Error :: java.lang.NumberFormatException: For input string: "18.000"

If I change the format to miliseconds =>

var timeString = "%s:%s:%s.%s".format(hoursNew, minutesNew, secondsNew, milisecNew)
var duration = LocalTime.parse(timeString)
var tm = Time.valueOf(DateTimeFormatter.ofPattern("HH:mm:ss.SSS").format(duration))

I am getting ERROR: invalid input syntax for type interval: "00:02:07+05:30"

Please help me out on this !!!

Solution

Seems like you are using JDBC without any library. If that is the case, the postgresql JDBC driver has the class PGInterval.

Instead of setTime, you have to use setObject and pass an instance of PGInterval as the parameter. Based on your example, you should write something like

val sql = "INSERT into etl.mls_refresh_snapshot(job_name, job_type, job_frequency, source_table_name, source_count, target_count, job_run_duration, started_at, ended_at)"

val statement : PreparedStatement = conn.prepareStatement(sql)

val pgInterval = new PGInterval(1,1,1,1,1,1,1)
statement.setObject(6, pgInterval)

In case you need to to a select and get the value of that column, you have to do the following

val result = statement.executeQuery("SELECT job_name, job_type, job_frequency, source_table_name, source_count, target_count, job_run_duration, started_at, ended_at FROM etl.mls_refresh_snapshot")
result.next()
val interval: PGInterval = result.getObject(6, classOf[PGInterval])

Here you have basic but complete example of how to create a table, insert an interval value and fetch it from the table

build.sbt

libraryDependencies ++= Seq(
  "com.dimafeng" %% "testcontainers-scala-scalatest" % "0.41.0" % Test,
  "com.dimafeng" %% "testcontainers-scala-postgresql" % "0.41.0" % Test,
  "org.postgresql" % "postgresql" % "42.5.4"
)

src/test/scala/PostgresqlContainerTest.scala

import com.dimafeng.testcontainers.PostgreSQLContainer
import com.dimafeng.testcontainers.scalatest.TestContainerForAll
import org.postgresql.util.PGInterval
import org.scalatest.funsuite.AsyncFunSuite
import org.scalatest.matchers.should.Matchers
import org.testcontainers.utility.DockerImageName

import java.sql.{DriverManager, PreparedStatement, Statement}

class PostgresqlContainerTest
    extends AsyncFunSuite
    with Matchers
    with TestContainerForAll {

  override val containerDef =
    PostgreSQLContainer.Def(DockerImageName.parse("postgres:15.4-alpine3.18"))

  test("CRUD for interval type column") {
    withContainers { pgContainer =>
      // load driver
      Class.forName(pgContainer.driverClassName)

      // connect
      val connection = DriverManager.getConnection(
        pgContainer.jdbcUrl,
        pgContainer.username,
        pgContainer.password
      )

      // create table with interval column
      val stmt: Statement = connection.createStatement
      val sql = "CREATE TABLE interval_table (interval_column Interval)"
      stmt.executeUpdate(sql)

      val years = 1
      val months = 4
      val days = 3
      val hours = 3
      val minutes = 22
      val seconds = 42

      // create the PGInterval object
      val pgInterval: PGInterval =
        new PGInterval(years, months, days, hours, minutes, seconds)

      // insert into table with interval value
      val preparedStatement: PreparedStatement = connection.prepareStatement(
        "INSERT INTO interval_table(interval_column) VALUES (?)"
      )

      // set the value using `setObject` and using `PGInterval` as parameter
      preparedStatement.setObject(1, pgInterval)
      preparedStatement.executeUpdate()

      // query the record saved before
      val select = connection.createStatement()
      val result =
        select.executeQuery("SELECT interval_column FROM interval_table")
      result.next()

      // get the interval value from the column
      val interval: PGInterval = result.getObject(1, classOf[PGInterval])

      // assert the expected values
      interval.getYears should be(years)
      interval.getMonths should be(months)
      interval.getDays should be(days)
      interval.getHours should be(hours)
      interval.getMinutes should be(minutes)
      interval.getSeconds should be(seconds)
    }
  }
}