From user-return-28725-archive-asf-public=cust-asf.ponee.io@flink.apache.org Mon Jul 22 18:46:55 2019 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [207.244.88.153]) by mx-eu-01.ponee.io (Postfix) with SMTP id DD23E18064F for ; Mon, 22 Jul 2019 20:46:54 +0200 (CEST) Received: (qmail 45529 invoked by uid 500); 22 Jul 2019 18:46:42 -0000 Mailing-List: contact user-help@flink.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Delivered-To: mailing list user@flink.apache.org Received: (qmail 45200 invoked by uid 99); 22 Jul 2019 18:46:41 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 22 Jul 2019 18:46:41 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id DE0FCC1782; Mon, 22 Jul 2019 18:46:40 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.05 X-Spam-Level: ** X-Spam-Status: No, score=2.05 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, DKIM_VALID_EF=-0.1, FREEMAIL_ENVFROM_END_DIGIT=0.25, HTML_MESSAGE=2, RCVD_IN_DNSWL_NONE=-0.0001, SPF_HELO_NONE=0.001, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd1-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-ec2-va.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id TCvov1RhJJQT; Mon, 22 Jul 2019 18:46:39 +0000 (UTC) Received-SPF: Pass (mailfrom) identity=mailfrom; client-ip=209.85.128.50; helo=mail-wm1-f50.google.com; envelope-from=suez1224@gmail.com; receiver= Received: from mail-wm1-f50.google.com (mail-wm1-f50.google.com [209.85.128.50]) by mx1-ec2-va.apache.org (ASF Mail Server at mx1-ec2-va.apache.org) with ESMTPS id 3DBC7BC7FC; Mon, 22 Jul 2019 18:46:39 +0000 (UTC) Received: by mail-wm1-f50.google.com with SMTP id u25so25946412wmc.4; Mon, 22 Jul 2019 11:46:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:from:date:message-id:subject:to; bh=U5LdDudkHjCkXx8fXbLJe5Ci6825lEZVSg+7V54kCDo=; b=XntO0Cl3jvuJf9rjZze3PNQgF3oTTz2AphkZC+qT2eT1SoNM8b2ozA9v8DO1o6tEV5 q30O7tF2dTLGeWpMeobXFqMzYL1SQPyC+Q9w1nQn+gqzligd/lGrkAP8iB1xAEuAQ+2g kseSoCCc9Ji9CH9XKTw/SdgITVJ8ZezE77Jv8aAhh+587Ttr/odf+KuYW/9JFk/Xiix3 aRj7xv8gJCp498kJbeBginxkszBvWRf1Vvbhb6NjwSHaHCgUm8GtacPo/jaTGtmXI1V1 rwLTdAY3Por/KS1r3g5S5FD20XCn/CVh30yKihZGbWyCFqyQzseAAJta91k0JG5sb6gD jkGA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=U5LdDudkHjCkXx8fXbLJe5Ci6825lEZVSg+7V54kCDo=; b=jcBO8mC3qFgztPq9iqx1wEBOWXUfITg9jq/wqG5QlvlYCyFsxZeiuQ2OAUqcNpQKI0 9NBPRY9cwmfGj7IUdtnlG9andEfbHMJYtUcbUVzId4Xs0mOQyAi3RrBr6bybExxWHZPa 6aHFAWkr7jYRRRBVh+qy2MyqXaGutzALVvpkyH74CtKZlJxQfD0fjy5UlJj4WRAtLYfg 278HrzCioZU2N+/vp6ay00e36Ahzesw4trsX2/EoizUi/7Sy+gU5Y7bsnpAfn2m0W0Al BTkRbBLWNZaXIWWqumF26LZ0Q9WETCEs/dfjnYYpdzngtmiWLGFkM8Wh4xqeuGFbCU8J KeWQ== X-Gm-Message-State: APjAAAWp5KPFEDVk2N7rGwd1mxvUug6E9MK6ukUarRlvnwenv5Dnnk1G MucnU3XXcPeOmG9We0WrJrcDimwRQSKZboJS0742ek+5 X-Google-Smtp-Source: APXvYqyKfSx4Jtfa2x1KYfLP7Nq0VM9vXFZ20pRrUcc8ysO+T1uVMmYMYsE65DgRVbUb7JTwtE9Gi95lFews8FIbJ+E= X-Received: by 2002:a7b:cc04:: with SMTP id f4mr66543963wmh.125.1563821197809; Mon, 22 Jul 2019 11:46:37 -0700 (PDT) MIME-Version: 1.0 From: Shuyi Chen Date: Mon, 22 Jul 2019 11:46:26 -0700 Message-ID: Subject: Timestamp(timezone) conversion bug in non blink Table/SQL runtime To: dev , user Content-Type: multipart/alternative; boundary="000000000000fcdc56058e497e4c" --000000000000fcdc56058e497e4c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=99s default t= imezone (which is unusual), NOT milliseconds since epoch. And SqlFunctions.internalToTimestamp() is used to convert timestamp value in the current JVM=E2=80=99s default timezone to milliseconds since epoch, whi= ch 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 --000000000000fcdc56058e497e4c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0all,=C2=A0

Currently, in the non-blink= table/SQL runtime, Flink used SqlFunctions.internalToTimestamp(long v) fro= m Calcite to convert event time (in long) to java.sql.Timestamp. However, a= s discussed in the recent Calcite mailing list (Jul. 19, 2019), SqlFunction= s.internalToTimestamp() assumes the input timestamp value is in the current= JVM=E2=80=99s default timezone (which is unusual), NOT milliseconds since = epoch. And SqlFunctions.internalToTimestamp() is used to convert timestamp = value in the current JVM=E2=80=99s default timezone to milliseconds since e= poch, which java.sql.Timestamp constructor takes. Therefore, the results wi= ll not only be wrong, but change if the job runs in machines on different t= imezones 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 Sq= lFunctions.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.T= imestamp after SqlFunctions.internalToTimestamp() will become 18000000 mill= isec since epoch (05:00:00 UTC on 1 January 1970).=C2=A0

=
More details are captured in=C2=A0https:= //issues.apache.org/jira/browse/FLINK-13372. Please let me know your th= oughts and correct me if I am wrong. Thanks a lot.

Shuyi
--000000000000fcdc56058e497e4c--