Venkat, thanks for the answer! On Tue, Jun 23, 2015 at 2:22 AM, Venkata Sowrirajan < [email protected]> 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 <[email protected]> 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 > > >
