Presto: Casting timestamps with timezones to varchar changes the time

[DrXopher]

If I cast a timestamp with timezone to a string it shifts the time by an hour. If I cast a string to a timestamp with timezone it shifts the time in the opposite direction.

It seems to be related to daylight savings. Sao Paulo changed their daylight saving on 4th November. Trying dates after that works fine.

What's going on?

    CAST('2018-10-28 14:44:59.000 America/Sao_Paulo' AS TIMESTAMP WITH TIME ZONE),
    CAST(CAST('2018-10-28 14:44:59.000 America/Sao_Paulo' AS TIMESTAMP WITH TIME ZONE) AS VARCHAR)

[findepi]

presto> select timestamp '2018-10-28 14:44:59.000 America/Sao_Paulo';
                   _col0
-------------------------------------------
 2018-10-28 13:44:59.000 America/Sao_Paulo

@haozhun can this be a difference between Joda Time and java.time time zone databases?

[haozhun]

Root cause is #8233. But we can mitigate this by upgrading joda and make sure java version matches.

[haozhun]

• We must not upgrade to joda 2.10.1, which has tzdata 2018g. There is no java release with that version of tzdata at this time.
• If we upgrade to joda 2.10, tzdata version will be 2018e. Java has 2018e since 10.0.2, 8u181, 7u191, 6u201.
• Presto currently has joda 2.9.9 (1 version prior to 2.10), tzdata version is 2017b. Java has 2017b since 8u141, 7u151, 6u161 (but updated to 2017c since 9.0.4, 8u161, 7u171, 6u181). In other words, 8u151/152 is the only Java version that works without tz glitches with current Presto. (Presto will refuse to start if you use a java version below 8u151 for separate reasons.)

To mitigate the issue right away, the admin can rollback java version to 8u151/152. Presto is going to upgrade joda version to 2.10 in the next release.

[haozhun]

According to tzdata, America/Sao_Paulo zone went through rule changes since 2017b.

# From Steffen Thorsen (2017-12-18):
# According to many media sources, next year's DST start in Brazil will move to
# the first Sunday of November, and it will stay like that for the years after.
# ... https://www.timeanddate.com/news/time/brazil-delays-dst-2018.html
# From Steffen Thorsen (2017-12-20):
# http://www.planalto.gov.br/ccivil_03/_ato2015-2018/2017/decreto/D9242.htm
#
# From Fábio Gomes (2018-10-04):
# The Brazilian president just announced a new change on this year DST.
# It was scheduled to start on November 4th and it was changed to November 18th.
# From Rodrigo Brüning Wessler (2018-10-15):
# The Brazilian government just announced that the change in DST was
# canceled....  Maybe the president Michel Temer also woke up one hour
# earlier today. :)

[electrum]

Is there an easy way to detect the tzdata versions for Joda and Java? If so, we could fail on startup if they do not match.

[haozhun]

Let's move discussion about solving the root cause to #8233

[elonazoulay]

Reopening this issue as we removed the joda-to-java-time-bridge registration until the cache thrashing issue is resolved.

[stale[bot]]

This issue has been automatically marked as stale because it has not had any activity in the last 2 years. If you feel that this issue is important, just comment and the stale tag will be removed; otherwise it will be closed in 7 days. This is an attempt to ensure that our open issues remain valuable and relevant so that we can keep track of what needs to be done and prioritize the right things.