SQL 标准定义了三种日期时间类型：

• DATE - 对应 java.sql.Date, 有 year, month, day
• TIME - 对应 java.sql.Time, 有 hour, minute, second
• TIMESTAMP - 对应 java.sql.Timestamp, 有 year, month, day, hour, minute, second, 以及 nanoseconds

通常使用 java.util/java.time 下的类，而不是 java.sql 下的类，避免依赖 java.sql 包

相关的类对应的 Hibernate type 及 JDBC type

• java.sql.Timestamp - TimestampType(TIMESTAMP)
• java.sql.Time - TimeType(TIME)
• java.sql.Date - DateType(DATE)
• java.util.Calendar - CalendarType(TIMESTAMP)
• java.util.TimeZone - TimeZoneType(VARCHAR, using the TimeZone ID)
• java.time.Duration - DurationType(BIGINT)
• java.time.Instant - InstantType(TIMESTAMP)
• java.time.LocalDateTime - LocalDateTimeType(TIMESTAMP)
• java.time.LocalDate - LocalDateType(DATE)
• java.time.LocalTime - LocalTimeType(TIME)
• java.time.OffsetDateTime - OffsetDateTimeType(TIMESTAMP)
• java.time.OffsetTime - OffsetTimeType(TIME)
• java.time.ZonedDateTime - OffsetTimeType(TIMESTAMP)

而 java.util.Date 类型的属性就需要显式(使用 @Temporal)指定 SQL type 。

java.util.Date mapped as DATE

package org.example.demo.hibernate;

import java.text.SimpleDateFormat;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Persistence;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

public class Test {
    public static void main(String[] args) throws Exception {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("test");
        EntityManager em = factory.createEntityManager();

        DateEvent event = new DateEvent();
        event.timestamp = new Date();

        em.getTransaction().begin();
        em.persist(event);
        em.getTransaction().commit();

        em.clear();

        DateEvent e = em.find(DateEvent.class, event.id);
        // 2017-06-25 00:00:00.000+0800
        System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSZ").format(e.timestamp));

        em.close();
        factory.close();
    }

    @Entity(name = "DateEvent")
    public static class DateEvent {
        @Id
        @GeneratedValue
        Integer id;
        @Column(name = "`timestamp`")
        @Temporal(TemporalType.DATE)
        Date timestamp;
    }
}

生成 SQL

drop table if exists DateEvent cascade
drop sequence if exists hibernate_sequence

create sequence hibernate_sequence start 1 increment 1
create table DateEvent (id int4 not null, "timestamp" date, primary key (id))
select nextval ('hibernate_sequence')
insert into DateEvent ("timestamp", id) values ('2017-06-25 16:22:34', 1)

select test_datee0_.id as id1_0_0_, test_datee0_."timestamp" as timestam2_0_0_ from DateEvent test_datee0_ where test_datee0_.id=1

p6spy 生成的 SQL 有点不对，数据库中实际是没有时间的，只有 "2017-06-25"

java.util.Date mapped as TIME

package org.example.demo.hibernate;

import java.text.SimpleDateFormat;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Persistence;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

public class Test {
    public static void main(String[] args) throws Exception {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("test");
        EntityManager em = factory.createEntityManager();

        DateEvent event = new DateEvent();
        event.timestamp = new Date();

        em.getTransaction().begin();
        em.persist(event);
        em.getTransaction().commit();

        em.clear();

        DateEvent e = em.find(DateEvent.class, event.id);
        // 1970-01-01 16:25:45.991+0800
        System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSZ").format(e.timestamp));

        em.close();
        factory.close();
    }

    @Entity(name = "DateEvent")
    public static class DateEvent {
        @Id
        @GeneratedValue
        Integer id;
        @Column(name = "`timestamp`")
        @Temporal(TemporalType.TIME)
        Date timestamp;
    }
}

生成 SQL

drop table if exists DateEvent cascade
drop sequence if exists hibernate_sequence

create sequence hibernate_sequence start 1 increment 1
create table DateEvent (id int4 not null, "timestamp" time, primary key (id))
select nextval ('hibernate_sequence')
insert into DateEvent ("timestamp", id) values ('2017-06-25 16:25:45', 1)

select test_datee0_.id as id1_0_0_, test_datee0_."timestamp" as timestam2_0_0_ from DateEvent test_datee0_ where test_datee0_.id=1

