the difference between these two is nicely illustrated by below example:

test=# create table timestamp_test(t1 timestamp(0) without time zone, t2 timestamp(0) with time zone);
CREATE TABLE
test=# insert into timestamp_test values ('2011-01-01 00:00:00+03', '2011-01-01 00:00:00+03');
INSERT 0 1
test=# select * from timestamp_test;
         t1          |           t2
---------------------+------------------------
 2011-01-01 00:00:00 | 2010-12-31 13:00:00-08
(1 row)

What it means:

timestamp without time zone ignores any time zone offset specified when inserting data. So it doesn’t matter whether you are inserting '2011-01-01 00:00:00+03' or '2011-01-01 00:00:00-03'. For it, both are same and get truncated to '2011-01-01 00:00:00‘. And will return the inserted value as is when it is queried.

timestamp with time zone on the other hand takes into account the time zone offset when inserting data. It will convert the inserted value into UTC and store the epoch seconds as a bigint. When the data is queried, it will convert the UTC seconds to the time zone of the server and format the return value in that time zone. In above example we inserted ‘2011-01-01 00:00:00+03’ into the database. ‘2011-01-01 00:00:00+03’ means its ‘2011-01-01 00:00:00’ where I live and I am 3 hours ahead of GMT. So in GMT it is ‘2010-12-31 21:00:00’. And in PST which is 8 hours behind GMT (and the time zone of the server), it is ‘2010-12-31 13:00:00’ which is what it displays as 2010-12-31 13:00:00-08.

In a nutshell,

timestamp without time zone = I will be inserting timestamps without time zone
timestamp with time zone = I will be inserting timestamps with time zone

This is how I believe the nomenclature originated. Ideally, Postgres should throw exception when user tries to insert a timestamp with time zone into a timestamp without time zone column but it doesn’t do that currently.

Which one should you use?

I honestly cannot think of a reason for using timestamp without time zone. This is because both take up 8 bytes so the cost is the same and timestamp with time zone takes into account time zone offsets.

If you are using timestamp without time zone make sure the timestamps you are inserting are in same time zone, otherwise you have a bug in your code.

That’s it for this post.