Postgres Timestamp

There are no bulletproof solutions here.

My first advice: don't rely never on the default timezone of the server.

My second advice: choose timestamp/timestamptz according to the (predominant) semantics of the data.

In more detail: PostgresSQL has two timestamp variants, confusingly called TIMESTAMP WITHOUT TIMEZONE (timestamp) and TIMESTAMP WITH TIMEZONE (timestamptz). Actually, neither lets you store a timezone or an offset. Both datatypes occupy the same width, and their difference is subtle (and, worse, can bite you if you don't fully understand them and your server changes the timezone). My sanity ruleset is:

Use TIMESTAMP WITH TIMEZONE (timestamptz) for storing events that are predominantly related to the "physical" time, for which you are mainly interested in querying if event 1 was before event 2 (regardless of timezones) or computing time intervals (in "physical units", eg, seconds; not in "civil" units as days-months, etc). The typical example are record creation/modification time - what one usually means by the word "Timestamp".

Use TIMESTAMP WITHOUTH TIMEZONE (timestamp) for storing events for which the critical information is the "civil time" (that is, the fields {year-month-day hour-min-sec} as a whole), and the queries involve calendar calculations. In this case, you would store here only the "local time", i.e., the date-time relative to some unspecified timezone.

The later option makes you easy to query for all events that happened on day '2013-01-20' (in each corresponding region) - but makes it difficult to query for events that ocurred (phisically) before a reference one (unless we know they are in the same timezone). YOu choose.

If you need the full thing, neither is enough, you need either to store the timezone or the offset in an additional field. Another option, which wastes a few bytes but can be more efficient for queries is to store both fields.

See also this.

Use 'timestamptz' (or 'timestamp with time zone' for older versions of postgresql) for your input fields, you can then set a custom time offset for every insert using either the timezone or time offset to whatever suits your preference.

Example...

CREATE TABLE "timetest"
(
"timestamp" timestamptz
);

INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 PST');
INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 Europe/Madrid');
INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 Europe/Athens');
INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 GMT+11');
INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 GMT-11');
INSERT INTO "timetest" ("timestamp") VALUES ('2013-01-01 08:45:00 UTC');

...and your times will be adjusted accordingly

SELECT * FROM "timetest"; -- note this may default to your timezone
------------------------
[timestamp]
------------------------
2013-01-01 16:45:00+00
2013-01-01 07:45:00+00
2013-01-01 06:45:00+00
2012-12-31 21:45:00+00
2013-01-01 19:45:00+00
2013-01-01 08:45:00+00
2013-01-01 08:45:00+00

or better still, try the following...

SELECT 
"timestamp" AT TIME ZONE 'Australia/Sydney' AS "Sydney", 
"timestamp" AT TIME ZONE 'Australia/Perth' AS "Perth" 
FROM "timetest";
--------------------------------------------
[Sydney]..............[Perth]
--------------------------------------------
2013-01-02 03:45:00 - 2013-01-02 00:45:00
2013-01-01 18:45:00 - 2013-01-01 15:45:00
2013-01-01 17:45:00 - 2013-01-01 14:45:00
2013-01-01 08:45:00 - 2013-01-01 05:45:00
2013-01-02 06:45:00 - 2013-01-02 03:45:00
2013-01-01 19:45:00 - 2013-01-01 16:45:00

Finally, to get an idea of list of the timezones availalble to your database try:

SELECT * FROM pg_timezone_names ORDER BY utc_offset DESC;