p6spy 生成的 SQL 有点不对，数据库中实际只有 "16:25:45.991" 注意这与 Hibernate 文档说的也不一样，实际是有毫秒字段的。

java.util.Date mapped as TIMESTAMP

package org.example.demo.hibernate;

import java.text.SimpleDateFormat;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Persistence;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

public class Test {
    public static void main(String[] args) throws Exception {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("test");
        EntityManager em = factory.createEntityManager();

        DateEvent event = new DateEvent();
        event.timestamp = new Date();

        em.getTransaction().begin();
        em.persist(event);
        em.getTransaction().commit();

        em.clear();

        DateEvent e = em.find(DateEvent.class, event.id);
        // 2017-06-25 16:28:11.279+0800
        System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSZ").format(e.timestamp));

        em.close();
        factory.close();
    }

    @Entity(name = "DateEvent")
    public static class DateEvent {
        @Id
        @GeneratedValue
        Integer id;
        @Column(name = "`timestamp`")
        @Temporal(TemporalType.TIMESTAMP)
        Date timestamp;
    }
}

生成的 SQL

drop table if exists DateEvent cascade
drop sequence if exists hibernate_sequence

create sequence hibernate_sequence start 1 increment 1
create table DateEvent (id int4 not null, "timestamp" timestamp, primary key (id))
select nextval ('hibernate_sequence')
insert into DateEvent ("timestamp", id) values ('2017-06-25 16:28:11', 1)

select test_datee0_.id as id1_0_0_, test_datee0_."timestamp" as timestam2_0_0_ from DateEvent test_datee0_ where test_datee0_.id=1

p6spy 生成的 SQL 有点不对，数据库中实际有 "2017-06-25 16:28:11.279" 有毫秒字段。

Date

package org.example.demo.hibernate;

import java.text.SimpleDateFormat;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Persistence;

public class Test {
    public static void main(String[] args) throws Exception {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("test");
        EntityManager em = factory.createEntityManager();

        DateEvent event = new DateEvent();
        event.timestamp = new Date();

        em.getTransaction().begin();
        em.persist(event);
        em.getTransaction().commit();

        em.clear();

        DateEvent e = em.find(DateEvent.class, event.id);
        // 2017-06-25 16:42:58.085+0800
        System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSZ").format(e.timestamp));

        em.close();
        factory.close();
    }

    @Entity(name = "DateEvent")
    public static class DateEvent {
        @Id
        @GeneratedValue
        Integer id;
        @Column(name = "`timestamp`")
        Date timestamp;
    }
}

生成的 SQL

drop table if exists DateEvent cascade
drop sequence if exists hibernate_sequence

create sequence hibernate_sequence start 1 increment 1
create table DateEvent (id int4 not null, "timestamp" timestamp, primary key (id))
select nextval ('hibernate_sequence')
insert into DateEvent ("timestamp", id) values ('2017-06-25 16:42:58', 1)

select test_datee0_.id as id1_0_0_, test_datee0_."timestamp" as timestam2_0_0_ from DateEvent test_datee0_ where test_datee0_.id=1

可以看到不使用 @Temporal 效果与 @Temporal(TemporalType.TIMESTAMP) 一样。

Calendar

package org.example.demo.hibernate;

import java.text.SimpleDateFormat;
import java.util.Calendar;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Persistence;

public class Test {
    public static void main(String[] args) throws Exception {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("test");
        EntityManager em = factory.createEntityManager();

        DateEvent event = new DateEvent();
        event.timestamp = Calendar.getInstance();

        em.getTransaction().begin();
        em.persist(event);
        em.getTransaction().commit();

        em.clear();

        DateEvent e = em.find(DateEvent.class, event.id);
        // 2017-06-25 16:34:09.122+0800
        System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSZ").format(e.timestamp.getTime()));

        em.close();
        factory.close();
    }

    @Entity(name = "DateEvent")
    public static class DateEvent {
        @Id
        @GeneratedValue
        Integer id;
        @Column(name = "`timestamp`")
        Calendar timestamp;
    }
}

生成 SQL

drop table if exists DateEvent cascade
drop sequence if exists hibernate_sequence

create sequence hibernate_sequence start 1 increment 1
create table DateEvent (id int4 not null, "timestamp" timestamp, primary key (id))
select nextval ('hibernate_sequence')
insert into DateEvent ("timestamp", id) values ('2017-06-25 16:34:09', 1)

select test_datee0_.id as id1_0_0_, test_datee0_."timestamp" as timestam2_0_0_ from DateEvent test_datee0_ where test_datee0_.id=1

