Re: how to show time zone with numerical offset in CSV log?

Hi

> thanks for the quick response and it looked promising but did not work
> as expected.
>
> I can set the datestyle to ISO on database level but this does not
> seem to effect the way the CSV logs are written. I still get
> 2015-09-22 13:06:01.658 UTC (or CEST and so on) in the log files. And
> as I see it is not only in the CSV logs, also in the none CSV logs I
> have.

I guess this means that the datestyle affects the way how the client wants the information displayed.

> Is there a way to convince Postgres to write the date/time with
> numerical time zone values to the log files?

Unfortunately I am a bit short of time right at the moment. I would suggest that you look into how the tools you are using generate the csv.

Here what I could see using psql:

db=> create table testdate (timedate timestamptz);
CREATE TABLE
db=> \d testdate
Table "public.testdate"
Column | Type | Modifiers
----------+--------------------------+-----------
timedate | timestamp with time zone |

db=> insert into testdate values(clock_timestamp());
INSERT 0 1
db=> insert into testdate values(clock_timestamp());
INSERT 0 1
db=> insert into testdate values(clock_timestamp());
INSERT 0 1

kofadmin(at)kofdb(dot)localhost=> SET datestyle TO "GERMAN";
SET
db=> \copy testdate to testdate.csv csv header ;
COPY 3

Content of file:

timedate
22.09.2015 15:53:48.268 CEST
22.09.2015 15:53:49.612 CEST
22.09.2015 15:53:50.44 CEST

db=> SET datestyle TO "ISO";
SET
db=> \copy testdate to testdate.csv csv header ;
COPY 3

Content of file:

timedate
2015-09-22 15:53:48.268+02
2015-09-22 15:53:49.612+02
2015-09-22 15:53:50.44+02

So it is actually a matter of instructing the client to print the date in the format that you wish.

Hope this helps.
Bye
Charles