# Datetime from sql to Timestamp logstash

**URL:** <https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594>\
**Category:** Logstash\
**Created:** [January 30, 2018, 9:58am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594 "2018-01-30T09:58:41Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Anuar\_Mukatov](https://avatars.discourse-cdn.com/v4/letter/a/dec6dc/32.png) [@Anuar\_Mukatov](https://discuss.elastic.co/u/Anuar_Mukatov)\
**Post date:** [January 30, 2018, 9:58am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/1 "2018-01-30T09:58:41Z")

</div>

Hello everybody!  
Help me please with @timestamp, i have jdbc input with mssql server, and in my output i have variable - datetime, which include time of created table. How i can use it instead @timestamp?  
this is my conf file:

```
input {
 jdbc {
 jdbc_driver_library => "/etc/logstash/drivers/sqljdbc42.jar"
 jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
 jdbc_connection_string => "jdbc:sqlserver://server:1433;databasename=db"
 jdbc_user => "login"
 jdbc_password => "pass"
 statement => "SELECT m.*
 , mc.nameRu AS CategoryNameRu
, mt.nameRu AS TypeNameRu
, ms.nameRu AS SourceNameRu
, p.Fio_Ru
, s.RowName AS LoginName
, pos.FullNameRu AS PositionNameRu
  FROM LOG_Messages m
  JOIN DIC_LOG_MESSAGE_SOURCE_TO_TYPE mst ON mst.id = m.refMessageSourceType
  JOIN DIC_LOG_MESSAGE_CATEGORY mc ON mc.id = mst.refMessageCategory
  JOIN DIC_LOG_MESSAGE_TYPE mt ON mt.id = mst.refMessageType
  JOIN DIC_LOG_MESSAGE_SOURCE ms ON ms.id = mst.refMessageSource
  LEFT JOIN ULS_Persons p ON p.id = m.refRecordCard
  LEFT JOIN LOG_SidIdentification s ON s.id = m.refSid
  LEFT JOIN ULS_SubdivisionPositions pos ON pos.id = m.refPosition
  ORDER BY id DESC"
    }
  }   

  filter {
   mutate {
add_field => { "message" => "%{typenameru}" }
            convert => ["datetime", "string"]
  copy => { "datetime" => "@timestamp" }
  }
   	}
  output {
    gelf{
         	host => "0.0.0.0"
            port => 12231
            short_message => 'short_message'
            }
  stdout { codec => rubydebug}
     }
```

---

<div class="post-metadata">

**Author:** ![ericohtake](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ericohtake/32/24539_2.png) [@ericohtake](https://discuss.elastic.co/u/ericohtake)\
**Post date:** [January 30, 2018, 10:20am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/2 "2018-01-30T10:20:53Z")

</div>

You can use the plug-in "date" inside your filter. When you use it to parse your custom date field, it automatically generates the @timestamp field for your events. You don't need to add, convert or copy your original field like the example you posted.

---

<div class="post-metadata">

**Author:** ![Anuar\_Mukatov](https://avatars.discourse-cdn.com/v4/letter/a/dec6dc/32.png) [@Anuar\_Mukatov](https://discuss.elastic.co/u/Anuar_Mukatov)\
**Post date:** [January 30, 2018, 10:23am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/3 "2018-01-30T10:23:51Z")

</div>

Can you please help me, how to use this plugin in my case?

---

<div class="post-metadata">

**Author:** ![ericohtake](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ericohtake/32/24539_2.png) [@ericohtake](https://discuss.elastic.co/u/ericohtake)\
**Post date:** [January 30, 2018, 10:31am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/4 "2018-01-30T10:31:04Z")

</div>

Try the documentation, they have lots of examples and it is a really easy plug-in to use.  
[https://www.elastic.co/guide/en/logstash/current/plugins-filters-date.html](https://www.elastic.co/guide/en/logstash/current/plugins-filters-date.html)

Try it out and let me know if you have problems.

---

<div class="post-metadata">

**Author:** ![Anuar\_Mukatov](https://avatars.discourse-cdn.com/v4/letter/a/dec6dc/32.png) [@Anuar\_Mukatov](https://discuss.elastic.co/u/Anuar_Mukatov)\
**Post date:** [January 30, 2018, 10:49am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/5 "2018-01-30T10:49:00Z")

</div>

I write this -

```
    date {
     match => ["datetime", "YYYY-MM-dd HH:mm:ss.SSS"]
     target => "@timestamp"
    }

```

but in output:  
datetime - 2018-01-29T22:16:59.537Z  
timestamp - 2018-01-30T10:47:36.247Z

---

<div class="post-metadata">

**Author:** ![nomedx](https://avatars.discourse-cdn.com/v4/letter/n/f4b2a3/32.png) [@nomedx](https://discuss.elastic.co/u/nomedx)\
**Post date:** [January 30, 2018, 11:05am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/6 "2018-01-30T11:05:54Z")

</div>

Hi Anuar,  
I have the same problem with Cassandra:  
this is the query in statement:  
statement =\> "select \* from mydatabase.products\_to\_elastic WHERE lastmodified \> :sql\_last\_value"  
and I obtain this error:

[2018-01-30T12:01:00,702][ERROR][logstash.inputs.jdbc] Java::ComDatastaxDriverCoreExceptions::InvalidQueryException: Unable to coerce '2018-01-30 11:59:02.709784' to a formatted date (long): select \* from mydatabase.products\_to\_elastic WHERE lastmodified \> '2018-01-30 11:59:02.709784' ALLOW FILTERING

I used this filter:  
filter {  
date {  
match =\> ["lastmodified", "yyyy-MM-dd HH:mm:ss.SSS"]  
}  
}

but I don't understand if the filter works with input or only with data in output.

---

<div class="post-metadata">

**Author:** ![Anuar\_Mukatov](https://avatars.discourse-cdn.com/v4/letter/a/dec6dc/32.png) [@Anuar\_Mukatov](https://discuss.elastic.co/u/Anuar_Mukatov)\
**Post date:** [January 30, 2018, 11:11am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/7 "2018-01-30T11:11:43Z")

</div>

Hello!  
Did you try without filter?

---

<div class="post-metadata">

**Author:** ![nomedx](https://avatars.discourse-cdn.com/v4/letter/n/f4b2a3/32.png) [@nomedx](https://discuss.elastic.co/u/nomedx)\
**Post date:** [January 30, 2018, 11:26am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/8 "2018-01-30T11:26:09Z")

</div>

Yes, but the same error.

---

<div class="post-metadata">

**Author:** ![Anuar\_Mukatov](https://avatars.discourse-cdn.com/v4/letter/a/dec6dc/32.png) [@Anuar\_Mukatov](https://discuss.elastic.co/u/Anuar_Mukatov)\
**Post date:** [January 30, 2018, 11:49am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/9 "2018-01-30T11:49:43Z")

</div>

can you paste your conf file?

---

<div class="post-metadata">

**Author:** ![Anuar\_Mukatov](https://avatars.discourse-cdn.com/v4/letter/a/dec6dc/32.png) [@Anuar\_Mukatov](https://discuss.elastic.co/u/Anuar_Mukatov)\
**Post date:** [January 30, 2018, 11:50am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/10 "2018-01-30T11:50:15Z")

</div>

Can you help me please! =)

---

<div class="post-metadata">

**Author:** ![ericohtake](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ericohtake/32/24539_2.png) [@ericohtake](https://discuss.elastic.co/u/ericohtake)\
**Post date:** [January 31, 2018, 7:08am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/11 "2018-01-31T07:08:42Z")

</div>

I assume your original field looks like this: **2018-01-29 22:16:59.537**

The date filter takes in consideration your platform locale. If you want to see the events as it comes, you can use the time zone setting. And you don't need to set the target to the @timestamp. This is the plugin default.

```
filter {
  date {
     match => ["message", "YYYY-MM-dd HH:mm:ss.SSS"] #2018-01-29 22:16:59.537
     timezone => "Etc/UTC"
    }
}

```

This is the output:

```
[2018-01-31T16:00:38,404][INFO][logstash.agent] 
2018-01-29 22:16:59.537
{
       "message" => "2018-01-29 22:16:59.537",
    "@timestamp" => 2018-01-29T22:16:59.537Z
}

```

You have to decide if you want to leave this config as is or not, depending on how and where you are visualizing your data. For me, Kibana sets the timestamp on screen by the users browser's locale, so I never set the time zone on Logstash.

---

<div class="post-metadata">

**Author:** ![Anuar\_Mukatov](https://avatars.discourse-cdn.com/v4/letter/a/dec6dc/32.png) [@Anuar\_Mukatov](https://discuss.elastic.co/u/Anuar_Mukatov)\
**Post date:** [January 31, 2018, 10:01am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/12 "2018-01-31T10:01:29Z")

</div>

I use Graylog.

---

<div class="post-metadata">

**Author:** ![Anuar\_Mukatov](https://avatars.discourse-cdn.com/v4/letter/a/dec6dc/32.png) [@Anuar\_Mukatov](https://discuss.elastic.co/u/Anuar_Mukatov)\
**Post date:** [January 31, 2018, 12:44pm UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/13 "2018-01-31T12:44:30Z")

</div>

I check logstash conf file with debug and i saw that, i have two variables with time, first datetime - it is time from DB and second @timestamp. Can i replase timestamp value with datetime values?

---

<div class="post-metadata">

**Author:** ![ericohtake](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ericohtake/32/24539_2.png) [@ericohtake](https://discuss.elastic.co/u/ericohtake)\
**Post date:** [January 31, 2018, 12:59pm UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/14 "2018-01-31T12:59:57Z")

</div>

I didn't quite understand your question. Isn't it what we have been doing on the messages above?  
All the examples were already given. What is exactly the problem?

When you use the date filter plugin you have to give it the field where your date is, so you can parse it. Logstash will automatically put the parsed date on the @timestamp field. You can use target if you want to send it to another field though, that is optional.

After you have done that, if you don't need your "datetime" field anymore, (because you send it parsed to the @timestamp field), you can remove it.

---

<div class="post-metadata">

**Author:** ![Anuar\_Mukatov](https://avatars.discourse-cdn.com/v4/letter/a/dec6dc/32.png) [@Anuar\_Mukatov](https://discuss.elastic.co/u/Anuar_Mukatov)\
**Post date:** [January 31, 2018, 3:31pm UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/15 "2018-01-31T15:31:27Z")

</div>

What you write before, i do it, but no any changes in timestamp. Can you please write what exactly and how i must write it?

---

<div class="post-metadata">

**Author:** ![Anuar\_Mukatov](https://avatars.discourse-cdn.com/v4/letter/a/dec6dc/32.png) [@Anuar\_Mukatov](https://discuss.elastic.co/u/Anuar_Mukatov)\
**Post date:** [February 1, 2018, 3:11am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/16 "2018-02-01T03:11:52Z")

</div>

this us my debug example -

> {  
> "sourcenameru" =\> "Задачи",  
> "loginname" =\> "OrderPointStatement",  
> "refrvsproperties" =\> nil,  
> "refmessagesourcetype" =\> 542,  
> "message" =\> "Действие",  
> "positionnameru" =\> nil,  
> "content" =\> "Start ParagraphExecutor.Execute",  
> "datetime" =\> 2014-12-31T18:00:00.463Z,  
> "@timestamp" =\> 2018-02-01T06:26:05.376Z,  
> "refposition" =\> nil,  
> "clientipaddress" =\> "",  
> "refrecordcard" =\> nil,  
> "@version" =\> "1",  
> "id" =\> 14232235,  
> "refarchive" =\> 50,  
> "refsid" =\> 3726,  
> "categorynameru" =\> "Информация",  
> "typenameru" =\> "Действие",  
> "fio\_ru" =\> nil  
> }

And i need to replace them.

---

<div class="post-metadata">

**Author:** ![vaidehi9039](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vaidehi9039/32/25891_2.png) [@vaidehi9039](https://discuss.elastic.co/u/vaidehi9039)\
**Post date:** [February 1, 2018, 10:15am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/17 "2018-02-01T10:15:12Z")

</div>

Hi,

I am also facing the same issue. I am unable to parse @timestamp value as my wso2 server timestamp.

Below is my logstash conf file.

input {  
beats {  
port =\> 5044  
}  
}

filter {  
grok {  
match =\> ["message", "TID:%{SPACE}[%{INT:tenant\_id}]%{SPACE}[]%{SPACE}[%{TIMESTAMP\_ISO8601:event\_timestamp}]%{SPACE}%{LOGLEVEL:level}%{SPACE}{%{JAVACLASS:java\_class}}%{SPACE}-%{SPACE}%{GREEDYDATA:log\_message}" ]  
}

```
	date {
		match => ["message", "yyyy-MM-dd'T'HH:mm:ss.SSSZ"]
		timezone => "Etc/UTC"
	}

```

}

output {  
elasticsearch {  
hosts =\> "localhost:9200"  
manage\_template =\> false  
index =\> "%{[@metadata][beat]}-%{+YYYY.MM.dd}"  
document\_type =\> "%{[@metadata][type]}"  
}  
}

In kibana:

| @timestamp | | February 1st 2018, 15:39:18.937 |
| --- | --- | --- |
| t @version | | 1 |
| t \_id | | 9xvXMFEB-1-QqhW1ljvK |
| t \_index | | filebeat-2018.02.01 |
| # \_score | | - |
| t \_type | | doc |
| t event\_timestamp | | 2018-02-01 15:39:16,016 |
| t java\_class | | org.wso2.carbon.core.init.CarbonServerManager |
| t level | | INFO |
| t log\_message | | Halting JVM {org.wso2.carbon.core.init.CarbonServerManager} |
| t message | | TID: [-1254] [] [2018-02-01 15:39:16,016] INFO {org.wso2.carbon.core.init.CarbonServerManager} - Halting JVM {org.wso2.carbon.core.init.CarbonServerManager} |
| t prospector.type | | log |
| t source | | C:\Softwares\WSO2\wso2ei-6.1.0\wso2ei-6.1.0\repository\logs\wso2carbon.log |
| t tags | | beats\_input\_codec\_plain\_applied, \_dateparsefailure |
| t tenant\_id | | -1234 |

Please help me.

Even this date filter also not working.

date {  
match =\> ["event\_timestamp" , "yyyy-MM-dd'T'HH:mm:ss.SSSZ"]  
target =\> "@timestamp"  
add\_field =\> { "debug" =\> "timestampMatched"}  
}

---

<div class="post-metadata">

**Author:** ![ericohtake](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ericohtake/32/24539_2.png) [@ericohtake](https://discuss.elastic.co/u/ericohtake)\
**Post date:** [February 1, 2018, 10:22am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/18 "2018-02-01T10:22:02Z")

</div>

Anuar, always post examples of your data as you did now. Much easier to try to help. 😀

Your datetime field is in the format, " **ISO8601**".  
Try this:

```
filter {
  date {
     match => ["datetime", "ISO8601"]
     timezone => "Etc/UTC"
    }
}

```

[https://www.elastic.co/guide/en/logstash/current/plugins-filters-date.html#plugins-filters-date-match](https://www.elastic.co/guide/en/logstash/current/plugins-filters-date.html#plugins-filters-date-match)

---

<div class="post-metadata">

**Author:** ![ericohtake](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ericohtake/32/24539_2.png) [@ericohtake](https://discuss.elastic.co/u/ericohtake)\
**Post date:** [February 1, 2018, 10:25am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/19 "2018-02-01T10:25:48Z")

</div>

Would you mind opening a new thread for your issue? It is important to post also a sample of the data you are trying to process in Logstash. And check the answer for Anuar. It might work for you too. If not, open a new thread please.

---

<div class="post-metadata">

**Author:** ![Anuar\_Mukatov](https://avatars.discourse-cdn.com/v4/letter/a/dec6dc/32.png) [@Anuar\_Mukatov](https://discuss.elastic.co/u/Anuar_Mukatov)\
**Post date:** [February 1, 2018, 11:09am UTC](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594/20 "2018-02-01T11:09:43Z")

</div>

How i can change example to GMT-6?

[Next page](https://discuss.elastic.co/t/datetime-from-sql-to-timestamp-logstash/117594.md?page=2)