PostgreSQL 日期类型
在前后端的数据传递过程中,日期类型是一种特殊的类型,因此需要单独介绍处理方法。
数据类型对应关系
TIMESTAMP WITH TIME ZONE-->OffsetDateTime,java.sql.Timestamp
TIMESTAMPTZ-->
Java OffsetDateTime
LocalDateTime expireTime = LocalDateTime.now().plusMinutes(15);
OffsetDateTime atOffset = expireTime.atOffset(ZoneOffset.UTC);
创建表
首先,在 PostgreSQL 中创建一个带有时区的时间戳的表,示例如下:
CREATE TABLE activity(
id BIGINT NOT NULL,
title VARCHAR(256),
start_time TIMESTAMP WITH TIME ZONE,
end_time TIMESTAMP WITH TIME ZONE,
PRIMARY KEY (id)
);
后端存储和查询
使用 OffsetDateTime
在后端,我们使用 Java 来处理和存储带有时区的时间戳。以下是一个示例:
package com.litongjava.website.service;
import java.sql.Timestamp;
import java.time.Instant;
import java.time.OffsetDateTime;
import java.time.ZoneId;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import org.junit.Test;
import com.litongjava.db.activerecord.Db;
import com.litongjava.db.activerecord.Row;
import com.litongjava.tio.boot.admin.config.TioAdminDbConfiguration;
import com.litongjava.tio.utils.environment.EnvUtils;
import com.litongjava.tio.utils.snowflake.SnowflakeIdUtils;
import lombok.extern.slf4j.Slf4j;
@Slf4j
public class ActivityServiceTest {
@Test
public void addActivity() {
// 连接数据库
EnvUtils.load();
new TioAdminDbConfiguration().config();
// 获取当前系统时区
ZoneId zoneId = ZoneId.systemDefault();
// 设置开始时间
long currentTimeMillis = System.currentTimeMillis();
Instant startInstant = Instant.ofEpochMilli(currentTimeMillis);
OffsetDateTime startTime = startInstant.atZone(zoneId).toOffsetDateTime();
// 设置结束时间
long endTimeMillis = System.currentTimeMillis() + (1000 * 60 * 60 * 24);
Instant endInstant = Instant.ofEpochMilli(endTimeMillis);
OffsetDateTime endTime = endInstant.atZone(zoneId).toOffsetDateTime();
// 创建记录
Row row = new Row().set("id", SnowflakeIdUtils.id()).set("title", "Test Activity").set("start_time", startTime).set("end_time", endTime);
boolean save = Db.save("activity", row);
log.info("save result:{}", save);
// SQL: insert into "activity"("id", "title", "start_time", "end_time") values(?, ?, ?, ?)
}
@Test
public void listActivity() {
// 连接数据库
EnvUtils.load();
new TioAdminDbConfiguration().config();
// 查询所有记录
List<Row> records = Db.findAll("activity");
List<Map<String, Object>> lists = records.stream().map(e -> e.toMap()).collect(Collectors.toList());
// 打印结果
for (Map<String, Object> map : lists) {
for (Map.Entry<String, Object> e : map.entrySet()) {
if (e.getValue() != null) {
log.info("{},{},{}", e.getKey(), e.getValue(), e.getValue().getClass().toString());
}
}
}
}
}
输出结果
start_time,2025-03-15 00:51:53.685,class java.sql.Timestamp
end_time,2025-03-16 00:51:53.685,class java.sql.Timestamp
输出类型为 class java.sql.Timestamp 是框架的问题吗?
输出类型为 java.sql.Timestamp
并不是框架的问题,而是 JDBC 驱动和 ORM 框架对 SQL 类型的映射方式。大多数情况下,SQL 中的 TIMESTAMP WITH TIME ZONE
类型在 JDBC 层面上可能默认映射为 java.sql.Timestamp
。这主要是出于兼容性和历史原因,很多框架和驱动在处理时间数据时倾向于使用 java.sql.Timestamp
,即使在底层数据库中存储的是带有时区信息的时间戳。
如果需要更精确地处理时区数据,可以在查询或数据转换时显式地将 java.sql.Timestamp
转换为 Java 8 及以上的 OffsetDateTime
或 ZonedDateTime
,以便更好地保留时区信息。这样做也能使代码更符合现代 Java 日期时间 API 的使用习惯。
总结:
- 不是框架的问题:这是 JDBC 类型映射的默认行为。
- 解决方案:如有需要,可以在应用层进行显式转换来处理时区数据。
Java 收到字符串 2025-03-01 00:28:03 如何存储到 表字段为 TIMESTAMP WITH TIME ZONE 的表中?
package com.litongjava.website.service;
import java.sql.Timestamp;
import org.junit.Test;
import com.litongjava.db.activerecord.Db;
import com.litongjava.db.activerecord.Row;
import com.litongjava.tio.boot.admin.config.TioAdminDbConfiguration;
import com.litongjava.tio.utils.environment.EnvUtils;
import com.litongjava.tio.utils.snowflake.SnowflakeIdUtils;
public class ActivityServiceTest {
@Test
public void testParseString() {
// 连接数据库
EnvUtils.load();
new TioAdminDbConfiguration().config();
// 示例字符串
String timeString = "2025-03-01 00:28:03";
//转换类型
Timestamp timestamp = Timestamp.valueOf(timeString);
Row row = Row.by("id", SnowflakeIdUtils.id()).set("start_time", timestamp);
Db.save("activity", row);
}
}
注意 java.sql.Timestamp 不包含时区信息,
Java 收到 Long 1743157717000 如何存储到 表字段为 TIMESTAMP WITH TIME ZONE 的表中?
@Test
public void testParseLongTimestamp() {
// 连接数据库
EnvUtils.load();
new TioAdminDbConfiguration().config();
// 毫秒时间戳
long timeMillis = 1743157717000L;
// 将毫秒时间戳转换为 Timestamp 对象
Timestamp timestamp = new Timestamp(timeMillis);
// 创建数据行,并设置 start_time 字段
Row row = Row.by("id", SnowflakeIdUtils.id()).set("start_time", timestamp);
// 保存数据到数据库
Db.save("activity", row);
}
前后端参数传递
创建活动接口
前端通过 /api/table/activity/create
接口传递如下参数:
{
"title": "Test Title1",
"start_time": 1720420650846,
"end_time": 1720420650846,
"start_time_input_type": "millisecond",
"end_time_input_type": "millisecond",
"start_time_to_type": "ISO8601",
"end_time_to_type": "ISO8601"
}
后端实际接收到的数据:
tableName: activity, kv: {start_time=2024-07-08T15:37:30.846+09:00, end_time=2024-07-08T15:37:30.846+09:00, title=Test Title1}
执行的 SQL 语句:
insert into "activity"("start_time", "end_time", "title", "id") values(?, ?, ?, ?)
查询活动接口
通过 ID 查询活动的示例:
id=399895750549155840
idType=long
返回的数据格式如下:
{
"data": {
"title": "Test Activity",
"start_time": 1720418576632,
"update_time": 1720418590002,
"id": "399895750549155840"
},
"ok": true,
"msg": null,
"code": 1
}
注意,输出的 start_time
和 update_time
都是毫秒时间戳格式,前端需要自行解析。