Change Timezone for a particular field "calldate"

[rmartinez.rv (Ricardo)]

Hi.
I'm using logstash jdbc to pull data from a mysql database. So far so good. This is part of the configuration file:

input {
        jdbc {
                jdbc_driver_library => "/usr/share/java/mysql-connector-java.jar"
                jdbc_driver_class => "com.mysql.jdbc.Driver"
                jdbc_connection_string => "jdbc:mysql://xx.xx.xx.xx:3306/asterisk"
                jdbc_user => "user"
                jdbc_password => "password"
                schedule => "* * * * *"
                statement => "SELECT * FROM cdr where cdr_id > :sql_last_value"
                use_column_value => true
                tracking_column => cdr_id
        }
}

But i'm having some problems trying to parse a date field called "calldate" and change his Timezone.
The calldate field looks something like this in the mysql database:

MariaDB [asterisk]> select calldate,cdr_id from cdr order by cdr_id desc limit 1;
+---------------------+--------+
| calldate            | cdr_id |
+---------------------+--------+
| 2021-07-23 14:17:38 |   1852 |
+---------------------+--------+
1 row in set (0.001 sec)

MariaDB [asterisk]>

It seems to be in UTC because the local time when i made the call was 10:17:38 AM (I'm based in Chile which is UTC-4)
So, the first goal is catch the calldate field to parse the information and the change the timezone.
I have tried:

filter {
        date {
            match => [ "calldate" , "yyyy-MM-dd HH:mm:ss" ]
            match => [ "calldate" , "yyyy-MM-dd'T'HH:mm:ss.SSS" ]
            match => [ "calldate", "ISO8601" ]
            match => [ "calldate", "MMM dd, yyyy @ HH:mm:ss.SSS" ]
            timezone => "America/Santiago"
            target => "new_calldate"
        }
}

But when i run the service i always get this this log for logstash:

Jul 23 10:19:01 reverse logstash: "tags" => [
Jul 23 10:19:01 reverse logstash: [0] "_dateparsefailure"
Jul 23 10:19:01 reverse logstash: ]
Jul 23 10:19:01 reverse logstash: }

And the calldate field still has the original timezone in elasticsearch.
What i'm doing wrong in order to parse correctly the calldate field?. Is that the correct way to change the timezone for the new_date?

Any help would be much appreciated!

Regards

[Badger]

If a field has a date type in the database then the jdbc filter will change it to a LogStash::Timestamp object, and a date filter cannot parse that. The jdbc_default_timezone or plugin_timezone options on the input may help. It is also possible to specify the timezone in the connection string. If all else fails then

mutate { convert => { "calldate" => string } }

before the date filter.

[rmartinez.rv (Ricardo)]

Hi @Badger
Thanks again for your help. Yes... finally i can make it work using just the jdbc_default_timezone, like this:
jdbc_default_timezone => "UTC"

So, with this change the field calldate is using my local "TimeZone".

At least for me is a mistery how it works, according to the docs the jdbc_default_timezone seems to SET the TimeZone for "all" the DATE fields in the SQL request... so .. calldate is one of them and with that field set to UTC, i'm telling it that calldate comes in UTC, so i guess kibana or elasticsearch then changes the date to the local timezone (i'm guessing the Timezone set in the EKS server?).

So.. if the field calldate is something like:
2021-07-23 16:03:29

Then.. using jdbc_default_timezone as UTC calldate keeps the same value : 2021-07-23 16:03:29
But then is shown in the EKS server as : 2021-07-23 12:03:29 because my server is using UTC-4

Could this be correct?
Thanks anyway!

[Badger]

@rmartinez.rv

so i guess kibana or elasticsearch then changes the date to the local timezone

logstash and elasticsearch always work with dates as UTC. By default kibana will shift the date to the browser's timezone.