在Java中使用时区将日期转换为时间戳 [英] Converting date to timestamp with timezone in Java
问题描述
我有一个PostgreSQL数据库,其列定义为时间戳记
I have a PostgreSQL database with a column defined as timestamp
我收到的时区格式为yyyy-MM-ddTHH:mm:ss.SSSX,例如 2020-12-16T15:05:26.507Z
.如果我要将其插入带有时间戳的列中,则会抛出"时间戳格式必须为yyyy-mm-dd hh:mm:ss"
I am receiving the date with timezone format yyyy-MM-ddTHH:mm:ss.SSSX", for example 2020-12-16T15:05:26.507Z
. If I want to insert this into a column with timestamp it will throw "Timestamp format must be yyyy-mm-dd hh:mm:ss"
我在做
Timestamp.valueOf("2020-12-16T15:05:26.507")
现在,时区日期来自JSON,所以我现在将其作为字符串.
Now the timezone date comes from a JSON, so I am taking it as a string string for now.
如何将其转换为简单的时间戳格式?到2020-12-16 15:05:26
How do I go about converting this to simple Timestamp format? to 2020-12-16 15:05:26
推荐答案
下表总结了 PostgreSQL
列类型与 Java SE 8
日期-时间类型的映射:
The following table summarizes the PostgreSQL
column type mapping with Java SE 8
date-time types:
--------------------------------------------------
PostgreSQL Java SE 8
==================================================
DATE LocalDate
--------------------------------------------------
TIME [ WITHOUT TIMEZONE ] LocalTime
--------------------------------------------------
TIMESTAMP [ WITHOUT TIMEZONE ] LocalDateTime
--------------------------------------------------
TIMESTAMP WITH TIMEZONE OffsetDateTime
--------------------------------------------------
请注意,不支持 ZonedDateTime
, Instant
和 OffsetTime
/ TIME [WITHTIME TIMEZONE]
.另外,请注意,所有 OffsetDateTime
实例都必须位于 UTC
中(其时区偏移量为 +00:00
小时).这是因为后端将它们存储为 UTC
.
Note that ZonedDateTime
, Instant
and OffsetTime
/ TIME [ WITHOUT TIMEZONE ]
are not supported. Also, note that all OffsetDateTime
instances will have to be in UTC
(which has a time zone offset of +00:00
hours). This is because the backend stores them as UTC
.
因此,有两种选择.
将列类型更改为 TIMESTAMP WITH TIMEZONE
.推荐这样做是因为您的日期时间字符串具有 Z
,代表 Zulu
date-time或 UTC
date-time.使用 OffsetDateTime
,您可以解析此日期时间字符串,而无需明确地任何 DateTimeFormatter
.
Change the column type to TIMESTAMP WITH TIMEZONE
. This is recommended because your date-time string has Z
which stands for Zulu
date-time or UTC
date-time. Using OffsetDateTime
, you can parse this date-time string without requiring any DateTimeFormatter
explicitly.
演示:
import java.time.OffsetDateTime;
public class Main {
public static void main(String[] args) {
OffsetDateTime odt = OffsetDateTime.parse("2020-12-16T15:05:26.507Z");
System.out.println(odt);
}
}
输出:
2020-12-16T15:05:26.507Z
下面给出的示例是如何将此 OffsetDateTime
用于数据库
Given below is an example of how to use this OffsetDateTime
for DB CRUD operations:
OffsetDateTime odt = OffsetDateTime.parse("2020-12-16T15:05:26.507Z");
PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");
st.setObject(1, odt);
st.executeUpdate();
st.close();
选项-2:
如果您仍然希望将列类型保持为 TIMESTAMP [WITHTIME TIMEZONE]
,则可以从 OffsetDateTime
获取 LocalDateTime
并使用如下所示:
Option - 2:
If you still want to keep the column type as TIMESTAMP [ WITHOUT TIMEZONE ]
, you can get the LocalDateTime
from OffsetDateTime
and use the same as shown below:
OffsetDateTime odt = OffsetDateTime.parse("2020-12-16T15:05:26.507Z");
LocalDateTime ldt = odt.toLocalDateTime();
PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");
st.setObject(1, ldt);
st.executeUpdate();
st.close();
这篇关于在Java中使用时区将日期转换为时间戳的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!