CSV field from string to Date

(Sridhar B) #1


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

Sample CSV file;

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"]

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

output {
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


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,
"@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


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.