# Replace @timestamp value with SQL DB datetime value

**URL:** <https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748>\
**Category:** Logstash\
**Created:** [March 13, 2018, 2:19pm UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748 "2018-03-13T14:19:08Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![zakkhan](https://avatars.discourse-cdn.com/v4/letter/z/b5a626/32.png) [@zakkhan](https://discuss.elastic.co/u/zakkhan)\
**Post date:** [March 13, 2018, 2:19pm UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/1 "2018-03-13T14:19:09Z")

</div>

I have tried everything but nothing worked. I need to replace @timestamp with my Datetime column value.

Format of both values  
**Datetime: 2017-08-23T13:20:09.000Z**  
**@timesatmp: 2018-03-13T12:11:38.080Z**

input {  
jdbc {  
jdbc\_driver\_library =\> "C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc\_6.0\enu\jre8\sqljdbc42.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
jdbc\_connection\_string =\> "jdbc:sqlserver://serverName:Port;user=user;password=password;database=DBNAME;"  
jdbc\_user =\> "user"  
jdbc\_password =\> "password"  
statement =\> "SELECT 'BizTalk Application' As PlatformType,[ServiceName] AS Platform,[MachineName] AS HOST,**[DateTime],**[ExceptionMessage] As Message FROM [QAEsbExceptionDb].[dbo].[Fault]"  
type =\> "esblogs"  
}  
beats{  
port=\> "5044"  
}  
}

filter {  
mutate{  
add\_field =\> ["Received\_at", "%{@timestamp}"]  
convert =\> ["DateTime", "string"]  
}  
date{  
match =\> ["DateTime", "YYYY-MM-dd HH:mm:ss.SSSZ"]  
target =\> "@timestamp"  
}  
}

output{  
stdout { codec =\> rubydebug }  
}

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [March 13, 2018, 2:36pm UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/2 "2018-03-13T14:36:56Z")

</div>

> [@zakkhan](#):
>
> match =\> ["DateTime", "YYYY-MM-dd HH:mm:ss.SSSZ"]

That does not match the format you have given for DateTime. Try

```
match => ["DateTime", "YYYY-MM-dd'T'HH:mm:ss.SSS'Z'"]
```

---

<div class="post-metadata">

**Author:** ![zakkhan](https://avatars.discourse-cdn.com/v4/letter/z/b5a626/32.png) [@zakkhan](https://discuss.elastic.co/u/zakkhan)\
**Post date:** [March 13, 2018, 2:42pm UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/3 "2018-03-13T14:42:31Z")

</div>

I tried this but still no luck.

```
    "datetime" => 2017-11-03T10:18:54.000Z,
        "host" => "BTSBPMQA01",
        "type" => "esblogs",
    "@version" => "1",
"PlatformType" => "BizTalk Application",
  "@timestamp" => 2018-03-13T14:41:29.067Z,
```

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [March 13, 2018, 2:56pm UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/4 "2018-03-13T14:56:53Z")

</div>

Field names are case sensitive. Try

```
match => ["datetime", "YYYY-MM-dd'T'HH:mm:ss.SSS'Z'"]
```

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [March 13, 2018, 3:02pm UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/5 "2018-03-13T15:02:38Z")

</div>

As the `datetime` field is not formatted as a string (no surrounding `"`) in the rubydebug output, it could already be date object. You may be able to just copy it over without using the date plugin.

---

<div class="post-metadata">

**Author:** ![zakkhan](https://avatars.discourse-cdn.com/v4/letter/z/b5a626/32.png) [@zakkhan](https://discuss.elastic.co/u/zakkhan)\
**Post date:** [March 13, 2018, 3:27pm UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/6 "2018-03-13T15:27:47Z")

</div>

I tried this as well. But didn't work:  
mutate  
{  
copy=\> ["timestamp", "datetime"]  
}

Output:

```
  "datetime" => 2017-11-03T10:19:00.000Z,
      "host" => "BTSBPMQA01",
      "type" => "esblogs",
  "@version" => "1",
"PlatformType" => "BizTalk Application",
"@timestamp" => 2018-03-13T15:23:42.031Z,

```

and when i tried with this:  
mutate  
{  
copy=\> ["@timestamp", "datetime"]  
}  
It throws exceptiopn:  
Exception in thread "Ruby-0-Thread-54@[main]\>worker0: :1" java.lang.NullPointerException

---

<div class="post-metadata">

**Author:** ![vinodr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vinodr/32/28759_2.png) [@vinodr](https://discuss.elastic.co/u/vinodr)\
**Post date:** [March 14, 2018, 4:33am UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/7 "2018-03-14T04:33:06Z")

</div>

@zakkhan Can you try the below date filter:

```
  date {
    match => ["datetime", "ISO8601"]
  } 

```

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

 ![ISO8601](https://us1.discourse-cdn.com/elastic/original/3X/8/1/817e22cad91a5afbc3b82fb6a649e34a5b10a14d.PNG)

---

<div class="post-metadata">

**Author:** ![zakkhan](https://avatars.discourse-cdn.com/v4/letter/z/b5a626/32.png) [@zakkhan](https://discuss.elastic.co/u/zakkhan)\
**Post date:** [March 14, 2018, 10:55am UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/8 "2018-03-14T10:55:59Z")

</div>

Thanks but still not working.

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [March 14, 2018, 2:16pm UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/9 "2018-03-14T14:16:54Z")

</div>

Known issue, see [https://github.com/logstash-plugins/logstash-filter-date/issues/95](https://github.com/logstash-plugins/logstash-filter-date/issues/95) for workaround.

---

<div class="post-metadata">

**Author:** ![zakkhan](https://avatars.discourse-cdn.com/v4/letter/z/b5a626/32.png) [@zakkhan](https://discuss.elastic.co/u/zakkhan)\
**Post date:** [March 14, 2018, 2:20pm UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/10 "2018-03-14T14:20:05Z")

</div>

Is this the right way to access DB column i.e. [DateTime] in filter section.

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

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [March 14, 2018, 2:39pm UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/11 "2018-03-14T14:39:33Z")

</div>

> Is this the right way to access DB column i.e. [DateTime] in filter section.

Yes.

---

<div class="post-metadata">

**Author:** ![zakkhan](https://avatars.discourse-cdn.com/v4/letter/z/b5a626/32.png) [@zakkhan](https://discuss.elastic.co/u/zakkhan)\
**Post date:** [March 14, 2018, 3:00pm UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/12 "2018-03-14T15:00:45Z")

</div>

I am stuck. When i hard-code exact value of DateTime column in a temp field and use it instead of DateTime it works.

#DateTime = "2017-12-04 20:52:41.000"

mutate {  
add\_field =\> ["NewTimestamp","2017-12-04 20:52:41.000"]  
}  
date{  
match =\> ["NewTimestamp", "YYYY-MM-dd HH:mm:ss.SSS"]  
target =\> "@timestamp"  
}

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [March 14, 2018, 7:09pm UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/13 "2018-03-14T19:09:45Z")

</div>

Did you read the GitHub issue I posted a link to?

---

<div class="post-metadata">

**Author:** ![zakkhan](https://avatars.discourse-cdn.com/v4/letter/z/b5a626/32.png) [@zakkhan](https://discuss.elastic.co/u/zakkhan)\
**Post date:** [March 15, 2018, 8:17am UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/14 "2018-03-15T08:17:15Z")

</div>

Thanks. I was using wrong format of datetime conversion. My issue has been resolved now.

---

<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:** [April 12, 2018, 8:17am UTC](https://discuss.elastic.co/t/replace-timestamp-value-with-sql-db-datetime-value/123748/15 "2018-04-12T08:17:15Z")

</div>

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