System timezone changes generated checksums for YAML change logs that doesn't specify timezone for TIMESTAMP columns

[muhammednursoy]

Search first

• I searched and no similar issues were found

Description

If a YAML change log contains an insert for a TIMESTAMP column but doesn't specify the timezone, Liquibase generates different checksums when system timezone changes.

Steps To Reproduce

1. Create a change log which contains an TIMESTAMP column insert but doesn't specify timezone
2. Run change log
3. Change system timezone to something other than current value
4. Run change log once more
5. Change log fails due to different checksums

Expected/Desired Behavior

System timezone have no effect on Liquibase checksums when timezone is not specified in YAML change logs

Liquibase Version

4.23.0

Database Vendor & Version

PostgresSQL 9.6.17

Liquibase Integration

spring boot

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

VM

Additional Context

under the hoods snakeyaml uses system timezone as default when timezone is missing.

Example change log

databaseChangeLog:
  - changeSet:
      id: 0.0.1
      author: johndoe
      changes:
      - createTable:
          tableName: example
          columns:
            - column:
                name: id
                type: VARCHAR(255)
                constraints:
                  primarykey: true
                  unique: true
                  nullable: false
            - column:
                name: created_at
                type: TIMESTAMP(3)
                constraints:
                  nullable: false

      - insert:
          tableName: example
          columns:
            - column:
                name: id
                value: example1
            - column:
                name: created_at
                value: 2018-03-09 08:41:31.000

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[tati-qalified]

Hi @muhammednursoy
I was able to confirm that there are different behaviours with timestamps with different changelog file formats.

Here's part of the output from running liquibase update-sql with the .yml code you've provided:

INSERT INTO
  PUBLIC.example (id, created_at)
VALUES
  ('example1', 'Fri Mar 09 05:41:31 UYT 2018');

This uses my timezone.

And here's the result of using the equivalent code with a .xml changelog format:

INSERT INTO
  PUBLIC.example (created_at)
VALUES
  ('2018-03-09 08:41:31.000');

This uses the timestamp value as provided, and it doesn't change the date format.

---

In this case I understand the need for both the yml behaviour and the xml behaviour. On the one hand, if the timezone isn't specified, it makes sense to assume that the user's timezone is desired. On the other hand, I agree that the value should be used as specified instead of changing it.

I'm not sure which of those is the expected behaviour for Liquibase, but it should definitely be consistent throughout all changelog file formats.

I'll discuss this with the development team and come back to you.

Thank you,
Tatiana

[tati-qalified]

I can now confirm that this is a bug in the yml parser. The correct behaviour is the one displayed for the xml files.

We'll look into this, thank you for reporting it!
Tatiana

cc @filipelautert