How to convert a timestamp(6) field into an integer?

Elastic Stack Logstash
1

I get a timestamp(6) from an Oracle database with the input-jdbc plugin. This field is converted into Date when I put it in my elastic instance. But I need to have it in the UNIX_MS format (= number of milliseconds since 1st january 1970 - cf. Date filter plugin)!

In other words, I need Date "2018-08-14T09:08:40.764Z" to become "1534237720764" stored in an integer.

I tried the mutate-convert filter but I lost the milliseconds accuracy.

filter {
  mutate {
    convert => {
      "myDate" => "integer"
    }
}

Thanks for your help

2

Try this: Use the date{} filter to first create a date object for logstash and then use some ruby code to cast it into an integer :slight_smile:

date {
    match => ["myDate", "ISO8601"]
    target => "myDateObj"
}
ruby {    
    code => "event['unixDate'] = event['myDateObj'].to_i"  
}

I haven't had a chance to test it but in theory it should work.

Alternatively you could use SQL from your Oracle query to convert the timestamp to an integer before and then store that inside a logstash variable so you don't have to do the conversion with logstash. There are many tips on how to convert timestamp to unixtime on google.
https://www.google.ca/search?safe=off&ei=x_q1W5aiA5yjjwT32YSYBA&q=oracle+sql+timestamp+to+unixtime+ms&oq=oracle+sql+timestamp+to+unixtime+ms

3

Thank you @AquaX, I hadn't think about this possibility to make the conversion in the SQL query. I'm not sure which solution is the best though.

I think the date filter is not necessary in this case. This ruby expression is no more supported with the logstash version I use (6.4.1) :

Ruby exception occurred: Direct event field references (i.e. event['field']) have been disabled in favor of using event get and set methods (e.g. event.get('field'))

So I try this :

ruby {
  code => "event.set('unixDate', event.get('myDate').to_i)"
}

But I have the same behavior than with the convert filter : I lost the milliseconds ...

I tried with "to_r" (undefined in logstash), "to_s" and "to_f" but none of these give me what I want.

4

Try this then in your input SQL query:
SELECT (CAST(myDate AS DATE) - DATE '1970-01-01')2460601000 + MOD( EXTRACT( SECOND FROM SYSTIMESTAMP ), 1 ) * 1000 FROM DUAL

Source:

5

I finally use that trick :

ruby {
  code => "event.set('unixDate', (event.get('myDate').to_f.round(3)*1000).to_i)"
}

And it does the job.

Thanks anyway @AquaX.

6

Cool!

7

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