# Sql\_Last\_Value date format seems to be irrelevant

**URL:** <https://discuss.elastic.co/t/sql-last-value-date-format-seems-to-be-irrelevant/217790>\
**Category:** Logstash\
**Created:** [February 4, 2020, 11:32am UTC](https://discuss.elastic.co/t/sql-last-value-date-format-seems-to-be-irrelevant/217790 "2020-02-04T11:32:55Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mohan\_vel](https://avatars.discourse-cdn.com/v4/letter/m/49beb7/32.png) [@Mohan\_vel](https://discuss.elastic.co/u/Mohan_vel)\
**Post date:** [February 4, 2020, 11:32am UTC](https://discuss.elastic.co/t/sql-last-value-date-format-seems-to-be-irrelevant/217790/1 "2020-02-04T11:32:55Z")

</div>

Hi All i am using logstash to index document from MSSQL server to elasticsearch, i am using below config file in order to do an incremental indexing, for that i am using using column called modified\_date but having problem with dateformat.

```
input {
jdbc {
jdbc_driver_library => "D:/Users/xxxxx/Desktop/driver/mssql-jdbc-7.4.1.jre12-shaded.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxxx;"
jdbc_user => "xxx"
jdbc_password => "xxxx"
jdbc_paging_enabled => true
tracking_column => modified_date
use_column_value => true
clean_run => true
tracking_column_type => "timestamp"
schedule => "*/1 * * * *"
statement => "Select pl.policynumber,pl.policyholdername,pl.dob,pl.age,pl.client_address clientaddress,cl.claimnumber,Cl.claimtype,cl.modified_date modified_date,Cl.is_active from policy pl
inner join claim Cl on Cl.policynumber=pl.policynumber where cl.modified_date >:sql_last_value"
}
}
filter {
if [is_active] {
        mutate {    
            add_field => {
                "[@metadata][elasticsearch_action]" => "index"
            }
        }
        mutate {
            remove_field => ["is_active","@version","@timestamp"]
        }
    } else {
        mutate {    
            add_field => {
                "[@metadata][elasticsearch_action]" => "delete"
            }
        }
        mutate {
            remove_field => ["is_active","@version","@timestamp"]
        }
} 
}
output {
elasticsearch {
hosts => "https://e5a4a4a4de7940d9b12674d62eac9762.eastus2.azure.elastic-cloud.com:9243"
user => "elastic"
password => "xxxxx"
index => "xxxx"
action => "%{[@metadata][elasticsearch_action]}"
document_type => "_doc"
document_id => "%{claimnumber}"

}
stdout { codec => rubydebug }
}

```

Attached screenshot for reference

 ![Untitled](https://us1.discourse-cdn.com/elastic/original/3X/3/7/37eb54dad6947eaefb09d399fb9a13520a1623dd.png)

Date format seems to be wrong due to that each time it is picking all the documents instead of modified one refer attached screenshot for more clarity. Should some one provide insight on this issue?

---

<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:** [February 4, 2020, 5:02pm UTC](https://discuss.elastic.co/t/sql-last-value-date-format-seems-to-be-irrelevant/217790/2 "2020-02-04T17:02:17Z")

</div>

SQL Server [supports](https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver15#supported-string-literal-formats-for-datetime2) ISO 8601 date formats. What makes you think this is not working?

---

<div class="post-metadata">

**Author:** ![Mohan\_vel](https://avatars.discourse-cdn.com/v4/letter/m/49beb7/32.png) [@Mohan\_vel](https://discuss.elastic.co/u/Mohan_vel)\
**Post date:** [February 4, 2020, 5:33pm UTC](https://discuss.elastic.co/t/sql-last-value-date-format-seems-to-be-irrelevant/217790/3 "2020-02-04T17:33:27Z")

</div>

Hi @Badger  
I have upgraded my logstash version from 7.2.0 to 7.5.2  
In my SQL table last updated value is highlighted below.

 ![Untitled](https://us1.discourse-cdn.com/elastic/original/3X/b/6/b6f1d977de26f64fa0f33deb7c45153a46b29957.png)

when i was checking in .jdbc\_last\_run file after running logstash, modified\_date column value is seems to be irrelevant one attached screenshot for your reference

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/a/4/a4933215bbb2373db45d3d18af6f9ae4b8d64484.png)

Date has updated correctly but time looks like Evening 5 PM **2020-02-04 17:24:08.165000000 Z** but actually it should be like this **2020-02-04 22:44:29.673000000 Z**

i was unable to find the reason why time getting updated wrongly. In my previous version 7.2.0 it was working fine once i upgrade this to latest version this problem got arised due to this i was unable to perform incremental indexing.

Config file has already given. Kindly suggest how to resolve this issue?

---

<div class="post-metadata">

**Author:** ![shekarkola](https://avatars.discourse-cdn.com/v4/letter/s/f05b48/32.png) [@shekarkola](https://discuss.elastic.co/u/shekarkola)\
**Post date:** [February 5, 2020, 8:29am UTC](https://discuss.elastic.co/t/sql-last-value-date-format-seems-to-be-irrelevant/217790/4 "2020-02-05T08:29:04Z")

</div>

@Mohan_vel

Is the variance between your source data (in sql table) and the `sql_last_value` reflects same as the variance between your timezone and the UTC?

if so, as workaround you can consider that within `statement`. or you may use the additional parameter [`jdbc_default_timezone`](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-jdbc_default_timezone)

---

<div class="post-metadata">

**Author:** ![Mohan\_vel](https://avatars.discourse-cdn.com/v4/letter/m/49beb7/32.png) [@Mohan\_vel](https://discuss.elastic.co/u/Mohan_vel)\
**Post date:** [February 5, 2020, 9:08am UTC](https://discuss.elastic.co/t/sql-last-value-date-format-seems-to-be-irrelevant/217790/5 "2020-02-05T09:08:14Z")

</div>

Hi @shekarkola

As suggested i have added default timezone it's working fine. Thanks a lot config file below for your reference.

```
input {
jdbc {
jdbc_driver_library => ""
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://EC2AMAZ-J90JR4A\SQLEXPRESS:1433;databaseName=xxxx;"
jdbc_user => "xxxx"
jdbc_password => "xxx"
jdbc_paging_enabled => true
tracking_column => modified_date
use_column_value => true
clean_run => true
tracking_column_type => "timestamp"
schedule => "*/1 * * * *"
statement => "Select* from claim where modified_date >:sql_last_value"
last_run_metadata_path => "D:\Users\xxxx\Desktop\logstash-7.2.0\jdbc_lastrun\jdbc_last_run.txt"
jdbc_default_timezone => "UTC" 
}
}
filter {
mutate {
   remove_field => ["@version","@timestamp"]
 }
}
output {

stdout { codec => rubydebug }
}
```

---

<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:** [March 4, 2020, 9:08am UTC](https://discuss.elastic.co/t/sql-last-value-date-format-seems-to-be-irrelevant/217790/6 "2020-03-04T09:08:14Z")

</div>

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