Storing date, time, and timezone in PostgreSQL with Grails and GORM

Question

I have a Grails 2.5.3 application that is connecting to PostgreSQL, and I want to store a java Date or Calendar object in the database, and include the time zone.

Based on the PostgreSQL Documentation, the default timestamp type does not include time zone, so you need to use the timestamptz type to include the time zone.

Unfortunately when I try to set this up in the mapping closure of the domain class, it fails. I'm trying to use this:

createdDate type: 'timestamptz'

And the error I receive is:

nested exception is org.hibernate.MappingException: Could not determine type for: timestamptz

Unfortunately the list of Hibernate types does not seem to include anything that would map this value. The ones related to dates are: date, time, timestamp, calendar, calendar-date. I have tested each of these, and none of them create the desired timestamp with time zone in Postgres.

There are articles that talk about creating a custom Hibernate UserType for this, but it seems like this would be a fairly common use case, and I can't help but think there is something that should let me get this working out of the box.

Answer 1

You can create your own dialect and then map the Java type to the SQL type. You can see how it's done in the grails-postgresql-extensions plugin and subclass that dialect or just the default postgresql one.

package my.company

import java.sql.Types
import groovy.transform.CompileStatic
import net.kaleidos.hibernate.PostgresqlExtensionsDialect

@CompileStatic
class SQDialect extends PostgresqlExtensionsDialect {

    SQDialect() {
        registerColumnType(Types.TIMESTAMP, 'timestamp with time zone')
    }
}