Store/Retrieve a timestamp with or without timezone

Question

I wanted to test some differences with the timestamp and timestamp with time zone fields. But I came across something I can't really understand why it works like this.

I have the following setup:

CREATE TABLE tbl_test_timezones (
    first_date timestamp with time zone,
    second_date timestamp 
)

I have inserted some testdata:

insert into tbl_test_timezones (first_date, second_date) values (now(), now());

Now I wanted to check the differences between the two fields when I do a select

select 
    first_date, 
    first_date at time zone 'Europe/Brussels' as first_date_wt, 
    second_date, 
    second_date at time zone 'Europe/Brussels' as second_date_wt
from tbl_test_timezones

which gives me the following result

first_date:     2016-10-03 07:03:16.63818+00    
first_date_wt:  2016-10-03 09:03:16.63818   
second_date:    2016-10-03 07:03:16.63818   
second_date_wt: 2016-10-03 05:03:16.63818+00

Question 1

I'm wondering why second_date_wt is doing -2 instead of +2 like first_date_wt?

Question 2

Let's say my application stores the timestamps with the time zone and the user wants to get some records with this timestamp. Do you have to store some kind of user setting for the user where he can enter his timezone and always include this in the select query when retrieving them? Or do you prefer to just get it and do that timezone stuff in the client application?

for example:

select start_date at time zone (
    select user_time_zone from tbl_user_settings where user_id = 2
)
from tbl_projects

or do just

select start_date 
from tbl_projects

Answer 1

Answer to question 1

These two values have a different meaning.

first_date AT TIME ZONE 'Europe/Brussels'

answers: What does the wall clock show in Brussels at this point in time?

second_date AT TIME ZONE 'Europe/Brussels'

answers: At what point in time does the wall clock in Brussels show this value?

Answer to question 2

The best way to do this is by setting the configuration parameter TimeZone to the time zone of the client application. Then all timestamp with time zone values will be shown in this time zone, and timestamp without time zone values will be interpreted as values in this timezone:

SHOW TimeZone;

   TimeZone
---------------
 Europe/Vienna
(1 row)

SELECT
   TIMESTAMP WITH TIME ZONE '2016-10-01 00:00:00 UTC' AS "midnight at UTC",
   CAST(
      TIMESTAMP WITHOUT TIME ZONE '2016-10-01 00:00:00'
      AS TIMESTAMP WITH TIME ZONE
   ) AS "midnight local";

    midnight at UTC     |     midnight local
------------------------+------------------------
 2016-10-01 02:00:00+02 | 2016-10-01 00:00:00+02
(1 row)

SET TimeZone = 'America/Los_Angeles';

SELECT
   TIMESTAMP WITH TIME ZONE '2016-10-01 00:00:00 UTC' AS "midnight at UTC",
   CAST(
      TIMESTAMP WITHOUT TIME ZONE '2016-10-01 00:00:00'
      AS TIMESTAMP WITH TIME ZONE
   ) AS "midnight local";

    midnight at UTC     |     midnight local
------------------------+------------------------
 2016-09-30 17:00:00-07 | 2016-10-01 00:00:00-07
(1 row)