CSV field from string to Date


(Sridhar B) #1

Hi,

I have to convert the Run_date of the CSV file as date but its populating as string. Please help me..!!

Sample CSV file;
30-NOV-17,GH,381,CUSTOMERS
30-NOV-17,GH,50047,TRANSACTIONS
30-NOV-17,IQ,0,TRANSACTIONS
30-NOV-17,KE,631,CUSTOMERS
30-NOV-17,KE,95374,TRANSACTIONS

Below is the config file -

cat logstconf.conf
input {
file {
path => "/tmp/filename.csv"
type => "csv"
start_position => "beginning"
sincedb_path => "/dev/null"
}
}
filter {
csv {
separator => ","
columns => ["Run_date","Cntry_code","Count","Cust_OR_TRAN"]
skip_empty_columns => true

}

date {
match => ["Run_date", "dd-MMM-yy"]
}

mutate{
convert => { "Count" => "integer" }
}
mutate{
add_field => {
"application" => "appnme"
}
}
mutate{
add_field => {
"Environment" => "prd"
}
}

}
output {
elasticsearch{
action => "index"
#hosts => "http://localhost:9200"
hosts => "http://localhost:9200"
index => "indexname"
workers => 1

}
stdout {
    codec => rubydebug
}

}


(Kurt Schraeyen) #2

I think you need to add "target => "Run_date" to the date filter.


(Sridhar B) #3

Thanks.. will try and let you know..


(Sridhar B) #4

hi,

I have tried the target as below.

date {
match => ["Run_date", "dd-MMM-yy"]
target => "Run_date"
}

good news is its accept the Run_date as date. but its capturing the time in 24hrs format.

below is the Logstash output.
{
"path" => "filename.csv",
"@timestamp" => 2017-12-18T05:37:52.874Z,
"Cust_OR_TRAN" => "CUSTOMERS",
"@version" => "1",
"Environment" => "PRD",
"Run_date" => 2017-12-05T16:00:00.000Z,
"Count" => 1225,
"message" => "06-DEC-17,ZM,1225,CUSTOMERS",
"type" => "csv",
"Cntry_code" => "ZM"
}

I'm expecting the output as like as in the message "06-DEC-17". but actually it takes as " "Run_date" => 2017-12-05T16:00:00.000Z,"

Please help.!!


(Magnus Bäck) #5

The date filter parses strings into UTC timestamps with millisecond precision. Presumably your timezone is UTC+8, and 2017-12-06T00:00:00,000+0800 => 2017-12-05T16:00:00,000+0000 so things look fine.


(Sridhar B) #6

Hi,

thanks.! can you help me to get only the date in dd-MMM-yy format [as like as in CSV.]

the problem why I want to convert is, I'm unable to sort the date as asend / descn while I visualize the same in Kibana. because this capture as a string..


(Kurt Schraeyen) #7

You can change the way the date is displayed in Kibana in it's settings. In Kibana under "Management" -> "advanced settings" -> dateForm


(Sridhar B) #8

Hi.. I have changed the settings in advanced settings and now I can get the date in kibana like DD-MMM-YY. now the problem is as I mentioned earlier the date is converted based on TZ and shows the different date.

original ---
"message" => "06-DEC-17,ZM,1225,CUSTOMERS", conversation ---
"Run_date" => 2017-12-05T16:00:00.000Z,

Pls help me here..!!


(Kurt Schraeyen) #9

The configuration you posted is not applying a timezone. Are you applying the correct timezone in Logstash in the date filter?


(Sridhar B) #10

No.. I'm not configuring any timezone.. it takes default I believe....


(Kurt Schraeyen) #11

Date fields are always stored in UTC, so it's only normal they are displayed that way in JSON. When your local time is 06-DEC-17 and logstash adds 00:00 for hours and minutes, the UTC time for that is 2017-12-05T16:00:00.000Z. Kibana is smart enough to translate that back to your local time, and so should any other program.


(Sridhar B) #12

Can you help me to configuring kibana to translate back to local time please...!! I just modified the display option in advanced settings like DD-MMM-YY...


(Magnus Bäck) #13

The display option has nothing to do with this. The timezone adjustment option ("dateFormat:tz") is set to "browser" by default and that's the behavior you're looking for.


(system) #14

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.