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 ?

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("-") }` — sebszyller, Jul 26 '16 at 13:22
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, Jul 26 '16 at 13:28

score 5 · Answer 1 · answered Mar 31 '17 at 03:51

5

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"

answered Mar 31 '17 at 03:51

Eron Wright

1,022
12
10

why the `Any*` has a `*`; and can one use it like this: `t("the string values")` – soMuchToLearnAndShare Dec 05 '21 at 13:37
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 Dec 05 '21 at 13:53
note: try not to use java.sql.Timestamp (old legacy type), instead use the new one, see https://stackoverflow.com/questions/42766674/convert-java-time-instant-to-java-sql-timestamp-without-zone-offset – soMuchToLearnAndShare Dec 05 '21 at 22:32

flavian · Accepted Answer · 2016-07-26T14:00:58.250

2

 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.

edited Jul 26 '16 at 14:00

answered Jul 26 '16 at 13:41

flavian

28,161
11
65
105

It's seems that "toOption" is not a member of org.joda.time.DateTime – Clement Cuvillier Jul 26 '16 at 13:53
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. – flavian Jul 26 '16 at 13:55
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[Long].singleOpt)` The error is: [PSQLException : Error: The operator doesn't exist : date = character varying] – Clement Cuvillier Jul 26 '16 at 14:19
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? – flavian Jul 28 '16 at 09:33

score 1 · Answer 3 · answered Jul 26 '16 at 13:54

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")

score 1 · Answer 4 · answered Jul 19 '21 at 12:10

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)

score 0 · Answer 5 · answered Aug 16 '19 at 10:03

0

Just use Timestamp.valueOf and String.replaceAll to fix the slashes. You also need to have add the time (just fix it to midnight, or whatever you want):

Timestamp.valueOf("2015/06/08".replaceAll("/", "-") + " 00:00:00")

answered Aug 16 '19 at 10:03

Paul

1,939
1
18
27

Scala - Convert a string to a timestamp

5 Answers5