search

kestra-io / plugin-jdbc

https://kestra.io/plugins/
Apache License 2.0
10 stars 7 forks source link

JDBC query returns incorrect value for timestamp when timezone is non-UTC #371

Open paulgrainger85 opened 2 weeks ago

paulgrainger85 commented 2 weeks ago

Describe the issue

Issue reported by user using MYSQL query task.

  1. Start a MYSQL DB using Europe/Madrid as the timezone docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=password -p 3306:3306 -e TZ=Europe/Madrid -d mysql:latest mysql
  2. In Kestra configuration, set webserver and worker TZ to Europe/Madrid, e.g. 
    kestra:
image: registry.kestra.io/docker/kestra-ee:v0.15.17-full
pull_policy: always
# Note that this is meant for development only. Refer to the documentation for production deployments of Kestra which runs without a root user.
user: "root"
command: server standalone --worker-thread=128
volumes:
  - kestra-ee-data:/app/storage
  - /var/run/docker.sock:/var/run/docker.sock
  - /tmp/kestra-wd:/tmp/kestra-wd
env_file:
  - .env
environment:
  TZ: Europe/Madrid
...
  3. Execute the following flow 
    id: mysql_timezone
namespace: demo
tasks:
- id: query
type: io.kestra.plugin.jdbc.mysql.Query
url: jdbc:mysql://docker.for.mac.localhost:3306/
username: root
password: password
sql: "select CURRENT_TIMESTAMP()"
fetchOne: true

Notice that the timestamp returned is incorrect, and is consistently 2 hours behind current time in Madrid.

This appears to have been fixed in versions 0.17 onwards

Environment