Date and time in UTC - how to store them in postgres?

Question

I am getting my data: date and time in UTC, in a csv file format in separate columns. Since I will need to convert this zone to date and time of the place where I live, currently in summer to UTC+2, and maybe some other zones I was wondering what is the best practice to insert data in postgres when we are talking about type of data. Should I place both of my data in a single column or keep them separate as types: date and time, and if not should I use timestamp or timestampz (or something else).

Answer 1

use timestamptz it will store your time stamp in UTC. and will display it to the client according to it's locale.

https://www.postgresql.org/docs/current/static/datatype-datetime.html

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

updated with another good point from Lukasz, I had to mention:

Also in favor of single column is the fact that if you would store both date and time in separate columns you would still need to combine them and convert to timestamp if you wanted to change time zone of date.

Not doing that would lead to date '2017-12-31' with time '23:01:01' would in other time zone in fact be not only different time, but different date with all YEAR and MONTH and DAY different

another update As per Laurenz notice, don't forget the above docs quote An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. Which means you have to manage the input dates carefully. Eg:

t=# create table t(t timestamptz);
CREATE TABLE
t=# set timezone to 'GMT+5';
SET
t=# insert into t select '2017-01-01 00:00:00';
INSERT 0 1
t=# insert into t select '2017-01-01 00:00:00' at time zone 'UTC';
INSERT 0 1
t=# insert into t select '2017-01-01 00:00:00+02';
INSERT 0 1
t=# select * from t;
           t
------------------------
 2017-01-01 00:00:00-05
 2017-01-01 05:00:00-05
 2016-12-31 17:00:00-05
(3 rows)