{"action":"created","issue":{"url":"https://api.github.com/repos/jprante/elasticsearch-jdbc/issues/519","labels_url":"https://api.github.com/repos/jprante/elasticsearch-jdbc/issues/519/labels{/name}","comments_url":"https://api.github.com/repos/jprante/elasticsearch-jdbc/issues/519/comments","events_url":"https://api.github.com/repos/jprante/elasticsearch-jdbc/issues/519/events","html_url":"https://github.com/jprante/elasticsearch-jdbc/issues/519","id":66209399,"number":519,"title":"Add timestamp field for Logstash & Kibana Visualization","user":{"login":"deusofnull","id":3485446,"avatar_url":"https://avatars.githubusercontent.com/u/3485446?v=3","gravatar_id":"","url":"https://api.github.com/users/deusofnull","html_url":"https://github.com/deusofnull","followers_url":"https://api.github.com/users/deusofnull/followers","following_url":"https://api.github.com/users/deusofnull/following{/other_user}","gists_url":"https://api.github.com/users/deusofnull/gists{/gist_id}","starred_url":"https://api.github.com/users/deusofnull/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/deusofnull/subscriptions","organizations_url":"https://api.github.com/users/deusofnull/orgs","repos_url":"https://api.github.com/users/deusofnull/repos","events_url":"https://api.github.com/users/deusofnull/events{/privacy}","received_events_url":"https://api.github.com/users/deusofnull/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":11,"created_at":"2015-04-03T19:02:50Z","updated_at":"2015-04-22T18:57:13Z","closed_at":null,"body":"Hey all.  I am trying to have a method of visualizing data from a SQL server using the ELK stack.   I am pulling the output of a SP in SQL into Elasticsearch using this JDBC River.  \r\n\r\n\r\n    curl -XPUT 'localhost:9200/_river/report_posting_ops/_meta' -d '{\r\n        \"type\": \"jdbc\",\r\n        \"schedule\": \"0 0/15 1-23 ? * *\",\r\n        \"jdbc\": {\r\n                \"url\": \"jdbc:sqlserver://host.example.com:1433;databaseName=dbname\",\r\n                \"locale\": true,\r\n                \"user\": \"dbuser\",\r\n                \"password\": \"password@123\",\r\n                \"sql\": [\r\n                        {\r\n                                \"callable\": true,\r\n                                \"statement\": \"EXEC [Report].[usp_dashboard_operation_posting_trend_15min]\"\r\n                        }\r\n                       ],\r\n                \"index\": \"sql_posting_ops\",\r\n                \"type\": \"sql_posting_trends\"\r\n                }\r\n        }'\r\n\r\nAnd it works great for pulling the SQL reports out of SQL.  \r\nThis is what the data looks like\r\n\r\n    {\r\n     \"_index\": \"sql_posting_ops\",\r\n      \"_type\": \"jt_sql_posting_trends\",\r\n      \"_id\": \"AUyAmhp4CW0VqTsqp8uI\",\r\n      \"_score\": 1,\r\n      \"_source\": {\r\n         \"posting_status\": \"pending_confirm\",\r\n         \"day_name\": \"Friday\",\r\n         \"day_timeinterval\": \"2015-04-03 00:45:00\",\r\n         \"posting_count\": 14,\r\n          \"average_posting_count\": 14,\r\n          \"average_posting_day_count\": 45\r\n     }\r\n}\r\n\r\nThe issue is that I would like to create a time-stamped field out of the returned data so that I can crate time-indexed searches / visualizations using Kibana.  This requires something akin to a logstash-event time-stamp or an event field logstash date-filtered time stamp.   I would want to use the \"day_timeinterval\" field to do this.  \r\n\r\nIs there any way of using this JDBC river in order to add a timestamp that Kibana would recognize as valid?   Any help or ideas would be greatly appreciated.\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/jprante/elasticsearch-jdbc/issues/comments/95302545","html_url":"https://github.com/jprante/elasticsearch-jdbc/issues/519#issuecomment-95302545","issue_url":"https://api.github.com/repos/jprante/elasticsearch-jdbc/issues/519","id":95302545,"user":{"login":"deusofnull","id":3485446,"avatar_url":"https://avatars.githubusercontent.com/u/3485446?v=3","gravatar_id":"","url":"https://api.github.com/users/deusofnull","html_url":"https://github.com/deusofnull","followers_url":"https://api.github.com/users/deusofnull/followers","following_url":"https://api.github.com/users/deusofnull/following{/other_user}","gists_url":"https://api.github.com/users/deusofnull/gists{/gist_id}","starred_url":"https://api.github.com/users/deusofnull/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/deusofnull/subscriptions","organizations_url":"https://api.github.com/users/deusofnull/orgs","repos_url":"https://api.github.com/users/deusofnull/repos","events_url":"https://api.github.com/users/deusofnull/events{/privacy}","received_events_url":"https://api.github.com/users/deusofnull/received_events","type":"User","site_admin":false},"created_at":"2015-04-22T18:57:13Z","updated_at":"2015-04-22T18:57:13Z","body":"Hey @jprante \r\n\r\nI have made some changes to my stored procedure, or rather the DBA guys did, and am now having a new issue.\r\n\r\nThe new timestamp format coming out of SQL looks like this: \r\n```2015-03-08 02:00:00 EST```\r\n\r\nAs per the Joda documentation, I assumed that the 'z' character would match this pattern:\r\n ```z: time zone:                   text :         Pacific Standard Time; PST````\r\n\r\nHowever, when I use a field mapping for my timestamp field like this:\r\n```          \"day_timeinterval\" : {\r\n            \"type\" : \"date\",\r\n            \"format\": \"yyyy-MM-dd HH:mm:ss z\"\r\n          }, ```\r\n\r\nI get a pretty nasty \"Illegal instant\" Exception:\r\n```\r\norg.elasticsearch.index.mapper.MapperParsingException: failed to parse [day_timeinterval]\r\n        at org.elasticsearch.index.mapper.core.AbstractFieldMapper.parse(AbstractFieldMapper.java:416)\r\n        at org.elasticsearch.index.mapper.object.ObjectMapper.serializeValue(ObjectMapper.java:709)\r\n        at org.elasticsearch.index.mapper.object.ObjectMapper.parse(ObjectMapper.java:500)\r\n        at org.elasticsearch.index.mapper.DocumentMapper.parse(DocumentMapper.java:542)\r\n        at org.elasticsearch.index.mapper.DocumentMapper.parse(DocumentMapper.java:491)\r\n        at org.elasticsearch.index.shard.service.InternalIndexShard.prepareCreate(InternalIndexShard.java:392)\r\n        at org.elasticsearch.action.bulk.TransportShardBulkAction.shardIndexOperation(TransportShardBulkAction.java:444)\r\n        at org.elasticsearch.action.bulk.TransportShardBulkAction.shardOperationOnPrimary(TransportShardBulkAction.java:150)\r\n        at org.elasticsearch.action.support.replication.TransportShardReplicationOperationAction$AsyncShardOperationAction.performOnPrimary(TransportShardReplicationOperationAction.java:512)\r\n        at org.elasticsearch.action.support.replication.TransportShardReplicationOperationAction$AsyncShardOperationAction$1.run(TransportShardReplicationOperationAction.java:419)\r\n        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)\r\n        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)\r\n        at java.lang.Thread.run(Thread.java:745)\r\nCaused by: org.elasticsearch.index.mapper.MapperParsingException: failed to parse date field [2015-03-08 02:15:00 EST], tried both date format [yyyy-MM-dd HH:mm:ss z], and timestamp number with locale []\r\n        at org.elasticsearch.index.mapper.core.DateFieldMapper.parseStringValue(DateFieldMapper.java:621)\r\n        at org.elasticsearch.index.mapper.core.DateFieldMapper.innerParseCreateField(DateFieldMapper.java:549)\r\n        at org.elasticsearch.index.mapper.core.NumberFieldMapper.parseCreateField(NumberFieldMapper.java:235)\r\n        at org.elasticsearch.index.mapper.core.AbstractFieldMapper.parse(AbstractFieldMapper.java:406)\r\n        ... 12 more\r\nCaused by: org.elasticsearch.common.joda.time.IllegalInstantException: Cannot parse \"2015-03-08 02:15:00 EST\": Illegal instant due to time zone offset transition (America/New_York)\r\n        at org.elasticsearch.common.joda.time.format.DateTimeParserBucket.computeMillis(DateTimeParserBucket.java:390)\r\n        at org.elasticsearch.common.joda.time.format.DateTimeFormatter.parseMillis(DateTimeFormatter.java:749)\r\n        at org.elasticsearch.index.mapper.core.DateFieldMapper.parseStringValue(DateFieldMapper.java:615)```\r\n\r\nDo you have any suggestions for how to handle this?  Would the ZZ / Z pattern solve this issue if the  stored procedure were changed?"}}