# Double convert timezone when parse datetime from sql

**URL:** <https://discuss.elastic.co/t/double-convert-timezone-when-parse-datetime-from-sql/177646>\
**Category:** Logstash\
**Created:** [April 19, 2019, 4:08pm UTC](https://discuss.elastic.co/t/double-convert-timezone-when-parse-datetime-from-sql/177646 "2019-04-19T16:08:42Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![4e1e44b5126c14a3c514](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/4e1e44b5126c14a3c514/32/43527_2.png) [@4e1e44b5126c14a3c514](https://discuss.elastic.co/u/4e1e44b5126c14a3c514)\
**Post date:** [April 19, 2019, 4:08pm UTC](https://discuss.elastic.co/t/double-convert-timezone-when-parse-datetime-from-sql/177646/1 "2019-04-19T16:08:42Z")

</div>

I have a table with such columns:

```
|utcDateTime|hash|Type|tran|Code|
|2019-04-19 14:53:36.000|d893b735ed52413d9b3ac2109961463f|NULL|NULL|NULL|

```

When I filter them through logstash with jdbc {} input plugin, the first column(utcDateTime) is decreases by another 3 hours (I'm at the UTC+3). And output looks like:  
`{"@version":"1","utcdatetime":"2019-04-19T11:53:36.000Z","@timestamp":"2019-04-19T11:53:36.000Z"}`

Config:

```
filter {
	mutate {
		convert => {"utcdatetime" => "string"}
	}
	date {
 		match => ["utcdatetime", "ISO8601"]
	}
}

```

What i did wrong?

---

<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:** [April 19, 2019, 4:52pm UTC](https://discuss.elastic.co/t/double-convert-timezone-when-parse-datetime-from-sql/177646/2 "2019-04-19T16:52:57Z")

</div>

You might be able to fix it like [this](https://discuss.elastic.co/t/error-logstash-inputs-jdbc-unable-to-connect-to-database/177091/4?u=badger). Or you could add a timezone option to the date filter to force it to move the timestamp forward three hours.

---

<div class="post-metadata">

**Author:** ![4e1e44b5126c14a3c514](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/4e1e44b5126c14a3c514/32/43527_2.png) [@4e1e44b5126c14a3c514](https://discuss.elastic.co/u/4e1e44b5126c14a3c514)\
**Post date:** [April 22, 2019, 7:34am UTC](https://discuss.elastic.co/t/double-convert-timezone-when-parse-datetime-from-sql/177646/3 "2019-04-22T07:34:12Z")

</div>

Unfortunately. It's not work.  
I tried:

1. Add `jdbc_default_timezone => "UTC"` to input part.
2. Add `timezone => "Europe/Moscow"` in filter part.
3. Example with `useTimezone=true&useLegacyDatetimeCode=false&serverTimezone=UTC` not work on MSSQL.
4. Tried all options in same time.

Example:

```
input {
	jdbc {
		statement => "SELECT [utcDateTime] FROM [dbo].[LogRecords] WHERE utcDateTime > '2019/22/04 7:00' AND error IS NOT NULL"
		jdbc_driver_library => "E:\logstash-6.7.1\sqljdbc_7.2\rus\mssql-jdbc-7.2.2.jre8.jar"
		jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
		jdbc_connection_string => "jdbc:sqlserver://192.168.2.20:1433;databaseName=LogIndex;integratedSecurity=false"
		jdbc_default_timezone => "Europe/Moscow"
		last_run_metadata_path => "E:\logstash-6.7.1\last_run.metadata"
  }
}
filter {
	mutate {
		convert => {"utcdatetime" => "string"}
		remove_field => [
			"[@version]"
				]
	}
	date {
 		match => ["utcdatetime", "ISO8601"]
		timezone => "Europe/Moscow"
	}
}
output {
	stdout{}
}

```

Output:

```
{
    "utcdatetime" => "2019-04-22T04:00:18.000Z",
     "@timestamp" => 2019-04-22T04:00:18.000Z
}

```

When a SQL record time 7:00:18 and realTime 10:00:18

---

<div class="post-metadata">

**Author:** ![4e1e44b5126c14a3c514](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/4e1e44b5126c14a3c514/32/43527_2.png) [@4e1e44b5126c14a3c514](https://discuss.elastic.co/u/4e1e44b5126c14a3c514)\
**Post date:** [April 22, 2019, 7:43am UTC](https://discuss.elastic.co/t/double-convert-timezone-when-parse-datetime-from-sql/177646/4 "2019-04-22T07:43:16Z")

</div>

My fault.  
With line `jdbc_default_timezone => "UTC"` in input works.

---

<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:** [May 20, 2019, 7:43am UTC](https://discuss.elastic.co/t/double-convert-timezone-when-parse-datetime-from-sql/177646/5 "2019-05-20T07:43:19Z")

</div>

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