Scala - Convert a string to a timestamp

Question

I'm working with PostgreSQL and PlayFramework, and I need to convert an Option[String] to a Timestamp for execute my query.

My Option[String] is a date in that format: 2016/07/26

And the format of the timestamp is: 2016-07-26

I know I can add to_stimetamp(myDate, 'YYYY-MM-DD') to my query but I don't want use that (actually I don't know if I will use MySQL or PostgreSQL later an that function doesn't exist in MySQL)

So how can I do ?

Solution 1

 import org.joda.time.DateTime
 import org.joda.time.format.DateTimeFormat
 import scala.util.Try

 object Test extends App {
   val sourceFormat = DateTimeFormat.forPattern("yyyy/MM/dd")
   val targetFormat = DateTimeFormat.forPattern("yyyy-MM-dd")

   def parse(str: String): Option[DateTime] = {
     Try(DateTime.parse(str, sourceFormat)).toOption
   }

   val input = "2015/07/11"
   val maybeDate = parse(input)
   println(maybeDate.map(_.toString(targetFormat)))
   // Some("2015-07-11")
 }

It's also more efficient to use the parse method together with a DateTimeFormat that was pre-computed, otherwise the format will be validated every single time. If you call toString, internally that will also attempt to rebuild a DateTimeFormat from a String once more, which is inefficient.

Unlike the other answer, I would suggest that you never use DateTime.parse("2016/07/26"), e.g. don't pass a String argument as there's no internal format cache.

Further more Option.map doesn't catch errors that can be thrown by DateTime.parse, so if you only use Option it will blow up at runtime with an error if the string doesn't parse, instead of properly returning a None. Using a Try solves that problem.

Solution 2

Here's an implicit conversion for timestamp literals.

import java.sql.Timestamp
import java.time.Instant

object util {
  implicit class StringTimeConversions(sc: StringContext) {
    def t(args: Any*): Timestamp =
      Timestamp.from(Instant.parse(sc.s(args:_*)))
  }
}

import util._
val time: Timestamp = t"2007-01-01T00:00:00.00Z"

Solution 3

If you are using joda DateTime then you can simply call parse method like this:

DateTime.parse("2016/07/26")

If string is wrapped in Option then you can simply use this:

val d = Option("2016/07/26")
d.map(dt => DateTime.parse(d))

Or else if you are on java.util.Date then you may use SimpleDateFormat like this:

val f = new java.text.SimpleDateFormat("yyyy-MM-dd")
f.parse("2016/07/26")

Solution 4

Use the java.time.ZonedDateTime. Define your own format and parse the timestamp.

First the required imports :

import java.time.ZonedDateTime
import java.time.format.DateTimeFormatter

The below code will return a ZonedDateTime which is a more generic format to work with -

        val tsFormat = "yyyy-MM-dd'T'HH:mm:ssz"
        val formatter = DateTimeFormatter.ofPattern(tsFormat)
        // sample date 2021-05-07T00:00:00+00:00
        val startTs = ZonedDateTime.parse(dateString, formatter)

View more solutions

Comments

[sebszyller]

Is the type of timestamp a String as well or a custom class? If the former you could just e.g. def format(opt: Option[String]): String = { val str = opt getOrElse "some/default/date" str.split('/') mkString("-") }

[Kiran Krishna Innamuri]

are you asking how to do it in scala?

[Clement Cuvillier]

Yes i'm askin how can I do in scala, at the end I want that my query work with PostgreSQL and MySQL so I need to convert it "in Scala" before I launch my query

[Clement Cuvillier]

It's seems that "toOption" is not a member of org.joda.time.DateTime

[flavian]

The toOption call is on the Try, make sure you are copying everything. I've added the top level import for scala.util.Try to hopefully clear everything up.

[Clement Cuvillier]

I try it in my query but it doesn't work, here is my query: SQL("""select id from intervalle_date where date_debut={date_debut}""".stripMargin).on( 'date_debut -> startDateParsed.map(_.toString(targetFormat))).as(scalar[Lon‌​g].singleOpt) The error is: [PSQLException : Error: The operator doesn't exist : date = character varying]

[flavian]

Hi @Clement.Cvl That are looks different and I don't know which SQL library you are using to be honest. Can you paste the resulting SQL string?

[soMuchToLearnAndShare]

why the Any* has a *; and can one use it like this: t("the string values")

[soMuchToLearnAndShare]

i understand a bit more regarding to how to use the extension method t, after reading the StringContext class docString. It has examples of parse json string to json object. but I am still not sure why the Any* as the args type.

[soMuchToLearnAndShare]

note: try not to use java.sql.Timestamp (old legacy type), instead use the new one, see stackoverflow.com/questions/42766674/…