Postgresql – hibernate mapping for postgresql “timestamp without time zone”

The differences are covered at the PostgreSQL documentation for date/time types. Yes, the treatment of TIME or TIMESTAMP differs between one WITH TIME ZONE or WITHOUT TIME ZONE. It doesn't affect how the values are stored; it affects how they are interpreted.

The effects of time zones on these data types is covered specifically in the docs. The difference arises from what the system can reasonably know about the value:

• With a time zone as part of the value, the value can be rendered as a local time in the client.

• Without a time zone as part of the value, the obvious default time zone is UTC, so it is rendered for that time zone.

The behaviour differs depending on at least three factors:

• The timezone setting in the client.
• The data type (i.e. WITH TIME ZONE or WITHOUT TIME ZONE) of the value.
• Whether the value is specified with a particular time zone.

Here are examples covering the combinations of those factors:

foo=> SET TIMEZONE TO 'Japan';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+09
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 06:00:00+09
(1 row)

foo=> SET TIMEZONE TO 'Australia/Melbourne';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+11
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 08:00:00+11
(1 row)

If you want it to be reversible, you're in trouble. Hashes are designed to be one-way.

In particular, given that an int has 32 bits of information, and a char has 16 bits of information, requiring reversibility means you can only have strings of zero, one or two characters (and even that's assuming that you're happy to encode "" as "\0\0" or something similar). That's assuming you don't have any storage, of course. If you can use storage, then just store numbers sequentially... something like:

private int stringToIntFunction(String uri) {
    Integer existingId = storage.get(uri);
    if (existingId != null) {
        return existingId.intValue();
    }
    return storage.put(uri);
}

Here storage.put() would increase a counter internally, store the URI as being associated with that counter value, and return it. My guess is that that's not what you're after though.

Basically, to perform a reversible encryption, I'd use a standard encryption library having converted the string to a binary format first (e.g. using UTF-8). I would expect the result to be a byte[].

If it doesn't have to be reversible, I'd consider just taking the absolute value of the normal hashCode() result (but mapping Integer.MIN_VALUE to something specific, as its absolute value can't be represented as an int).