You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by jia yichao <yc...@qq.com> on 2018/12/28 03:53:15 UTC

Calcite Time Conversion doubt

Hi community


Recently I have encountered a problem with time conversion.  I want to know why the following methods need to add or subtract time zone offsets. Is this designed to meet some specifications?  These methods are located in SqlFunctions.java. 

 /** Converts the Java type used for UDF parameters of SQL TIMESTAMP type
   * ({@link java.sql.Timestamp}) to internal representation (long).
   *
   * <p>Converse of {@link #internalToTimestamp(long)}. */
  public static long toLong(Timestamp v) {
    return toLong(v, LOCAL_TZ);
  }

// mainly intended for java.sql.Timestamp but works for other dates also
  public static long toLong(java.util.Date v, TimeZone timeZone) {
    final long time = v.getTime();
    return time + timeZone.getOffset(time);
  }

/** Converts the internal representation of a SQL TIMESTAMP (long) to the Java
   * type used for UDF parameters ({@link java.sql.Timestamp}). */
  public static java.sql.Timestamp internalToTimestamp(long v) {
    return new java.sql.Timestamp(v - LOCAL_TZ.getOffset(v));
  }

thanks
Jiayichao

Re: Calcite Time Conversion doubt

Posted by jia yichao <yc...@qq.com>.
hi Julian, 

Thank you for your replay . It’s very helpful to me. 
I want to learn more about the conversion relationship between SQL's time type and Java's time type and SQL’s time type with timezone . Can you give me some suggestions or links ?

Thanks 
Jiayichao



> 在 2018年12月29日，上午8:14，Julian Hyde <jh...@apache.org> 写道：
> 
> The root of the problem is that SQL’s TIMESTAMP type has no time zone (not even UTC) whereas Java’s Timestamp represents an instant in time, internally represented as an offset from the UTC epoch. Therefore translation between java.sql.Timestamp and SQL TIMESTAMP always needs a time zone.
> 
>> On Dec 27, 2018, at 7:53 PM, jia yichao <yc...@qq.com> wrote:
>> 
>> Hi community
>> 
>> 
>> Recently I have encountered a problem with time conversion.  I want to know why the following methods need to add or subtract time zone offsets. Is this designed to meet some specifications?  These methods are located in SqlFunctions.java. 
>> 
>> /** Converts the Java type used for UDF parameters of SQL TIMESTAMP type
>>  * ({@link java.sql.Timestamp}) to internal representation (long).
>>  *
>>  * <p>Converse of {@link #internalToTimestamp(long)}. */
>> public static long toLong(Timestamp v) {
>>   return toLong(v, LOCAL_TZ);
>> }
>> 
>> // mainly intended for java.sql.Timestamp but works for other dates also
>> public static long toLong(java.util.Date v, TimeZone timeZone) {
>>   final long time = v.getTime();
>>   return time + timeZone.getOffset(time);
>> }
>> 
>> /** Converts the internal representation of a SQL TIMESTAMP (long) to the Java
>>  * type used for UDF parameters ({@link java.sql.Timestamp}). */
>> public static java.sql.Timestamp internalToTimestamp(long v) {
>>   return new java.sql.Timestamp(v - LOCAL_TZ.getOffset(v));
>> }
>> 
>> thanks
>> Jiayichao
> 
>

Re: Calcite Time Conversion doubt

Posted by Julian Hyde <jh...@apache.org>.
The root of the problem is that SQL’s TIMESTAMP type has no time zone (not even UTC) whereas Java’s Timestamp represents an instant in time, internally represented as an offset from the UTC epoch. Therefore translation between java.sql.Timestamp and SQL TIMESTAMP always needs a time zone.

> On Dec 27, 2018, at 7:53 PM, jia yichao <yc...@qq.com> wrote:
> 
> Hi community
> 
> 
> Recently I have encountered a problem with time conversion.  I want to know why the following methods need to add or subtract time zone offsets. Is this designed to meet some specifications?  These methods are located in SqlFunctions.java. 
> 
> /** Converts the Java type used for UDF parameters of SQL TIMESTAMP type
>   * ({@link java.sql.Timestamp}) to internal representation (long).
>   *
>   * <p>Converse of {@link #internalToTimestamp(long)}. */
>  public static long toLong(Timestamp v) {
>    return toLong(v, LOCAL_TZ);
>  }
> 
> // mainly intended for java.sql.Timestamp but works for other dates also
>  public static long toLong(java.util.Date v, TimeZone timeZone) {
>    final long time = v.getTime();
>    return time + timeZone.getOffset(time);
>  }
> 
> /** Converts the internal representation of a SQL TIMESTAMP (long) to the Java
>   * type used for UDF parameters ({@link java.sql.Timestamp}). */
>  public static java.sql.Timestamp internalToTimestamp(long v) {
>    return new java.sql.Timestamp(v - LOCAL_TZ.getOffset(v));
>  }
> 
> thanks
> Jiayichao