postgres:使用COPY从CSV文件读取时指定时间戳以UTC为单位[duplicate]

psql (9.1.4)
Type "help" for help.

richardh=> show timezone;
 TimeZone 
----------
 GB
(1 row)

richardh=> set timezone='UTC';
SET
richardh=> show timezone;
 TimeZone 
----------
 UTC
(1 row)

richardh=> set timezone='US/Eastern';
SET
richardh=> show timezone;
  TimeZone  
------------
 US/Eastern
(1 row)

richardh=> set timezone='blah';
ERROR:  invalid value for parameter "TimeZone": "blah"

ALTER USER postgres SET timezone='Asia/Tokyo' ;

psql 医生说:

-v assignment
--set=assignment
--variable=assignment
Perform a variable assignment, like the \set internal command. Note that 
you must separate name and value, if any, by an equal sign on the command line....

 These assignments are done during a very early stage of start-up, 
 so variables reserved for internal purposes might get overwritten later.

因此,似乎必须在psql中使用SET命令,或者设置 PGTZ 环境变量:

PGTZ=PST8PDT psql -c 'show timezone'

当然,如果您同意为用户全局设置时区(不仅仅是为这个单独的psql实例),那么您可以在其 .bashrc 文件(如果在Linux中)

例如,如果您使用的是“IntelliJ IDEA 2017.3”(或DataGrips),则应将时区定义为:

'DB source properties' -> 'Advanced' tab -> 'VM Options': -Duser.timezone=UTC+06:00