Hi Shuyi, I think there were some discussions in the mailing list [1,2] and JIRA tickets [3,4] that might be related. Since the table-blink planner doesn't produce such error, I think this problem is valid and should be fixed.
Thanks, Rong [1] http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/event-time-timezone-is-not-correct-tt26457.html [2] http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/TimeZone-shift-problem-in-Flink-SQL-td25666.html#a25739 [3] https://issues.apache.org/jira/browse/FLINK-8353 [4] https://issues.apache.org/jira/browse/FLINK-8169 On Mon, Jul 22, 2019 at 1:49 PM Shuyi Chen <suez1...@gmail.com> wrote: > Hi Lasse, > > Thanks for the reply. If your input is in epoch time, you are not getting > local time, instead, you are getting a wrong time that does not make sense. > For example, if the user input value is 0 (which means 00:00:00 UTC on 1 > January 1970), and your local timezone is UTC-8, converting 00:00:00 UTC on > 1 January 1970 to your local timezone should yield 16:00:00 Dec 31, 1969. > But actually, you will be getting 08:00:00 UTC on 1 January 1970 from > Table/SQL runtime, which 00:00:00 on 1 January 1970 in your local timezone > (UTC-8). Your input time just get shifted by 8 hours in output. > > Shuyi > > On Mon, Jul 22, 2019 at 12:49 PM Lasse Nedergaard < > lassenederga...@gmail.com> wrote: > >> Hi. >> >> I have encountered the same problem when you input epoch time to window >> table function and then use window.start and window.end the out doesn’t >> output in epoch but local time and I located the problem to the same >> internal function as you. >> >> Med venlig hilsen / Best regards >> Lasse Nedergaard >> >> >> Den 22. jul. 2019 kl. 20.46 skrev Shuyi Chen <suez1...@gmail.com>: >> >> Hi all, >> >> Currently, in the non-blink table/SQL runtime, Flink used >> SqlFunctions.internalToTimestamp(long v) from Calcite to convert event time >> (in long) to java.sql.Timestamp. However, as discussed in the recent >> Calcite mailing list (Jul. 19, 2019), SqlFunctions.internalToTimestamp() >> assumes the input timestamp value is in the current JVM’s default timezone >> (which is unusual), NOT milliseconds since epoch. And >> SqlFunctions.internalToTimestamp() is used to convert timestamp value in >> the current JVM’s default timezone to milliseconds since epoch, which >> java.sql.Timestamp constructor takes. Therefore, the results will not only >> be wrong, but change if the job runs in machines on different timezones as >> well. (The only exception is that all your production machines uses UTC >> timezone.) >> >> Here is an example, if the user input value is 0 (00:00:00 UTC on 1 >> January 1970), and the table/SQL runtime runs in a machine in PST (UTC-8), >> the output sql.Timestamp after SqlFunctions.internalToTimestamp() will >> become 28800000 millisec since epoch (08:00:00 UTC on 1 January 1970); And >> with the same input, if the table/SQL runtime runs again in a different >> machine in EST (UTC-5), the output sql.Timestamp after >> SqlFunctions.internalToTimestamp() will become 18000000 millisec since >> epoch (05:00:00 UTC on 1 January 1970). >> >> More details are captured in >> https://issues.apache.org/jira/browse/FLINK-13372. Please let me know >> your thoughts and correct me if I am wrong. Thanks a lot. >> >> Shuyi >> >>
