2

I want to add UTC date and time to my mysql database as timestamp. Previously I tried using SimpleDateFormat as:

 //UTC based issued date and expiry date SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); sdf.setTimeZone(TimeZone.getTimeZone("UTC")); final String currentUtc= sdf.format(new Date()); SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date currentDate = (Date) simpleDateFormat.parse(currentUtc); //java.util.date

It worked but I found that use of SimpleDateFormat is not recommendated. So now I am using Instant date time api as:

 Instant currentInstant = Instant.now().truncatedTo(ChronoUnit.SECONDS); //gives UTC datetime

which is correctly giving me current time in UTC. The problem is then when I add this instant to my database it gets converted to local date by adding my UTC time offset to that instant which I don't want. 

 //adding UTC time to database but not working ps.setTimestamp(1, Timestamp.from(currentInstant));

So how to add UTC date without adding UTC time offset to my database? Thank you in advance.

Improve this question
2
  • What MySQL datatype are you saving to? I don’t have the experience, but I would expect timestamp to work better than datetime for UTC datetimes. Commented May 21, 2019 at 4:53
  • 1
    i am saving to Timestamp datatype in my mysql database Commented May 21, 2019 at 4:55

2 Answers 2

Reset to default
1

JDBC 4.2

 ps.setObject(1, currentInstant.atOffset(ZoneOffset.UTC));

Since JDBC 4.2 we can directly pass java.time objects to and from JDBC objects like PreparedStatement and ResultSet. Perhaps surprisingly we should no longer use setTimestamp or similar specific methods, but just setObject and getObject. In this way they have managed to add new functionality without changing the API specification (the methods setObject and getObject were already there).

Some JDBC drivers accept just ps.setObject(1, currentInstant);, which is logical. I don’t remember whether Connector/J for MySQL does. In any case, it is not specified in the JDBC standard, which requires an OffsetDateTime rather than an Instant. So I’d use the longer form above, which converts to OffsetDateTime, if only for portability.

And congrats on leaving SimpleDateFormat, Date and Timestamp behind. They had their time, but were all always poorly designed, so I agree in not using them.

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

2 Comments

i am getting this error: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '\xAC\xED\x00\x05sr\x00\x0Djava.time.Ser\x95]\x84\xBA\x1B"H\xB2\x0C\x00\x00xpw\x0B\x0A\x00\x00\x07\xE3\x05\x15\x05\x01\xF8\x00x' for column
That’s too bad. I cannot immediately say why. I tried pasting the error message into my search engine, but it seems it’s mostly finding examples of where the message came with the old date-time classes. You may try searching deeper.
0

You are doing correctly. It happened because Mysql Database automatically converts UTC DateTime to system timezone. If you really want UTC DateTime then you can set the timezone of your MySQL database as 

SET TIME_ZONE='+00:00';
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.