You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by George Lu <lu...@gmail.com> on 2015/06/19 02:22:13 UTC
Drill timestamp conversion
I want to convert the unix time in HBase to timestamp.
I tried to_date and to_timestamp.
The original unix time after convert_to is like "1434672665166", I have to
use below to extract the correct timestamp.
substr(to_timestamp(CAST(substr(CONVERT_FROM(T.log.ts,'UTF8'),1,10) AS
INT)),1,19)
Any better way?
And does it consider the locale when convert time?
When I use to_date, the 00:00:00 time will normally convert to previous
day, is that because the locale?
Thanks!
George
Re: Drill timestamp conversion
Posted by George Lu <lu...@gmail.com>.
Venkat, thanks for the answer!
On Tue, Jun 23, 2015 at 2:22 AM, Venkata Sowrirajan <
vsowrirajan@maprtech.com> wrote:
> 1. I don't think there is any single function which can take a hbase
> byte_array (which is unixtimestamp in ms) and convert that to timestamp. I
> think somehow you need to do all those conversions. You can avoid substr()
> and divide that int by 1000, since unix_ts is in milliseconds whereas
> to_timestamp takes in unix timestamp(in seconds). Below is an example of
> that.
>
> select TO_TIMESTAMP(CAST(CONVERT_FROM(T.log.ts,'UTF8') as BIGINT)/1000)
> from `timestamp-example` c limit 5;
>
> 2. I think to_date is converting the unix timestamp to GMT timezone but not
> sure on this. For eg:
>
> 0: jdbc:drill:> select TO_DATE(1434672665166) from
> `/user/syscheck/Pontis-2015-05-28/clientguts` c limit 5;
> +-------------+
> | EXPR$0 |
> +-------------+
> | 2015-06-19 |
> | 2015-06-19 |
> | 2015-06-19 |
> | 2015-06-19 |
> | 2015-06-19 |
> +-------------+
> 5 rows selected (0.443 seconds)
>
> When I try to convert the same unix timestamp to date it gave 2015-06-19
> instead of 2015-06-18
>
> Regards
>
> Venkat
> MapR Technologies, Inc.
>
> On Thu, Jun 18, 2015 at 5:22 PM, George Lu <lu...@gmail.com> wrote:
>
> > I want to convert the unix time in HBase to timestamp.
> >
> > I tried to_date and to_timestamp.
> >
> > The original unix time after convert_to is like "1434672665166", I have
> to
> > use below to extract the correct timestamp.
> >
> > substr(to_timestamp(CAST(substr(CONVERT_FROM(T.log.ts,'UTF8'),1,10) AS
> > INT)),1,19)
> >
> > Any better way?
> > And does it consider the locale when convert time?
> > When I use to_date, the 00:00:00 time will normally convert to previous
> > day, is that because the locale?
> >
> > Thanks!
> >
> > George
> >
>
Re: Drill timestamp conversion
Posted by Venkata Sowrirajan <vs...@maprtech.com>.
1. I don't think there is any single function which can take a hbase
byte_array (which is unixtimestamp in ms) and convert that to timestamp. I
think somehow you need to do all those conversions. You can avoid substr()
and divide that int by 1000, since unix_ts is in milliseconds whereas
to_timestamp takes in unix timestamp(in seconds). Below is an example of
that.
select TO_TIMESTAMP(CAST(CONVERT_FROM(T.log.ts,'UTF8') as BIGINT)/1000)
from `timestamp-example` c limit 5;
2. I think to_date is converting the unix timestamp to GMT timezone but not
sure on this. For eg:
0: jdbc:drill:> select TO_DATE(1434672665166) from
`/user/syscheck/Pontis-2015-05-28/clientguts` c limit 5;
+-------------+
| EXPR$0 |
+-------------+
| 2015-06-19 |
| 2015-06-19 |
| 2015-06-19 |
| 2015-06-19 |
| 2015-06-19 |
+-------------+
5 rows selected (0.443 seconds)
When I try to convert the same unix timestamp to date it gave 2015-06-19
instead of 2015-06-18
Regards
Venkat
MapR Technologies, Inc.
On Thu, Jun 18, 2015 at 5:22 PM, George Lu <lu...@gmail.com> wrote:
> I want to convert the unix time in HBase to timestamp.
>
> I tried to_date and to_timestamp.
>
> The original unix time after convert_to is like "1434672665166", I have to
> use below to extract the correct timestamp.
>
> substr(to_timestamp(CAST(substr(CONVERT_FROM(T.log.ts,'UTF8'),1,10) AS
> INT)),1,19)
>
> Any better way?
> And does it consider the locale when convert time?
> When I use to_date, the 00:00:00 time will normally convert to previous
> day, is that because the locale?
>
> Thanks!
>
> George
>