CODEWITHSUNDEEP
javamysqldatetimetimezone
Nick
Nick

Reputation: 11

When adding a timestamp to database it will only set the currenttime, ignoring values

So I am trying to save a timestamp to my database, but when adding it for some reason it will set the current time ignoring the value of the timestamp variable. Code:

try (PreparedStatement statement = connection.prepareStatement("INSERT INTO joinMoment (userId, moment) VALUES (?, ?);")) {
   statement.setString(1, id);
   System.out.println("DBTimeStamp: " + timestamp);
   statement.setTimestamp(2, timestamp);
   statement.executeUpdate();
}

The debug message does print the right time: DBTimeStamp: 2020-09-11 19:53:20.454 But what gets added to the database is: 2020-09-11 17:53:20 (Current time when adding)

Upvotes: 1

Views: 86

Answers (2)

O. Jones
O. Jones

Reputation: 108641

Time discrepancies of integral numbers of hours or half-hours point to time zone confusion.

If you use this query instead of the one in your question you don't have to send a timestamp to the database server. It will handle everything for you.

INSERT INTO joinMoment (userId, moment) VALUES (?, NOW() );

If your moment column is a TIMESTAMP, it will receive the UTC time. When you retrieve it MySQL will translate it from UTC to the time in the timezone setting of your connection or server.

If it's a DATETIME column it will receive the local time in the timezone of your connection or server.

If you DO send the timestamp from your Java program to the server, keep this in mind: the local timezone of your Java program should be set to the same as your MySQL connection or server timezone. If you want to send the server UTC timestamps, first issue this SQL command to set the timezone on your connection.

SET time_zone = 'UTC';

If you don't MySQL will unhelpfully convert timezones for you.

This distinction between TIMESTAMP and DATETIME is specific to MySQL. Other makes and models of table servers handle this stuff in other ways.

If you want those milliseconds to be stored, declare your moment column as either TIMEZONE(3) or DATETIME(3).

Upvotes: 1

Deviprasad Sharma
Deviprasad Sharma

Reputation: 526

I am assuming that you are living in a country with time UTC +2.00 So you need to convert the timestamp first and then store it

You can refer this link -> Convert UTC java.sql.Time to java.time.localtime with correct DST

Upvotes: 0

Related Questions