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

Question

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)

O. Jones · Accepted Answer · 2020-09-11 16:39:29Z

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).

Deviprasad Sharma · Accepted Answer · 2020-09-11 16:17:59Z

0

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

answered Sep 11, 2020 at 16:17

Deviprasad Sharma

5361 gold badge8 silver badges23 bronze badges

1 Comment

Nick Over a year ago

Oh thank you, I didn't know it was stored as UTC time!

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related