JPQL 和带时区的时间戳

发布于 2024-07-26 22:27:45 字数 586 浏览 6 评论 0原文

我的一个 JPA 实体中有以下字段定义：

@Column(nullable = false, name = "start_time",
columnDefinition= "TIMESTAMP WITH TIME ZONE")
@Temporal(javax.persistence.TemporalType.TIMESTAMP)
private Date startTime = new Date();

@Column(nullable = true, name = "end_time",
columnDefinition= "TIMESTAMP WITH TIME ZONE")
@Temporal(javax.persistence.TemporalType.TIMESTAMP)
private Date endTime = null;

数据库是 PostgreSQL，不能选择使用没有时区的时间戳。关于时区的插入和更新进展顺利。当我尝试对某些测试数据运行 SELECT JPQL 查询并且过滤器参数是 Date 对象时，会出现问题（至少我这么认为）。 OpenJPA 生成的查询在参数中不包含时区。谁能给我一些关于 JPA 和带有时区列的时间戳的指导？

原文

I have the following field definitions in one of my JPA entities:

@Column(nullable = false, name = "start_time",
columnDefinition= "TIMESTAMP WITH TIME ZONE")
@Temporal(javax.persistence.TemporalType.TIMESTAMP)
private Date startTime = new Date();

@Column(nullable = true, name = "end_time",
columnDefinition= "TIMESTAMP WITH TIME ZONE")
@Temporal(javax.persistence.TemporalType.TIMESTAMP)
private Date endTime = null;

The database is PostgreSQL and using timestamp without timezone is not an option. The inserts and updates are going fine regarding the timezone. A problem arises (at least I think so) when I try to run a SELECT JPQL query on some test data and the filter parameters are Date objects. The query OpenJPA generates doesn't include the timezone in the parameters.
Can anyone give me some guidance regarding JPA and timestamp with timezone columns?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

尽揽少女心 2024-08-02 22:27:45

当 JPA 实体存储在数据库中时，我会避免在它们中使用日期和日历。标准 JDBC 时间值没有时间戳的概念。

我使用以下样式，实际上

private Long effectiveDate;
public Date getEffectiveDate() {
  if (effectiveDate == null) { return null; }
  return new Date(effectiveDate);
}
public void setEffectiveDate(Date d) {
  if (d == null) { effectiveDate = null; }
  effectiveDate = d.getTime();
}

，存储在数据库中的数据是一个简单的长值。但是，在应用程序层上，您仍然使用具有时区概念的 Date 对象。

现在有时您仍然希望在数据库级别存储日期以供查询之用。在这种情况下，您可以执行以下操作

private Long effectiveDate;
private Date effectiveDateDate;
public Date getEffectiveDate() {
  if (effectiveDate == null) { return null; }
  return new Date(effectiveDate);
}
public void setEffectiveDate(Date d) {
  effectiveDateDate = d;
  if (d == null) { effectiveDate = null; }
  effectiveDate = d.getTime();
}

I would avoid the use of Date and Calendar in JPA entities when they are being stored in the database. Standard JDBC temporal values have no concept of timestamps.

I use the following style instead

private Long effectiveDate;
public Date getEffectiveDate() {
  if (effectiveDate == null) { return null; }
  return new Date(effectiveDate);
}
public void setEffectiveDate(Date d) {
  if (d == null) { effectiveDate = null; }
  effectiveDate = d.getTime();
}

Effectively the data that gets stored in the database is a simple long value. However, on the application tier you still use the Date objects which have the concept of timezones.

Now sometimes you would still want to store the date for query purposes on the database level. In which case you can do the following

private Long effectiveDate;
private Date effectiveDateDate;
public Date getEffectiveDate() {
  if (effectiveDate == null) { return null; }
  return new Date(effectiveDate);
}
public void setEffectiveDate(Date d) {
  effectiveDateDate = d;
  if (d == null) { effectiveDate = null; }
  effectiveDate = d.getTime();
}

回复收藏 0 原文