Logstash timestamp error when CEST is at the end

Hi all,

I have a weird timestamp problem and maybe someone can help on this:

The PostgreSQL logs are in CSV format and I get a fixed date string from Postgres with CEST at the end.
How do I need to set the timestamp expression in the date filter to catch/accept CEST?

An example log message:
2015-08-21 17:02:22.753 CEST,"demo","demo",18372,"XXX.XXX.YYY.ZZZ:51716",55d73c07.47c4,3,"idle",2015-08-21 16:56:07 CEST,,0,LOG,00000,"Verbindungsende: Sitzungszeit: 0:06:15.000 Benutzer=demo Datenbank=demo Host=XXX.XXX.YYY.YYY port=51716",,,,,,,,,""

An example error message from Logstash (does not correspond to the message above but it would have the same meaning):
{:timestamp=>"2015-08-21T16:41:02.152000+0200", :message=>"Failed parsing date from field", :field=>"timestamp", :value=>"2015-08-21 16:41:01.373 CEST", :exception=>"Invalid format: "2015-08-21 16:41:01.373 CEST" is malformed at "CEST"", :config_parsers=>"YYYY-MM-DD HH:mm:ss.SSS z", :config_locale=>"default=en_US", :level=>:warn}

The corresponding logstash.conf snippet:
filter {
if [type] =~ "postgres" {
csv {
columns => [ "timestamp", "postgres_username", "postgres_databasename", "postgres_process_id", "postgres_connection_from", "postgres_session_id", "postgres_session_line_number", "postgres_command_tag", "postgres_session_start_time", "postgres_virtual_transaction_id", "postgres_transaction_id", "postgres_error_severity", "postgres_sql_state_code", "postgres_message", "postgres_detail", "postgres_hint", "postgres_internal_query", "postgres_internal_query_pos", "postgres_context", "postgres_query", "postgres_query_pos", "postgres_location", "postgres_application_name" ]
date {
match => [ "timestamp", "YYYY-MM-DD HH:mm:ss.SSS z" ]

Thx in advance,

Quoting the Joda-Time documentation:

Zone names: Time zone names ('z') cannot be parsed

So, unless you can hardcode the timezone in the date filter configuration I think you need to translate the timezone names into offsets and include the offset in the timestamp to be parsed.

THX for the info. I will think about a solution later. At the moment the entries are there with two seconds difference and we can live with that at the moment.

There seems to be some kind of fix for this, http://stackoverflow.com/questions/13181970/joda-datetime-parse-date-with-cest-in-string/13182191#13182191

But I'm not sure which version of joda-time Logstash uses?

Here is my fix:

First I grok the logline and separate the datetime, timezone and the rest.

grok {
  match => ['message', "%{DATA:timestamp} %{WORD:tz} ........"]

In my zone the 'tz' variable now contains 'CET' or 'CEST'.

With the translate filter I lookup the corresponding offset.
Note: you need to extend the dictionary with your own relevant zones + offsets.

translate {
  field       => 'tz'
  destination => 'tz_num'
  dictionary  => [
    'CET',   '+0100',
    'CEST',  '+0200'

Append the offset to the grokked timestamp:

mutate {
  replace => ['timestamp', '%{timestamp} %{tz_num}']

And now parse it as usual:

date {
  match => [ 'timestamp', 'yyyy-MM-dd HH:mm:ss.SSS Z' 

And as bonus:

mutate {
  remove => [ 'tz', 'tz_num' ]


Hope this helps?


This helped me a lot when parsing Cisco logs.

Unfortunately Cisco doesn't have support for numbered timezones:S