Geek Answers Handbook

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

How can I make sure that my entire development environment around PostgreSQL does not bother with local time zones. For simplicity, I have to be 100% sure that each value (stamp) is UTC. When I inserted a row using timestamp without time zone (!), Using the CURRENT_TIMESTAMP function, I had to realize that this was not the case, although I had never provided time zone information.

Is there a step-by-step guide that helps me get rid of time zones?

+4
source share
3 answers

This requires understanding first. I wrote an exhaustive answer on how PostgreSQL handles timestamps and time zones here:
Ignoring Time Zones in Rails and PostgreSQL

You cannot “not have” a time zone. You can work with the timestamp [without time zone] , but you will still have the time zone in your client.

Your expression:

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

is a contradiction in adjecto . CURRENT_TIMESTAMP returns a timestamp with time zone (!). If you just threw it (or forced it) into the timestamp [without time zone] , the timestamp [without time zone] offset is truncated instead of applied. You get local time (regardless of the current time zone of the session) instead of UTC. Consider:

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

If your local time zone is not "UTC" or something like "London", the two expressions return different values.

If you want to keep the literal value that you see in your time zone, use one of the following options:

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

If you want to save the point in time, since it will be displayed in UTC, use one of the following options:

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

You are a victim of a serious misconception: Timestamps do not contain time zone information ! See my other answer here for more info. In other words, your development environment no longer uses time zones. The only thing you need is to make sure that when the text representation of time is converted to a timestamp (and vice versa), what the conversion does is know in which time zone the text representation was expressed. Otherwise, time zones do not matter.

I blame Sun for that! They thought it would be convenient for developers if they included methods for converting timestamps to / from text inside the timestamp object itself (first with Date and then using Calendar ). Since this conversion required a time zone, they thought it would be very convenient if the same class kept the time zone, so you did not have to pass it each time the conversion was performed. This has contributed to one of the most common (and harmful) misconceptions in Java ever. I do not know what kind of apologies people who develop in other languages. Maybe they are just dumb.

+2
source

Declare date columns "timestamptz" or "time zone with time zone".

Are you also asking about converting existing data not saved with timestamps?

0
source

More articles:


All Articles