Calendar DATE

package org.example.demo.hibernate;

import java.text.SimpleDateFormat;
import java.util.Calendar;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Persistence;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

public class Test {
    public static void main(String[] args) throws Exception {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("test");
        EntityManager em = factory.createEntityManager();

        DateEvent event = new DateEvent();
        event.timestamp = Calendar.getInstance();

        em.getTransaction().begin();
        em.persist(event);
        em.getTransaction().commit();

        em.clear();

        DateEvent e = em.find(DateEvent.class, event.id);
        // 2017-06-25 00:00:00.000+0800
        System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSZ").format(e.timestamp.getTime()));

        em.close();
        factory.close();
    }

    @Entity(name = "DateEvent")
    public static class DateEvent {
        @Id
        @GeneratedValue
        Integer id;
        @Column(name = "`timestamp`")
        @Temporal(TemporalType.DATE)
        Calendar timestamp;
    }
}

生成 SQL

drop table if exists DateEvent cascade
drop sequence if exists hibernate_sequence

create sequence hibernate_sequence start 1 increment 1
create table DateEvent (id int4 not null, "timestamp" date, primary key (id))
select nextval ('hibernate_sequence')
insert into DateEvent ("timestamp", id) values ('2017-06-25 16:36:03', 1)

select test_datee0_.id as id1_0_0_, test_datee0_."timestamp" as timestam2_0_0_ from DateEvent test_datee0_ where test_datee0_.id=1

Calendar TIME

package org.example.demo.hibernate;

import java.text.SimpleDateFormat;
import java.util.Calendar;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Persistence;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

public class Test {
    public static void main(String[] args) throws Exception {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("test");
        EntityManager em = factory.createEntityManager();

        DateEvent event = new DateEvent();
        event.timestamp = Calendar.getInstance();

        em.getTransaction().begin();
        em.persist(event);
        em.getTransaction().commit();

        em.clear();

        DateEvent e = em.find(DateEvent.class, event.id);
        System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSZ").format(e.timestamp.getTime()));

        em.close();
        factory.close();
    }

    @Entity(name = "DateEvent")
    public static class DateEvent {
        @Id
        @GeneratedValue
        Integer id;
        @Column(name = "`timestamp`")
        @Temporal(TemporalType.TIME)
        Calendar timestamp;
    }
}

第 18 行就报异常 org.hibernate.cfg.NotYetImplementedException: Calendar cannot persist TIME onlyorg.example.demo.hibernate.Test$DateEvent.timestamp

Calendar TIMESTAMP

package org.example.demo.hibernate;

import java.text.SimpleDateFormat;
import java.util.Calendar;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Persistence;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

public class Test {
    public static void main(String[] args) throws Exception {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("test");
        EntityManager em = factory.createEntityManager();

        DateEvent event = new DateEvent();
        event.timestamp = Calendar.getInstance();

        em.getTransaction().begin();
        em.persist(event);
        em.getTransaction().commit();

        em.clear();

        DateEvent e = em.find(DateEvent.class, event.id);
        // 2017-06-25 16:39:10.502+0800
        System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSZ").format(e.timestamp.getTime()));

        em.close();
        factory.close();
    }

    @Entity(name = "DateEvent")
    public static class DateEvent {
        @Id
        @GeneratedValue
        Integer id;
        @Column(name = "`timestamp`")
        @Temporal(TemporalType.TIMESTAMP)
        Calendar timestamp;
    }
}

生成的 SQL

drop table if exists DateEvent cascade
drop sequence if exists hibernate_sequence

create sequence hibernate_sequence start 1 increment 1
create table DateEvent (id int4 not null, "timestamp" timestamp, primary key (id))
select nextval ('hibernate_sequence')
insert into DateEvent ("timestamp", id) values ('2017-06-25 16:39:10', 1)

select test_datee0_.id as id1_0_0_, test_datee0_."timestamp" as timestam2_0_0_ from DateEvent test_datee0_ where test_datee0_.id=1

文档中说 java.util.Calendar takes into consideration the default Time Zone. FIXME 没看到时区

java.time @Temporal

如果对 java.time 类型加 @Temporal 则将抛异常 org.hibernate.AnnotationException: @Temporal should only be set on a java.util.Date or java.util.Calendar property

package org.example.demo.hibernate;

import java.time.LocalDate;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Persistence;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

