support from unix timestamp to string date

[ambition119]

Calcite support sql function UNIX_TIMESTAMP and FROM_UNIXTIME, BuiltInMethod.java info like :

UNIX_TIMESTAMP(DateTimeUtils.class, "unixTimestamp")

FROM_UNIXTIME(DateTimeUtils.class, "fromUnixTimestamp", long.class, TimeZone.class)

[julianhyde]

There are uses of default locale here. We can't have that. This code needs to behave identically in all locales.

In one or two places the comment says "unsigned int" when the result is clearly a signed long.

Methods that have a timezone argument must state very clearly, with examples, how the timezone is used. Are you converting to that timezone or from that timezone? So many times I expect a method to add 8 hours (to pacific) and it ends up subtracting 8 hours.

[ambition119]

There are uses of default locale here. We can't have that. This code needs to behave identically in all locales.

In one or two places the comment says "unsigned int" when the result is clearly a signed long.

Methods that have a timezone argument must state very clearly, with examples, how the timezone is used. Are you converting to that timezone or from that timezone? So many times I expect a method to add 8 hours (to pacific) and it ends up subtracting 8 hours.

Thank you for your suggestion, I fix the problem caused by timezone, How about changing to the following? convert user input TimeZone.

/**
   * Convert unix timestamp (seconds since '1970-01-01 00:00:00' UTC) to use's TimeZone datetime string
   * in the given format.
   */
  public static String fromUnixTimestamp(long unixTimestamp, String format, TimeZone tz) {
    try {
      SimpleDateFormat formatter =
          new SimpleDateFormat(format, Locale.getDefault(Locale.Category.FORMAT));
      formatter.setTimeZone(tz);
      Date date = new Date(unixTimestamp * 1000);
      return formatter.format(date);
    } catch (Exception e) {
      return null;
    }
  }

test like :

assertThat(fromUnixTimestamp(-28800000,UTC_ZONE), is("1969-02-01 16:00:00"));
assertThat(fromUnixTimestamp(1557134198, UTC_ZONE), is("2019-05-06 09:16:38"));
assertThat(fromUnixTimestamp(1557134198, TimeZone.getTimeZone("GMT+2")), is("2019-05-06 11:16:38"));
assertThat(fromUnixTimestamp(1557134198, TimeZone.getTimeZone("GMT+8")), is("2019-05-06 17:16:38"));

if convert to UTC TimeZone, also modify code like :

/**
   * Convert unix timestamp (seconds since '1970-01-01 00:00:00' UTC) to UTC datetime string
   * in the given format and TimeZone.
   */
  public static String fromUnixTimestamp(long unixTimestamp, String format, TimeZone tz) {
    try {
      SimpleDateFormat formatter =
          new SimpleDateFormat(format, Locale.getDefault(Locale.Category.FORMAT));
      formatter.setTimeZone(tz);
      Date date = new Date(unixTimestamp * 1000);
      Long targetTime = date.getTime() - tz.getRawOffset() + UTC_ZONE.getRawOffset();
      return formatter.format(new Date(targetTime));
    } catch (Exception e) {
      return null;
    }
  }

test like：

assertThat(fromUnixTimestamp(1557134198, UTC_ZONE), is("2019-05-06 09:16:38"));
assertThat(fromUnixTimestamp(1557134198, TimeZone.getTimeZone("GMT+2")), is("2019-05-06 09:16:38"));
assertThat(fromUnixTimestamp(1557134198, TimeZone.getTimeZone("GMT+8")), is("2019-05-06 09:16:38"));

[julianhyde]

In SQL, timestamps are zoneless, whereas in Unix they are relative to the UTC epoch.

Existing methods that have 'unix' in the name and have long arguments and/or results follow the SQL semantics. (I apologize that the names are misleading. I wanted to capture the fact that it is milliseconds since 1970, but not that it was relative to 1970 UTC.)

Your method assumes that the long value is relative to UTC. That is opposite to how Avatica does things (although it is the natural way for Java folks to do things), and will confuse people.

[ambition119]

In SQL, timestamps are zoneless, whereas in Unix they are relative to the UTC epoch.

Existing methods that have 'unix' in the name and have long arguments and/or results follow the SQL semantics. (I apologize that the names are misleading. I wanted to capture the fact that it is milliseconds since 1970, but not that it was relative to 1970 UTC.)

Your method assumes that the long value is relative to UTC. That is opposite to how Avatica does things (although it is the natural way for Java folks to do things), and will confuse people.

thanks, Do you have better suggestions for implementation?

[ambition119]

@julianhyde Currently I am a developer of the hadoop ecosystem, because we are most familiar with hive SQL, and there are already many Hive sql production tasks. Now we want to use Hive sql as the standard (so existing SQL tasks can be run without changes), running in Spark, Flink or Drill.       Because calcite supports ANSI sql, many of them do not support well, such as create funcation, load date sql statement (I PR code not merge), and sql function like from_timestamp() (hive UDFFromUnixTime).      At present, I have done a lot of work in the middle of the interception layer. I saw that Calcite has discussed the support of dialects like Hive. Can the submitted code be used?

[julianhyde]

I'm open to including these changes in principle.

However, misunderstanding the difference between moments (e.g. millis since 1970 UTC) and zoneless timestamps (e.g. millis since zoneless 1970) is a major source of bugs. Especially as values flow between Java and SQL and back again. Avatica has to be 100% clear and consistent.

Your PR, as it stands, is confused on this issue.

[ambition119]

I'm open to including these changes in principle.

However, misunderstanding the difference between moments (e.g. millis since 1970 UTC) and zoneless timestamps (e.g. millis since zoneless 1970) is a major source of bugs. Especially as values flow between Java and SQL and back again. Avatica has to be 100% clear and consistent.

Your PR, as it stands, is confused on this issue.

My code is only available with a specific scope, thank you for your discussion and guidance.