PostgreSQL 日期类型

• 数据类型对应关系
• Java OffsetDateTime
• 创建表
• 后端存储和查询
  • 使用 OffsetDateTime
  • Java 收到字符串 2025-03-01 00:28:03 如何存储到 表字段为 TIMESTAMP WITH TIME ZONE 的表中?
  • Java 收到 Long 1743157717000 如何存储到 表字段为 TIMESTAMP WITH TIME ZONE 的表中?
• 前后端参数传递
  • 创建活动接口
  • 查询活动接口

在前后端的数据传递过程中，日期类型是一种特殊的类型，因此需要单独介绍处理方法。

数据类型对应关系

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 都是毫秒时间戳格式，前端需要自行解析。