java to mysql. I need convert from string parametre to timestamp

Question

I'm trying to parser String to Timestamp because I need to save this data on bbdd mysql.

String dateString: "2018-10-17T22:37:10.000+0000";
java.sql.Timestamp timeStampDate = null;
try {
        DateFormat formatter;
        formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.SSS");
        Date date = (Date) formatter.parse(dateString);
        timeStampDate = new Timestamp(date.getTime());

    } catch (ParseException e) {
        log.debug("ERROR parser String to Timestamp to save bbdd. ", e.getMessage());
    }

When I run my app I get this catch message:

ERROR parser String to Timestamp to save bbdd. Unparseable date: "2018-10-17T22:37:10.000+0000"

Can anybody help me?

First, I recommend you avoid the SimpleDateFormat class. It is not only long outdated, it is also notoriously troublesome. Today we have so much better in java.time, the modern Java date and time API. Second, for saving into MySQL it’s probably better to save an Instant, an OffsetDateTime or a LocalDateTimethan a Timestamp (another poorly designed and outdated class). — Anonymous
– Anonymous, Commented Nov 14, 2018 at 14:09

Scary Wombat · Accepted Answer · 2018-11-14 23:49:20Z

2

change your mask to

DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss.SSS");

so you have

java.sql.Timestamp timeStampDate = null;
String dateString = "2018-10-17T22:37:10.000+0000";

try {
    DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSZ");
    Date date = formatter.parse(dateString);
    timeStampDate = new Timestamp(date.getTime());

} catch (ParseException e) {
    e.printStackTrace();
}

By the way you should not need to cast the Date

Apologies for my slackness, in my haste I did not test the output and as per @andreas comment, the correct mask is actually yyyy-MM-dd'T'HH:mm:ss.SSSZ

edited Nov 14, 2018 at 23:49

answered Nov 14, 2018 at 0:10

Scary Wombat

44.9k7 gold badges38 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kara Over a year ago

thanks @Scary. Now it is working. and yes, I dont need to cast the Date.

Andreas Over a year ago

Sorry, that is wrong. First, it's HH, not hh. Second, ignoring the +0 time zone offset means you get the wrong time, e.g. I'm on US east coast, so running that code would get me time 2018-10-17 22:37:10.000 EDT, which is not the same as time 2018-10-17T22:37:10.000+0000, because that would be 2018-10-17 18:37:10.000 EDT. Correct format string is yyyy-MM-dd'T'HH:mm:ss.SSSZ

Anonymous · Accepted Answer · 2018-11-14 14:30:21Z

java.time

    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("uuuu-MM-dd'T'HH:mm:ss.SSSXX");
    String dateString = "2018-10-17T22:37:10.000+0000";
    OffsetDateTime odt = OffsetDateTime.parse(dateString, formatter);

    System.out.println("Parsed datetime: " + odt);

Output from this code is:

Parsed datetime: 2018-10-17T22:37:10Z

For saving into MySQL it’s good to use a datetime object, but the Timestamp class has design problems and is now long outdated. I am sorry that I don’t have the experience with MySQL, but I think the following should work:

    PreparedStatement ps = myDatabaseConnection.prepareStatement(
            "insert into my_table (my_timestamp) values (?)");
    ps.setObject(1, odt);

Link: Oracle tutorial: Date Time explaining how to use java.time.

Collectives™ on Stack Overflow

java to mysql. I need convert from string parametre to timestamp

2 Answers 2

2 Comments

java.time

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

java.time

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related