休眠5 + ZonedDateTime + postgresql包括时区和偏移量 [英] hibernate 5 + ZonedDateTime + postgresql include time zone and the offset

问题描述

我有一个正在运行的应用程序spring boot 1.3 + hibernate 5 + java 8 + ZonedDateTime + postgresql，在其中一个表中，我具有以下字段.

I have a running app spring boot 1.3 + hibernate 5 + java 8 + ZonedDateTime + postgresql and in one of the tables I have the following fields.

@Column(name = "DATE_ENABLED")
@Type(type="java.time.ZonedDateTime")   
private ZonedDateTime dateEnabled;

@Column(name = "DATE_DISABLED")
@Type(type="java.time.ZonedDateTime")   
private ZonedDateTime dateDisabled;

如果我运行该应用程序，则默认情况下会生成没有时区的时间戳记"

If I run the app then I see that this by default produces "timestamp without time zone"

testDB=# \d type
                Table "public.type"
             Column             |            Type             | Modifiers 
--------------------------------+-----------------------------+-----------
 type_id                        | bytea                       | not null
 date_disabled                  | timestamp without time zone | 
 date_enabled                   | timestamp without time zone | 

我知道，如果我将columnDefinition ="TIMESTAMP WITH TIME ZONE"添加到列中，例如

I know that if I add the columnDefinition= "TIMESTAMP WITH TIME ZONE" to the column i.e. something like

@Column(name = "DATE_DISABLED", columnDefinition= "TIMESTAMP WITH TIME ZONE")

然后它可以正常工作，并且我能够看到hibernate创建了一个带有时区的列，但是如果我正确理解这一点，那么它将仅适用于postgres，即如果我明天将数据库更改为mysql，那么hibernate将抛出错误.

then it works correctly and I am able to see that hibernate created a column with time zone, but if I understand this correctly this will be only working for postgres i.e. if I change the database tomorrow to mysql then hibernate will throw an error.

因此，我的问题是一般如何执行此操作，即告诉冬眠创建一列应包含时区和偏移量的列.我认为，由于有意创建了Java类型​​ ZonedDateTime"以包括时区和UTC中的时间偏移，因此默认情况下，休眠将创建一个包含时区的列.因此，问题又来了:告诉休眠包括时区和偏移量的正确方法是什么?

Thus my question is how to do that in general i.e. to tell hibernate to create a column that should include the time zone and the offset. I was of the opinion that since the java type "ZonedDateTime" is deliberately created to include the time zone and the time offset in UTC then hibernate will by default create a column that include the time zone. Thus the question again: what is the proper way to tell hibernate to include the time zone and the offset.

这是我pom的一部分:

Here are parts of my pom:

    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.3.0.RELEASE</version>

    <hibernate.version>5.0.4.Final</hibernate.version>

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
    </dependency>

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-java8</artifactId>
        <version>${hibernate.version}</version>
    </dependency>

和显示方言的我的属性文件

and my properties file showing the dialect

    @Bean
    public  Properties hibernateProperties() {
      return new Properties() {
        {
            setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
            setProperty("hibernate.chach.provider_class", "org.hibernate.cache.NoCacheProvider");
            setProperty("hibernate.show_sql", "true");              
            setProperty("hibernate.hbm2ddl.auto", "create-drop");
         }
      };
   }

推荐答案

如果您使用Hibernate进行架构创建，似乎此问题只是一个障碍.因此，如果在PostgreSQL上将列创建为timestamp with time zone后一切正常，则只需这样做.无论如何，让Hibernate生成您的架构是一个坏习惯.手动执行(或让DBA执行此操作).如果要自动化，请使用数据库迁移工具，例如 Flyway 或

It seems this problem is only an obstacle if you use Hibernate for schema creation. So if everything works well after you created the column as timestamp with time zone on PostgreSQL, just go with that. Anyway it's a bad practice to let Hibernate generate your schema. Do it manually (or let a DBA do that). If you want to automate, use a database migration tool like Flyway or Liquibase after a reliable person wrote the sql scripts.

此外，明天更改数据库"的要求听起来确实是虚构的，独立于数据库工作或多或少是不切实际的，并且在编写必须执行的更大应用程序方面越来越困难.

Beside this, the requirement "change the database tomorrow" sounds really fictional, working database independent is more or less unrealistic and gets harder on writing bigger applications which have to perform.

如果您需要有关Hibernate/JDBC时间戳行为的更多信息，请查看

If you need more information about Hibernate / JDBC timestamp behavior, maybe check out this nice article about that.

这篇关于休眠5 + ZonedDateTime + postgresql包括时区和偏移量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！