PostgreSQL, pgAdmin, Java: How to make them all UTC?

Question

How can I make sure my entire development environment around PostgreSQL is not messing about with local timezones. For simplicity I need to be 100% sure that each and every time(stamp) value is UTC. When I inserted a row with timestamp without time zone (!) using the CURRENT_TIMESTAMP function I had to realize this was not the case, even though I never ever specified any time zone information.

Is there any step-by-step manual that helps me get rid of time zones?

Answer 1

This requires understanding first. I wrote a comprehensive answer about how PostgreSQL handles timestamps and time zones here:
Ignoring timezones altogether in Rails and PostgreSQL

You cannot "not" have a time zone. You can operate with the type timestamp [without time zone], but you'd still have a time zone in your client.

Your statement:

When I inserted a row with timestamp without time zone (!) using the CURRENT_TIMESTAMP function ...

is a contradictio in adjecto. CURRENT_TIMESTAMP returns a timestamp with time zone (!). If you just cast it (or have it coerced automatically) into timestamp [without time zone], the time zone offset is truncated instead of applied. You get local time (whatever the current time zone setting of the session is) instead of UTC. Consider:

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
      ,CURRENT_TIMESTAMP::timestamp

Unless your local time zone setting is 'UTC' or something like 'London', the two expressions return different values.

If you want to save the literal value you see in your time zone, use one of:

SELECT CURRENT_TIMESTAMP::timestamp
      ,now()::timestamp
      ,LOCALTIMESTAMP;

If you want to save the point in time as it would be represented in UTC, use one of:

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
      ,now() AT TIME ZONE 'UTC;

Answer 2

You have fallen victim to a major misconception: Time stamps do not contain any time zone information! See my other answer here for details. In other words, your entire development environment already doesn't use time zones. The only thing you need to ensure is that when a text representation of the time is converted to a time stamp (and vice versa), the thing doing the converting knows what time zone the text representation was expressed in. For everything else, time zones are irrelevant.

I blame Sun for this! They thought it would be convenient for developers if they included methods for converting a time stamp to/from text inside the timestamp object itself (first with Date and then with Calendar). Since this conversion required a time zone, they thought it would be extra convenient if that same class stored the time zone, so you wouldn't have to pass it every time when doing a conversion. This fostered one of the most pervasive (and damaging) misconceptions in Java ever. I don't know what excuse people who develop in other languages have. Maybe they're just dumb.