HsqlException: type not found or user lacks privilege: DATETIMEOFFSET

Question

I have a Java 8, Spring Boot 2 application with JPA entities connecting to a MS SQL Server database. I am trying to create integration tests using HSQLDB (2.3.3), which work fine if I do not include audit information (created date, last updated, etc.).

This is the error message that I get:

Caused by: org.hsqldb.HsqlException: type not found or user lacks privilege: DATETIMEOFFSET

I understand that the 'type not found or user lacks privilege' error is generic, and could be caused by a variety of things. In this case, I know that everything would otherwise run correctly if not for trying to add a datetimeoffset column to a table.

While looking for answers online I found this documentation that at least some MS SQL date/time types and functions are supported, but this (two-year-old) answer suggests that HSQLDB has only limited support for MS SQL. I haven't found any documentation that talks about HSQLDB specifically in relation to the datetimeoffset data type.

My Table Creation and Data Insertion Script:

    DROP SCHEMA TEST IF EXISTS;
    CREATE SCHEMA TEST;

    CREATE TABLE TEST.Example (
      ID int IDENTITY NOT NULL,
      Name nvarchar(30) NOT NULL,
      Description nvarchar(1000),
      CreatedDate datetimeoffset(7) NOT NULL
    );

    INSERT INTO TEST.Example (ID, Name, Description, CreatedDate) 
    VALUES (1, 'First', 'This is the first example.', '2019-04-18 12:00:00 -05:00');

My Datasource Configuration:

    config:
      datasource:
        jdbc-url: jdbc:hsqldb:mem:testdb;sys.syntax_mss=true
        driver-class-name: org.hsqldb.jdbc.JDBCDriver
        validation-query: SELECT 1
        test-on-borrow: false
        test-while-idle: true
        time-between-eviction-runs-millis: 60000
        max-active: 10

My Entity:

    @Data
    @Entity
    @Table(name = "Example", schema = "TEST")
    public class ExampleEntity {    
        @Id
        @Column(name = "ID", unique = true, nullable = false)
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private int id;

        @Column(name = "Name", nullable = false)
        private String name;

        @Column(name = "Description")
        private String description;

        @Column(name = "CreatedDate", nullable = false)
        private LocalDateTime createdDate;    
    }

My Repository:

    public interface ExampleRepository extends CrudRepository<ExampleEntity, Integer> {
    }

My Integration Test Configuration:

    @Configuration
    @EnableJpaRepositories(
        basePackages = "com.test.example.repository",
        entityManagerFactoryRef = "integrationTestEntityFactory",
        transactionManagerRef = "integrationTestTransactionManager")
    public class IntegrationTestConfig {
        @Bean
        @ConfigurationProperties(prefix = "config.datasource")
        public DataSource integrationTestDatasource() {
            return new EmbeddedDatabaseBuilder()
                .addScript("scripts/schema.sql")
                .build();
        }

        @Bean
        public LocalContainerEntityManagerFactoryBean integrationTestEntityFactory(final DataSource integrationTestDatasource) {

            HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
            vendorAdapter.setDatabase(Database.SQL_SERVER);
            vendorAdapter.setShowSql(false);

            LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
            factory.setJpaVendorAdapter(vendorAdapter);
            factory.setPackagesToScan("com.test.example.entity");
            factory.setDataSource(integrationTestDatasource);
            return factory;
        }

        @Bean
        public PlatformTransactionManager integrationTestTransactionManager(final LocalContainerEntityManagerFactoryBean integrationTestEntityFactory) {
            JpaTransactionManager transactionManager = new JpaTransactionManager();
            transactionManager.setEntityManagerFactory(integrationTestEntityFactory.getObject());
            return transactionManager;
        }

        @Bean
        public NamedParameterJdbcTemplate integrationTestJdbcTemplate(final DataSource integrationTestDatasource) {
            return new NamedParameterJdbcTemplate(integrationTestDatasource);
        }
    }

Answer 1

The latest versions of HSQLDB support DATETIMEOFFSET in MSS compatibility mode. Use 2.4.1.

If you want to continue using an older version that does not support this type, then use CREATE TYPE DATETIMEOFFSET AS TIMESTAMP WITH TIME ZONE before creating your tables.