How Impala knows to convert strings to timestamps

[Boris Tyukin <bo...@boristyukin.com>]

Hello and Happy Friday!

We decided to store date/timestamps as a string due to all the issues we
had with time zones and the way we want them to work consistently across
Impala, Hive, Kudu and Spark.

In a query like below, I thought I would have to convert strings with
timestamps to timestamp explicitly but looks like Impala does implicit
conversion and our users won't need to worry about doing it. Is my
assumption correct?

SELECT something
FROM table1
WHERE
  ingest_dt_tm > now() - interval 2 hours

Query above is a simple one and I understand how Impala would "know" to do
implicit convertion.

But I was really surprised that query below also worked properly because in
that case I compare a string column with another string:

SELECT something
FROM table1
WHERE
  ingest_dt_tm > '2019-01-18 10:42:25.795'

I thought we would need to do CAST('2019-01-18 10:42:25.795' as timestamp)
or something like that.

Also from a performance perspective, would the statement below be equal:

SELECT something
FROM table1
WHERE
  ingest_dt_tm > '2019-01-18 10:42:25.795'

SELECT something
FROM table1
WHERE
  cast(ingest_dt_tm as timestamp)> '2019-01-18 10:42:25.795'

SELECT something
FROM table1
WHERE
  ingest_dt_tm > cast('2019-01-18 10:42:25.795' as timestamp)

Re: How Impala knows to convert strings to timestamps

[Boris Tyukin <bo...@boristyukin.com>]

Hi Jim,

I think it is the last thing you mentioned that make that magic happen. I
did not realize that standard lexicographical string compare will give the
same result as comparing timestamps but just remembered that this is how I
catalog my personal pictures to keep them in order (year/date/time)

so if timestamp is stored in a string column and I compare to another
string in the same format, no conversion occurs but they are still being
compared accurately. I will do some more testing, thanks for your help!

On Fri, Jan 18, 2019 at 11:54 AM Jim Apple <jb...@cloudera.com> wrote:

> Try looking at the "predicates: ' line in the scan on your EXPLAIN output.
>
> Check out "conversions" in
> http://impala.apache.org/docs/build/html/topics/impala_timestamp.html
>
> One final note: if ingest_dt_tm is in the same format as '2019-01-18
> 10:42:25.795', then standard lexicographical string compare will give the
> same result as comparing timestamps.
>
> On Fri, Jan 18, 2019 at 8:04 AM Boris Tyukin <bo...@boristyukin.com>
> wrote:
>
>> Hello and Happy Friday!
>>
>> We decided to store date/timestamps as a string due to all the issues we
>> had with time zones and the way we want them to work consistently across
>> Impala, Hive, Kudu and Spark.
>>
>> In a query like below, I thought I would have to convert strings with
>> timestamps to timestamp explicitly but looks like Impala does implicit
>> conversion and our users won't need to worry about doing it. Is my
>> assumption correct?
>>
>> SELECT something
>> FROM table1
>> WHERE
>>   ingest_dt_tm > now() - interval 2 hours
>>
>> Query above is a simple one and I understand how Impala would "know" to
>> do implicit convertion.
>>
>> But I was really surprised that query below also worked properly because
>> in that case I compare a string column with another string:
>>
>> SELECT something
>> FROM table1
>> WHERE
>>   ingest_dt_tm > '2019-01-18 10:42:25.795'
>>
>> I thought we would need to do CAST('2019-01-18 10:42:25.795' as
>> timestamp) or something like that.
>>
>> Also from a performance perspective, would the statement below be equal:
>>
>> SELECT something
>> FROM table1
>> WHERE
>>   ingest_dt_tm > '2019-01-18 10:42:25.795'
>>
>> SELECT something
>> FROM table1
>> WHERE
>>   cast(ingest_dt_tm as timestamp)> '2019-01-18 10:42:25.795'
>>
>> SELECT something
>> FROM table1
>> WHERE
>>   ingest_dt_tm > cast('2019-01-18 10:42:25.795' as timestamp)
>>
>>

Re: How Impala knows to convert strings to timestamps

[Jim Apple <jb...@cloudera.com>]

Try looking at the "predicates: ' line in the scan on your EXPLAIN output.

Check out "conversions" in
http://impala.apache.org/docs/build/html/topics/impala_timestamp.html

One final note: if ingest_dt_tm is in the same format as '2019-01-18
10:42:25.795', then standard lexicographical string compare will give the
same result as comparing timestamps.

On Fri, Jan 18, 2019 at 8:04 AM Boris Tyukin <bo...@boristyukin.com> wrote:

> Hello and Happy Friday!
>
> We decided to store date/timestamps as a string due to all the issues we
> had with time zones and the way we want them to work consistently across
> Impala, Hive, Kudu and Spark.
>
> In a query like below, I thought I would have to convert strings with
> timestamps to timestamp explicitly but looks like Impala does implicit
> conversion and our users won't need to worry about doing it. Is my
> assumption correct?
>
> SELECT something
> FROM table1
> WHERE
>   ingest_dt_tm > now() - interval 2 hours
>
> Query above is a simple one and I understand how Impala would "know" to do
> implicit convertion.
>
> But I was really surprised that query below also worked properly because
> in that case I compare a string column with another string:
>
> SELECT something
> FROM table1
> WHERE
>   ingest_dt_tm > '2019-01-18 10:42:25.795'
>
> I thought we would need to do CAST('2019-01-18 10:42:25.795' as timestamp)
> or something like that.
>
> Also from a performance perspective, would the statement below be equal:
>
> SELECT something
> FROM table1
> WHERE
>   ingest_dt_tm > '2019-01-18 10:42:25.795'
>
> SELECT something
> FROM table1
> WHERE
>   cast(ingest_dt_tm as timestamp)> '2019-01-18 10:42:25.795'
>
> SELECT something
> FROM table1
> WHERE
>   ingest_dt_tm > cast('2019-01-18 10:42:25.795' as timestamp)
>
>