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 >
