Import CSV date format time difference

[Julian_Tang]

Hi I try to import a CSV file in logstash.
but I have problem, the csv file first is date and time,
I use separator to "timestamp", but it + 8 hours (my time zone is +8)
so I how to set time don't +8 ?

input {
  file {
    path => "/tmp/test_new.txt"
    start_position => "beginning"
    sincedb_path => "/dev/null"
  }
}
filter {
  csv {
      separator => ","
      skip_header => "true"
      columns => ["timestamp","hostname","status"]
  }
 }

output {
   elasticsearch {
     hosts => "http://10.10.10.9:9200"
     index => "backuplog2"
  }
stdout {}
}

[leandrojmp (Leandro Pereira)]

This happens because in Elasticsearch every date field is stored in UTC and Kibana will convert this time in UTC to your local time, so if your date is not in UTC you need to tell that to Elasticsearch to avoid this confusion.

In your case, your date does not have any information about the timezone, so Elasticsearch will assume that 2022/10/17 19:17:53 is in UTC, and when Kibana converts this time to your time zone of +8, you will get the 2022/10/18 03:17:53.000.

You need to use a date filter in your Logstash pipeline to tell elasticsearch that your field is not in UTC.

date {
    match => ["timestamp", "yyyy/MM/dd HH:mm:ss"]
    timezone => "+0800"
    target => "timestamp"
}

[Rios]

@leandrojmp is it better to use canonical ID because of daylight saving time? Some countries are using, some not.

date {
    match => ["timestamp", "yyyy/MM/dd HH:mm:ss"]
    timezone => "Asia/Singapore"
    target => "timestamp"
}

[leandrojmp (Leandro Pereira)]

I think it's a question of preference.

You can use the numeric offset or the canonical timezone name, the difference is that using the numeric offset you know what is the time difference just looking at the config and using the canonical timezone you may need to look anywhere else unless it is some timezone you already know.

[Rios]

Accoriding to Joda format, Europe/Berlin is +01:00. I have tested, it's not the same result

input {
  generator {
       "message" => "2022/09/13 03:17:53" # and 2022/01/13 03:17:53
       count => 1
  }
} 
filter {
     date {
       match => ["message", "yyyy/MM/dd HH:mm:ss"]
       #timezone => "Europe/Berlin"
       timezone => "+0100"
       remove_field => ["event","host", "@version"]
    }
}
output {
    stdout {
        codec => rubydebug{ metadata => true}
    }
}

Result with +0100

"2022/09/13 03:17:53"  & "+0100" =>
{
    "@timestamp" => 2022-09-13T02:17:53.000Z,
       "message" => "2022/09/13 03:17:53"
}
"2022/01/13 03:17:53" & "+0100" =>
{
    "@timestamp" => 2022-01-13T02:17:53.000Z,
       "message" => "2022/01/13 03:17:53"
}

Result with Europe/Berlin

"2022/09/13 03:17:53" & "Europe/Berlin" =>
{
    "@timestamp" => 2022-09-13T01:17:53.000Z,
       "message" => "2022/09/13 03:17:53"
}
"2022/01/13 03:17:53" & "Europe/Berlin" =>
{
    "@timestamp" => 2022-01-13T02:17:53.000Z,
       "message" => "2022/01/13 03:17:53"
}

[leandrojmp (Leandro Pereira)]

Using the canonical timezone will also shift according to the Daylight Saving Time.

If I'm not wrong, Europe/Berlin is UTC + 2 now and it will go back to UTC + 1 on October 30th.

So you are right, it is better to use the canonical name if you need to deal with Daylight Saving Times.

In my case, I do not need to worry about DSTs so using the canonical name or the numeric offset has no difference.

[Rios]

Yes, some countries, use DST others don't. Berlin is +1h and +1h for DST => +2h right now.
I'm not right, somewhere exists a good developer.

For the end... End of DST in Europe is 30. October, but...
In the United States, Canada, and Mexico’s northern border cities, Daylight Saving Time (DST) ends on Sunday, November 6, 2022 .

TimeZones are excellent to make mess in a cloud environment especially for bank's logs or revenues