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?
Understand how Postgres handles timestamps and time first:
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 theCURRENT_TIMESTAMPfunction ...
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 for the timezone setting of the current session 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;
CURRENT_TIMESTAMP to a column of type timestamp. The current time zone setting is getting applied here, no amount of bold is going to change that. Try the sample code I supplied. I can assure you, I know what I am writing about. Find more explanation in the answer I linked to.CURRENT_TIMESTAMP is a function in PostgreSQL that returns a value of type timestamp with time zone. If there is a mistake in my answer I would ask you to point it out. I doubt there is one.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.
Declare date columns "timestamptz" or "timestamp with time zone".
Are you also asking about converting existing data not stored with timestamps?
TIMEZONE WITH TIME ZONEtimestamp without time zone
The TIMESTAMP WITHOUT TIME ZONE data type in both Postgres and the SQL standard represents a date and a time-of-day but without any concept of time zone or offset-from-UTC. So this type cannot represent a moment, is not a point on the timeline.
Any time zone or offset information you submit with a value to a column of this type will be ignored.
When tracking specific moments, use the other type, TIMESTAMP WITH TIME ZONE. In Postgres, any time zone or offset information you submit with a value to a column of this type will be used to adjust into UTC (and then discarded).
For simplicity I need to be 100% sure that each and every time(stamp) value is UTC.
Then use a column of type TIMESTAMP WITH TIME ZONE.
s there any step-by-step manual that helps me get rid of time zones?
You do not want to get rid of time zones (and offsets), as that would mean you would be left with an ambiguous date and time-of-day. For example, noon on the 23rd of January this year fails to tell us if you mean noon in Tokyo Japan, noon in Toulouse France, or noon in Toledo Ohio US. Those are all different moments, all several hours apart.
With JDBC 4.2, we can exchanged java.time objects rather than the terrible legacy date-time classes.
OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;
myPreparedStatement.setObject( … , odt ) ;
Retrieval.
OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;
These values will all be in UTC, having an offset of zero hours-minutes-seconds.
Beware that many tools and middleware, such as PgAdmin, will lie to you. In a well-intentioned anti-feature, they apply a default time zone to the data pulled from the database. Values of type TIMESTAMP WITH TIME ZONE are always stored in UTC in Postgres, always. But your tool may report that value as if in America/Montreal, or Pacific/Auckland, or any other default time zone.
I recommend always setting the default time zone in such tools to UTC.
