time zone information of date field lost, how to transfer time zone

[zgjhust]

the log item looks like

{
    "created_at" => "2018-12-17T06:57:58.000Z"
    ......
}

but when inserted into mysql, it looks like

  # select created_at from t limit 1;
    2018-12-17 06:57:58

the time zone of mysql server is +08:00, it is supposed to be 2018-12-17 14:57:58

Environment

• Version of plugin used: 5.4.0
• Version of Logstash used: 5.6.4
• Database engine & version you're connecting to: mysql 5.7.10
• Have you checked you've met the Logstash requirements for Java versions?: openjdk 1.8.0_191 b12

[theangryangel]

Check what datatype and how you're casting your datetime string. MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME). So if you're using cast(? as timestamp) in your logstash-output-jdbc string you'll need to modify it to the equivalent that MySQL supports (presumably cast(? as datetime)).

[zgjhust]

resolved by this:

statement => ["insert into t1 (record_time) values ( convert_tz(?, '+00:00', '+08:00'))","record_time"]