Importing MySql rows with existing timestamp when not stored as UTC

Elastic Stack Logstash
1

I've been searching around and can't seem to find an answer to this.

I have a MySql database which I am importing via logstash, which has an existing timestamp field called "receivetime". This is the field I want to use as the timestamp. It contains no timezone data, or if it does it's incorrectly set to UTC in the MySql Database.

When I import these to Elasticsearch using Logstash it works, and when I create an index pattern in Kibana I am able to select the field "receivetime" as the field for the time filter. The problem is it's reading the date as UTC, and adjusting it to EST which subracts 5 hours from it. It's already in an EST format, so this makes all entries 5 hours behind the actual time.

Basically, I need some way to add 5 hours to this field when importing so it stores it in UTC instead of EST.

I'm thinking about adjusting my SQL query to do this, but figured since logstash is designed to transform data there could be a way to do this.

Any suggestions would be greatly appreciated. Thanks!

2

Well I figured out a workaround by changing the date as a select it in the sql statement:

SELECT DATE_ADD(receivetime, INTERVAL 5 HOUR) as receivetime

Really the Sql data should have been stored as UTC in the first place but that's out of my control so this will work as long as they consistently continue to insert records as EST.

3

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