0

I have two different formats for data and I am trying to have a common format.

Format A

"yyyy-MM-dd'T'HH:mm:ss.SSSXXX"

Sample time: 2020-09-10T22:40:58.807+00:00

Format B

"yyyy-MM-dd'T'HH:mm:ss.SSSSSSSX"

Sample time: 2020-07-23T02:46:04.0978382Z

I am trying to merge them into one common format and eventually use them for a SQL Dataframe with type Timestamp. I have been using SimpleDateFormat for format A, and for Format B I cannot parse with SimpleDateFormat as it doesn't support Nanoseconds, so instead have been using

val inFormat = DateTimeFormatter.ofPattern(timestampFormat)
val localDatetime = LocalDateTime.parse(timestampString, inFormat)
Timestamp.valueOf(localDatetime) // converting Datetime to timestamp

After using the above my Format B sample time is 2020-07-23 02:46:04.0978382 While format A using SimpleDateFormat is 2020-09-10 22:40:58.807

How can I create a common format for both A and B to be later used into a SQL table? My goal is for whats in format A matches what is in format B(granularity too).

Something that I am trying is using OffsetDateTime to convert both formats. With OffSetDateTime I will

import java.time.OffsetDateTime
val odt = OffsetDateTime.parse(2020-07-23T02:46:04.0978382Z)
println(odt)

In this case, seems like I would truncate offsetDatetime? Is this a good approach?

Thanks

nrvaller
  • 353
  • 6
  • 18
  • You don't need a common format, you need to try to parse both. You can try to parse it and catch an exception, or find Scala lib that will produce an option. – Artem Sokolov Sep 14 '20 at 20:38
  • 1
    I recommend you don’t use `Timestamp`. That class is poorly designed and long outdated. Instead pass either an `OffsetDateTime`, an `Instant` or a `LocalDateTime` to your SQL database. See [my answer here](https://stackoverflow.com/a/54907501/5772882). – Ole V.V. Sep 15 '20 at 00:58

2 Answers2

1

You haven’t got two different formats. What you call format A and format B are both variants of ISO 8601 format, the international standard. OffsetDateTime parses the most common ISO 8601 variants as its default, that is, without any explicit formatter. Also both of your variants.

    OffsetDateTime dateTimeA = OffsetDateTime.parse("2020-09-10T22:40:58.807+00:00");
    System.out.println(dateTimeA);
    OffsetDateTime dateTimeB = OffsetDateTime.parse("2020-07-23T02:46:04.0978382Z");
    System.out.println(dateTimeB);

Output:

2020-09-10T22:40:58.807Z
2020-07-23T02:46:04.097838200Z

You also notice that no values have been truncated.

You probably don’t need to convert to a java.sql.Timestamp. That’s good because the Timestamp class is a hack on top of the already poorly designed java.util.Date class, and both those classes are long outdated. Assuming that you need a timestamp with time zone in SQL (recommended for timestamps), since JDBC 4.2 pass your OffsetDateTime to SQL. An example:

    OffsetDateTime dateTimeA = OffsetDateTime.parse("2020-09-10T22:40:58.807+00:00");
    
    PreparedStatement insertStmt = yourDatabaseConnection.prepareStatement(
            "insert into your_table(your_timestamp_with_time_zone_column) values (?)");
    insertStmt.setObject(1, dateTimeA);
    int rowsInserted = insertStmt.executeUpdate();

Links

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
0

You don't need a "common" format.

You have 2 options:

  1. You can detect with regex what format your current input has.

Like:

def parse(inputString: String): Timestamp {
  if(isFormatA(inputString))
    parseFormatA(inputString)
  else 
    parseFromatB(inputString)
}

You can improve it with regex pattern matching.

  1. You can sequentially try both formats with exception handling or library that will return option.

Like:

def parse(inputString: String): Timestamp {
  Try(parseFormatA(inputString))
    .orElse(Try(parseFormatB(inputString)))
    .get
}
Artem Sokolov
  • 810
  • 4
  • 8