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

On 09/22/2015 06:31 AM, Michael Zoet wrote:
> Hi Charles,
>
> 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.
>
> Is there a way to convince Postgres to write the date/time with
> numerical time zone values to the log files?

I don't know of a way, but it seems Logstash can be told how to do the
right thing:

https://www.elastic.co/guide/en/logstash/current/plugins-filters-date.html#plugins-filters-date-locale

See match and:

http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html

z time zone text Pacific Standard
Time; PST

>
> Regards,
> Michael
>
>
>
>
>> Hi
>>
>>> -----Original Message-----
>>> From: pgsql-general-owner(at)postgresql(dot)org
>>> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Michael Zoet
>>> Sent: Dienstag, 22. September 2015 12:07
>>> To: pgsql-general(at)postgresql(dot)org
>>> Subject: [GENERAL] how to show time zone with numerical offset in CSV
>>> log?
>>>
>>> Hello everybody,
>>>
>>> I am saving PostgreSQL log file data (CVS logs) with Logstash and
>>> Elasticsearch. My problem with this is that the time zone value is
>>> with the name of the time zone like
>>>
>>> 2015-09-22 12:02:59.836 CEST
>>>
>>> which Logstash can not process.
>>>
>>> What Logstash needs are date/time stamps like
>>>
>>> 2015-09-22 12:02:59.836 +0200.
>>>
>>> How can I setup Postgres to log with a numerical offset in the CSV
>>> logs and not with the name of the time zone?
>>
>> Not exactly the same format, but this may help:
>>
>> kofadmin(at)kofdb(dot)localhost=> SET datestyle TO 'ISO';
>> SET
>> kofadmin(at)kofdb(dot)localhost=> select now();
>> now
>> ----------------------------
>> 2015-09-22 12:53:38.123+02
>> (1 row)
>>
>> If you want the change to be persistent you can use:
>>
>> ALTER DATABASE database_name SET datestyle TO 'ISO';
>>
>> And then reconnect to see the change.
>>
>> Bye
>> Charles
>>
>>>
>>> Any hints and links to the corresponding documentation would be
>>> appreciated.
>>>
>>> Regards,
>>> Michael
>>>
>>>
>>>
>>>
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
> ----- Ende der Nachricht von Charles Clavadetscher
> <clavadetscher(at)swisspug(dot)org> -----
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com