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

**URL:** <https://discuss.elastic.co/t/how-to-convert-a-timestamp-6-field-into-an-integer/150923>\
**Category:** Logstash\
**Created:** [October 3, 2018, 5:01pm UTC](https://discuss.elastic.co/t/how-to-convert-a-timestamp-6-field-into-an-integer/150923 "2018-10-03T17:01:48Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![OphyTe](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ophyte/32/36444_2.png) [@OphyTe](https://discuss.elastic.co/u/OphyTe)\
**Post date:** [October 3, 2018, 5:01pm UTC](https://discuss.elastic.co/t/how-to-convert-a-timestamp-6-field-into-an-integer/150923/1 "2018-10-03T17:01:48Z")

</div>

I get a timestamp(6) from an Oracle database with the [input-jdbc plugin](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html). 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](https://www.elastic.co/guide/en/logstash/current/plugins-filters-date.html#plugins-filters-date-match))!

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](https://www.elastic.co/guide/en/logstash/current/plugins-filters-mutate.html#plugins-filters-mutate-convert) but I lost the milliseconds accuracy.

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

```

Thanks for your help

---

<div class="post-metadata">

**Author:** ![AquaX](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aquax/32/92006_2.png) [@AquaX](https://discuss.elastic.co/u/AquaX)\
**Post date:** [October 4, 2018, 11:43am UTC](https://discuss.elastic.co/t/how-to-convert-a-timestamp-6-field-into-an-integer/150923/2 "2018-10-04T11:43:32Z")

</div>

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 🙂

```auto
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](https://www.google.ca/search?safe=off&ei=x_q1W5aiA5yjjwT32YSYBA&q=oracle+sql+timestamp+to+unixtime+ms&oq=oracle+sql+timestamp+to+unixtime+ms)

---

<div class="post-metadata">

**Author:** ![OphyTe](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ophyte/32/36444_2.png) [@OphyTe](https://discuss.elastic.co/u/OphyTe)\
**Post date:** [October 4, 2018, 1:16pm UTC](https://discuss.elastic.co/t/how-to-convert-a-timestamp-6-field-into-an-integer/150923/3 "2018-10-04T13:16:24Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AquaX](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aquax/32/92006_2.png) [@AquaX](https://discuss.elastic.co/u/AquaX)\
**Post date:** [October 4, 2018, 2:24pm UTC](https://discuss.elastic.co/t/how-to-convert-a-timestamp-6-field-into-an-integer/150923/4 "2018-10-04T14:24:35Z")

</div>

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

Source:

> <https://stackoverflow.com/questions/31652232/how-to-get-millis-of-timestamp-since-1970-utc-in-oracle-sql>

---

<div class="post-metadata">

**Author:** ![OphyTe](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ophyte/32/36444_2.png) [@OphyTe](https://discuss.elastic.co/u/OphyTe)\
**Post date:** [October 5, 2018, 3:28pm UTC](https://discuss.elastic.co/t/how-to-convert-a-timestamp-6-field-into-an-integer/150923/5 "2018-10-05T15:28:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AquaX](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aquax/32/92006_2.png) [@AquaX](https://discuss.elastic.co/u/AquaX)\
**Post date:** [October 5, 2018, 3:29pm UTC](https://discuss.elastic.co/t/how-to-convert-a-timestamp-6-field-into-an-integer/150923/6 "2018-10-05T15:29:57Z")

</div>

Cool!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 2, 2018, 3:30pm UTC](https://discuss.elastic.co/t/how-to-convert-a-timestamp-6-field-into-an-integer/150923/7 "2018-11-02T15:30:01Z")

</div>

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