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

Question

I get my data: date and time in UTC, in CSV file format in separate columns. Since I will need to convert this zone to the date and time of the place where I live, now in the summer before UTC + 2, and maybe in some other zones I was wondering what is the best way to embed data in postgres when we say o data type. Should I put both of my data in one column or save them separately as types: date and time, and if not, I should use timestamp or timestampz (or something else).

Answer 1

use timestampzit will save your timestamp in UTC. and displays it to the client according to its locale.

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

For a timestamp with a time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time) Time, GMT). An input value that has an explicit time zone is specified converted to UTC using the appropriate offset for this time zone. If the time zone is not indicated in the input line, then it is assumed that the time zone specified by the TimeZone parameter of the system is converted to UTC using the offset for the time zone zone.

, UTC . , AT TIME ZONE (. 9.9.3).

Lukasz, :

, , , .

, "2017-12-31" "23: 01: 01" , ,

. Laurenz , , UTC . , . :

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)