Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

java.time.Instant to MySQL timestamp to java.time.Instant?  RSS feed

 
Daan Heuvelbeuk
Ranch Hand
Posts: 85
MySQL Database Netscape Windows XP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm working with instants which I store as MySQL timestamp. When I read the timestamp from the database I want to compare it to now (Instant timestamp = Instant.now()). Instant has a compareTo(Instant otherInstant) facility, but then I need to convert the MySQL timestamp to an Instant. Is that possible?

FWIW:
  • MySQL timestamp format is '2017-05-01 11:45:02'
  • java.time.Instant timestamp format is '2017-05-01T11:45:02'


  • I removed the T from the Instant timestamp to be able to store the timestamp in the database. So it is no problem to get the T back into the string. But then from string to instant ...
     
    Knute Snortum
    Sheriff
    Posts: 3545
    85
    Chrome Eclipse IDE Java Postgres Database VI Editor
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Does this site help?  I found it by googling java 8 date to instant.
     
    Paul Clapham
    Sheriff
    Posts: 22185
    38
    Eclipse IDE Firefox Browser MySQL Database
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    You seem to be doing string-fiddling to convert between java.sql.Timestamp and java.time.Instant. There's no need to do that: Timestamp has a static method Timestamp.from(Instant) which converts an Instant object to a Timestamp object, and an instance method toInstant() which returns an Instant object.
     
    Daan Heuvelbeuk
    Ranch Hand
    Posts: 85
    MySQL Database Netscape Windows XP
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Paul Clapham wrote:You seem to be doing string-fiddling to convert between java.sql.Timestamp and java.time.Instant. There's no need to do that: Timestamp has a static method Timestamp.from(Instant) which converts an Instant object to a Timestamp object, and an instance method toInstant() which returns an Instant object.


    You are right that I've my times confused. However, when I write the timestamp from Instant to my database (field of type MySQL timestamp) I get the error:

    When I change back my code it works with a charm.


    Eventually I want to let Timestamp handle all my time calculations.

    And to answer my original question, I should use Instant.parse(dbTimeString);

    Knute Snortum wrote:Does this site help?  I found it by googling java 8 date to instant.


    Thanks Knute. I found this tutorial at Oracle: Trail: Date Time: Table of Contents
     
    Dave Tolls
    Ranch Hand
    Posts: 2504
    27
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    That error looks to me like you are trying to do a PreparedStatement.setString passing in an object of type nl.mycumpanie.tk.dao.TkTimestamp.
    Which looks wrong.

    What does that bit of code look like?

    You shouldn't have to worry about formats for any of these, if your column is a DATE or TIMESTAMP column (or equivalent), and you are setting it using PreparedStatement.setDate (or setTimestamp).
     
    Daan Heuvelbeuk
    Ranch Hand
    Posts: 85
    MySQL Database Netscape Windows XP
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Dave Tolls wrote:That error looks to me like you are trying to do a PreparedStatement.setString passing in an object of type nl.mycumpanie.tk.dao.TkTimestamp.
    Which looks wrong.

    What does that bit of code look like?

    You shouldn't have to worry about formats for any of these, if your column is a DATE or TIMESTAMP column (or equivalent), and you are setting it using PreparedStatement.setDate (or setTimestamp).


    As far as I see it, the java.time.Instant is ISO-8601 compliant, meaning it has the format "yyyy-MM-ddTHH:mm:sssZ". This is not compatible with the format required for the MySQL timestamp field, meaning "yyyy-MM-dd HH:mm:ss". I have to convert to and fro ;-)

    And the code I'm using is:
     
    Knute Snortum
    Sheriff
    Posts: 3545
    85
    Chrome Eclipse IDE Java Postgres Database VI Editor
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Did you write your own prepareStatement() method?  Can we see that?
     
    Dave Tolls
    Ranch Hand
    Posts: 2504
    27
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Also, what is TkTimestamp class?

    As for Instant, no it is not ISO-8601 compliant.  That is the toString() method, not the class itself.
    An Instant is represented by a long and possibly s few other bits and pieces.

    This pretty much confirms my suspicion, that you are setting Dates as Strings.
    DATE and TIMESTAMP columns should be set using Date and Timestamp objects.
     
    Daan Heuvelbeuk
    Ranch Hand
    Posts: 85
    MySQL Database Netscape Windows XP
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    @Knute: I used the DAO tutorial - the data layer as starting point of my application.



    @Dave: The TkTimestamp class is the "Model class which represents a row from the table 'tktimestamp'".

    And you are right about me setting Dates as Strings. I was under the impression I should do that. How does one set "DATE and TIMESTAMP columns [] using Date and Timestamp objects"?
     
    Dave Tolls
    Ranch Hand
    Posts: 2504
    27
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    You just pass the Date or Timestamp into the PreparedStatement.
    In this case, into the Object[] of values you seem to be using, rather than using that getTimestampString method.
     
    Daan Heuvelbeuk
    Ranch Hand
    Posts: 85
    MySQL Database Netscape Windows XP
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Dave Tolls wrote:You just pass the Date or Timestamp into the PreparedStatement.
    In this case, into the Object[] of values you seem to be using, rather than using that getTimestampString method.


    I changed the code so it now uses an Instant instead of a String. The result is supplied below the code:
     
    Daan Heuvelbeuk
    Ranch Hand
    Posts: 85
    MySQL Database Netscape Windows XP
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    And if needed, this is the script I used to create the table:
     
    Dave Tolls
    Ranch Hand
    Posts: 2504
    27
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Timestamp is a class in the java.sql package.

    This is what Paul was talking about above, using the methods on Timestamp (for example) to turn an Instant into a Timestamp.

    The JDBC code doesn't currently work with the newer java.date classes (like Instant).
     
    Daan Heuvelbeuk
    Ranch Hand
    Posts: 85
    MySQL Database Netscape Windows XP
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I tried to change my code so it would return a java.sql.Timestamp to the database. But alas, I get a NullPointerException.



    This solution (line 155) is what I found searching on the Internet (Java - Convert java.time.Instant to java.sql.Timestamp without Zone offset). What would an appropriate way be to convert my Instant to java.sql.Timestamp?
     
    Knute Snortum
    Sheriff
    Posts: 3545
    85
    Chrome Eclipse IDE Java Postgres Database VI Editor
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I suspect this: ...is returning null.  Try setting a local variable and logging the value of it.
     
    Dave Tolls
    Ranch Hand
    Posts: 2504
    27
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    OK, for some reason I thought your timestamp or whatever was already an Instant.
    There's no point creating an Instant to then just turn it into a Timestamp.

    If this were my code, barring not having a model where dates/times are represented by Strings, I would use a SimpleDateFormat instance to parse thatString to a java.util.Date, and then turn that into a Timestamp.
     
    Paul Clapham
    Sheriff
    Posts: 22185
    38
    Eclipse IDE Firefox Browser MySQL Database
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Why isn't the system default ZoneId what you want? If it is what you want, then instead of



    instead you use

     
    Daan Heuvelbeuk
    Ranch Hand
    Posts: 85
    MySQL Database Netscape Windows XP
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Paul Clapham wrote:Why isn't the system default ZoneId what you want? If it is what you want, then instead of



    instead you use



    ZoneId.systemDefault(); seemed to be the solution.

    My logging is now:

    And my database now contains 2017-05-04 10:59:46

    It was the first time I worked with ZoneId, so I did not know (find, see) the option systemDefault();

    Thanks all for the help.
     
    Paul Clapham
    Sheriff
    Posts: 22185
    38
    Eclipse IDE Firefox Browser MySQL Database
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Daan Heuvelbeuk wrote:It was the first time I worked with ZoneId, so I did not know (find, see) the option systemDefault();


    Yeah, I know what you mean. When I converted a fairly large application to use the new date/time classes I found it very difficult to figure out how to work with them. My first try produced some pretty convoluted code which only later got streamlined into something less eye-hurting. Just like yours in fact.
     
    Dave Tolls
    Ranch Hand
    Posts: 2504
    27
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I still say creating an Instant for the sole purpose of converting it to a Timestamp is convoluted, but then again, it does seem to be shorter, so maybe I'm just getting old!
     
    It is sorta covered in the JavaRanch Style Guide.
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!