In PostgreSQL, can we directly compare two timestamp with different time zone?

Question

SELECT * FROM table_a 
WHERE time_1 >= to_timestamp('11/01/2014 10:00 PDT', 'MM/DD/YYYY HH24:MI TZ')

time_1 is in UTC time zone and it is a timestamp with timezone. So, will this give me what I want or I need to do exact UTC time in the function to_timestamp()?

Answer 1

Postgresql has two different timestamp data types and it is confusing which one should be used when. The two types are:

• timestamp (also known as timestamp without time zone) It is most likely that this is the type in table_a
• timestamp with time zone This is the data type returned by to_timestamp()

You must be sure that you are comparing apples with apples or pairs with pairs and not mix them or you may get undesirable results.

If your table_a.time_1 is a timestamp with time zone then the code you give in your question will work fine.

If your table_a.time_1 is a timestamp then you will need to change your code:

SELECT * 
  FROM table_a 
 WHERE time_1 >= to_timestamp('11/01/2014 10:00 PDT', 'MM/DD/YYYY HH24:MI TZ') at time zone 'utc';

The last part of this (at time zone 'utc') will strip the timezone (PDT) off the specified timestamp and translate the timestamp to UTC.

---

Edit: to help with your comments in this answer...

In order to understand how to translate time zones you need to understand the difference between the two forms of time stamp. It will become clear why you need to understand this below. As I indicate above the difference between the two forms of time stamp is confusing. There is a good manual page but for now just read on.

The main thing to understand is that neither version actually stores a time zone (despite the name). The naming would make much more sense if you added an extra word "translation". Think "timestamp without time zone translation" and "timestamp with time zone translation".

A timestamp with time zone translation doesn't store a time zone at all. It is designed to store time stamps which could come from anywhere in the world and not loose track of their meaning. So when entering one you must provide the time zone it came from or postgresql will assume it came from the time zone of your current session. Postgresql automatically translates it out of the given time zone into an internal time zone for the server. You don't need to know what time zone that is because postgresql will always translate it back from this internal time zone before giving you the value. When you retrieve the value (eg: SELECT my_time FROM foo) postgresql translates the time stamp to the time zone of your current session. Alternatively you can specify the time zone to translate into (eg: SELECT my_time AT TIME ZONE 'PDT' FROM foo).

With that in mind it's easier to understand that a timestamp without time zone translation will never be changed from the time you specify. Postgresql will regard 11:00:00 as happening before 12:00:00 even if you meant 11 in America and 12 in England. It's easy to see why that may not be what you want.

A very common programming error is to think that a timestamp with time zone is at a particular time zone. It isn't. It is at whatever time zone you ask for it to be. And if you don't specify what time zone you want it at then postgresql will assume you want it at your current session time zone.

You've stated that your field is a timestamp with time zone which are all at UTC. This isn't technically correct. Most likely your session time zone is UTC and postgresql is giving you everything in UTC as a result.

So you have a timestamp with time zone and you want to know what these times are in PDT? Easy: SELECT my_time AT TIME ZONE 'PDT' FROM foo.

It's important to understand that the AT TIME ZONE '...' syntax toggles between timestamp and timestamp with time zone.

• timestamp AT TIME ZONE 'PDT' converts into a timestamp with time zone and tells postgresql to convert to the PDT time zone.
• timestamp with time zone AT TIME ZONE 'PDT' converts into a timestamp telling postgresql to interpret it as coming from 'PDT'.

This symetry means that to reverse AT TIME ZONE 'foo' you just use AT TIME ZONE 'foo'. Put another way SELECT anything AT TIME ZONE 'PDT' AT TIME ZONE 'PDT' will always leave anything unchanged.