CODEWITHSUNDEEP
javahibernatetimezonejodatimehsqldb
Thomas Eizinger
Thomas Eizinger

Reputation: 1462

How to set timezone of HSQLDB?

How do I set the timezone to UTC in a HSQL database?

According to the documentation here one should just write:

SET TIME ZONE INTERVAL '+00:00' HOUR TO MINUTE;

but that does not work for me.

I use HSQLDB (v2.2.8) as a memory database in combination with hibernate (v4.2.2) for unittesting and JodaTime (v2.3) for time-related data. Some data is inserted with SQL-scripts via hibernate.hbm2ddl.import_files. These scripts use the now() function for creating a timestamp.

However when I retrieve the inserted value from the database and convert it to UTC I get the following results:

Without any SQL-Command like the one above:
2014-06-04T16:44:45.193Z
This is my current localtime, but in UTC.

With the above SQL-Command: (Note the 1 hour difference)
2014-06-04T15:50:56.688Z
This would be localtime if we would not have daylight savings time here.

Something that might be interesting: It does not matter what offset I provide in the above command, the output will always be the same.

Edit:

A sample line of my insert script:

INSERT INTO public.users (created_at, modified_at, email, firstname, lastname, password, state, image_id) VALUES (now(), now(), '[email protected]', 'Max', 'Mustermann', 'password_hash', 'ACTIVE', NULL);

My user entity inherits from the following entity:

@MappedSuperclass
public abstract class AbstractTrackedEntity extends AbstractEntity {
@Column(name = "created_at")
@Type(type="org.jadira.usertype.dateandtime.joda.PersistentDateTime")
DateTime createdAt;

@Column(name = "modified_at")
@Type(type="org.jadira.usertype.dateandtime.joda.PersistentDateTime")
DateTime modifiedAt;

public DateTime getCreatedAt() {
    return createdAt.withZone(DateTimeZone.UTC);
}

public void setCreatedAt(DateTime createdAt) {
    this.createdAt = createdAt.withZone(DateTimeZone.UTC);
}

public DateTime getModifiedAt() {
    return modifiedAt.withZone(DateTimeZone.UTC);
}

public void setModifiedAt(DateTime modifiedAt) {
    this.modifiedAt = modifiedAt.withZone(DateTimeZone.UTC);
}

@PrePersist
protected void onCreate() {
    modifiedAt = createdAt = DateTime.now(DateTimeZone.UTC);
}

@PreUpdate
protected void onUpdate() {
    modifiedAt = DateTime.now(DateTimeZone.UTC);
}

@Override
public String toString() {
    return "AbstractTrackedEntity{" +
            "createdAt=" + createdAt +
            ", modifiedAt=" + modifiedAt +
            '}';
}
}

Please note that in my particular problem, only the getters are relevant because the data is NOT inserted by hand (e.g. creating an entity and persisting with hibernate) but with the above mentioned SQL-script.

Summarized problem:
The database does not store timezone information with the actual timestamp value. Therefore if now() returns localtime, localtime will be inserted into the database.

Because our production database runs with timezone UTC, the code is written to treat every timestamp from the database as UTC. (Therefore those withZone(DateTimeZone.UTC) everywhere).
In my unittests I need to compare UTC-timestamps generated in the code with timestamps from the database. These unittests break atm because a timestamp from the database, treated as UTC, is 2 hours off from the timestamp generated by the code, although they were generated just seconds apart.

Upvotes: 2

Views: 5321

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241525

Reading these docs, perhaps CURRENT_TIMESTAMP would work better than NOW. I don't currently have ability to test this, but you might give it a try.

Since it returns TIMESTAMP WITH TIME ZONE, then you might also consider:

CURRENT_TIMESTAMP AT TIME ZONE INTERVAL '0:00'

See also this discussion thread.

Upvotes: 1

Related Questions