时间戳

首页 > 解决方案 > 微秒到时间戳 impala

sql - 微秒到时间戳 impala

问题描述

我正在尝试将微秒转换为 Impala 中的时间戳。

我尝试了以下但需要格式“ YYYY-MM-DD HH:MM:SS”：

select TIME_JIFFY,
    (cast(TIME_JIFFY as bigint) - 4*60*60*65536) as jiffy_in_est,
    (floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) as no_of_days,
    (floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) - ((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) * 24 * 60 * 60 * 65536)) / 65536 / 60 / 60)) as jiffy_hours,
    (floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) - (((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) * 24 * 60 * 60 * 65536) + ((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) - ((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) * 24 * 60 * 60 * 65536)) / 65536 / 60 / 60)) * 60 * 60 * 65536))) / 65536 / 60)) as jiffy_minutes,
    (floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) - (((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) * 24 * 60 * 60 * 65536) + ((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) - ((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) * 24 * 60 * 60 * 65536)) / 65536 / 60 / 60)) * 60 * 60 * 65536) + ((Floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) - (((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) * 24 * 60 * 60 * 65536) + ((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) - ((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) * 24 * 60 * 60 * 65536)) / 65536 / 60 / 60)) * 60 * 60 * 65536))) / 65536 / 60)) * 60 * 65536))) / 65536)) as jiffy_seconds,
    (to_date(days_add(to_date(to_utc_timestamp('1980-01-01','EST5EDT')),(floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24)))))) as emni
From gg.table_name

我得到如下内容： 在此处输入图像描述

标签： sqlhiveimpala

解决方案

您需要将微秒转换为 Unix 时间（秒，bigint），然后您可以使用内置函数from_unixtime获取格式“YYYY-MM-DD HH:MM:SS”：

select from_unixtime(CAST(TIME_JIFFY / 1000000 AS BIGINT),"yyyy-MM-dd HH:mm:ss");

+----------------------------------------------------------------------------------+
| from_unixtime(cast(TIME_JIFFY / 1000000 as bigint), 'yyyy-mm-dd hh:mm:ss') |
+----------------------------------------------------------------------------------+
| 2014-02-14 16:21:01                                                              |
+----------------------------------------------------------------------------------+

推荐阅读