英文:
When adding a timestamp to database it will only set the currenttime, ignoring values
问题
所以我正在尝试将时间戳保存到我的数据库中，但在添加时，由于某种原因它会设置为当前时间，而忽略时间戳变量的值。
代码：
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();
}
调试消息确实打印出了正确的时间：DBTimeStamp: 2020-09-11 19:53:20.454
但是实际添加到数据库中的却是：2020-09-11 17:53:20（添加时的当前时间）
英文:
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)
答案1
得分: 1
时间差异为整数小时或半小时指向时区混淆。
如果您使用此查询而不是问题中的查询，则无需将时间戳发送到数据库服务器。它将为您处理一切。
INSERT INTO joinMoment (userId, moment) VALUES (?, NOW());
如果您的moment列是TIMESTAMP，它将接收到UTC时间。当您检索它时，MySQL将将其从UTC转换为您的连接或服务器的时区设置中的时间。
如果它是DATETIME列，它将接收到连接或服务器时区的本地时间。
如果您确实从Java程序发送时间戳到服务器，请记住：Java程序的本地时区应设置为与MySQL连接或服务器的时区相同。如果要发送服务器的UTC时间戳，请首先发出此SQL命令来设置连接的时区。
SET time_zone = 'UTC';
如果不这样做，MySQL将不会有帮助地为您转换时区。
TIMESTAMP和DATETIME之间的这种区别特定于MySQL。其他制造和型号的表服务器以其他方式处理这些内容。
如果要存储这些毫秒数，请将您的moment列声明为TIMEZONE(3)或DATETIME(3)。
英文:
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).
答案2
得分: 0
我假设你生活在一个UTC +2.00的时区国家。
因此，你需要首先转换时间戳，然后进行存储。
你可以参考这个链接 -> https://stackoverflow.com/questions/52966281/convert-utc-java-sql-time-to-java-time-localtime-with-correct-dst
英文:
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 -> https://stackoverflow.com/questions/52966281/convert-utc-java-sql-time-to-java-time-localtime-with-correct-dst
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论