public class Test {
    public static void main(String[] args) throws Exception {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("test");
        EntityManager em = factory.createEntityManager();

        DateEvent event = new DateEvent();
        event.timestamp = LocalDate.now();

        em.getTransaction().begin();
        em.persist(event);
        em.getTransaction().commit();

        em.clear();

        DateEvent e = em.find(DateEvent.class, event.id);
        // 2017-06-25
        System.out.println(e.timestamp);

        em.close();
        factory.close();
    }

    @Entity(name = "DateEvent")
    public static class DateEvent {
        @Id
        @GeneratedValue
        Integer id;
        @Column(name = "`timestamp`")
        @Temporal(TemporalType.TIMESTAMP)
        LocalDate timestamp;
    }
}

第 17 行就抛异常 org.hibernate.AnnotationException: @Temporal should only be set on a java.util.Date or java.util.Calendar property: org.example.demo.hibernate.Test$DateEvent.timestamp

Using a specific time zone

当保存 java.sql.Timestamp or a java.sql.Time 类型属性时， Hibernate 会调用

• PreparedStatement.setTimestamp(int parameterIndex, java.sql.Timestamp)
• PreparedStatement.setTime(int parameterIndex, java.sql.Time x)

如果没有指定 time zone 则 JDBC driver 会使用 JVM 的默认时区，但这通常不正确。

通常使用一个 single reference time zone (e.g. UTC) 来读写数据库。 Hibernate 提供 hibernate.jdbc.time_zone 配置属性。

hibernate.jdbc.time_zone 可以通过 settings.put(AvailableSettings.JDBC_TIME_ZONE, TimeZone.getTimeZone("UTC")); 在 SessionFactory 范围配置，也可以通过 Session session = sessionFactory().withOptions().jdbcTimeZone(TimeZone.getTimeZone("UTC")).openSession(); 在 Session 范围配置。

如果有配置 hibernate.jdbc.time_zone 则 Hibernate 会调用

• PreparedStatement.setTimestamp(int parameterIndex, java.sql.Timestamp, Calendar cal)
• PreparedStatement.setTime(int parameterIndex, java.sql.Time x, Calendar cal) 其中 cal 参数用于指定 time zone

package org.example.demo.hibernate;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TimeZone;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.AvailableSettings;
import org.hibernate.service.ServiceRegistry;

public class Test {
    public static void main(String[] args) throws Exception {
        ServiceRegistry standardRegistry = new StandardServiceRegistryBuilder()
                .applySetting("hibernate.connection.url", "jdbc:p6spy:postgresql://localhost:5432/test")
                .applySetting("hibernate.connection.username", "postgres")
                .applySetting("hibernate.connection.password", "postgres")
                .applySetting(AvailableSettings.JDBC_TIME_ZONE, TimeZone.getTimeZone("UTC"))
                .applySetting("hibernate.hbm2ddl.auto", "create").build();
        Metadata metadata = new MetadataSources(standardRegistry).addAnnotatedClass(DateEvent.class)
                .getMetadataBuilder().applyImplicitNamingStrategy(ImplicitNamingStrategyJpaCompliantImpl.INSTANCE)
                .build();
        SessionFactory sessionFactory = metadata.getSessionFactoryBuilder().build();
        Session session = sessionFactory.openSession();

        DateEvent event = new DateEvent();
        event.createdTime = new Date();

        session.beginTransaction();
        session.persist(event);
        session.getTransaction().commit();

        session.clear();

        DateEvent e = session.get(DateEvent.class, event.id);
        // 2017-06-25 17:18:20.266+0800
        System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSZ").format(e.createdTime));

        session.close();
        sessionFactory.close();
    }

    @Entity(name = "DateEvent")
    public static class DateEvent {
        @Id
        @GeneratedValue
        Integer id;
        @Temporal(TemporalType.TIMESTAMP)
        Date createdTime;
    }
}

生成的 SQL

drop table if exists DateEvent cascade
drop sequence if exists hibernate_sequence

create sequence hibernate_sequence start 1 increment 1
create table DateEvent (id int4 not null, createdTime timestamp, primary key (id))
select nextval ('hibernate_sequence')
insert into DateEvent (createdTime, id) values ('2017-06-25 17:18:20', 1)

select test_datee0_.id as id1_0_0_, test_datee0_.createdTime as createdT2_0_0_ from DateEvent test_datee0_ where test_datee0_.id=1

注意，数据库中实际存储的值是 "2017-06-25 09:18:20.266"