13位时间戳转毫秒时间类型

在生产实践中，例如：需要对页面点击行为日志进行ETL清洗，这时会发现，同一秒中会有多个请求日志，需要区分这些请求日志的先后顺序，必须采用毫秒统计。这就意味着将13位时间戳除以1000转换成10位的方法已经不复存在了。

虽然hive中没有提供13位时间戳转换内置函数，但是我们可以使用以下方法解决：

--时间戳转换成固定日期
select
    concat(t,".",substring(1629637370845,11,13))
from(
    select from_unixtime(
        cast(substring(1629637370845,0,10) as bigint),
        'yyyy-MM-dd HH:mm:ss') as t
)tmp;
输出结果：
2021-08-22 13:02:50.845
-- 固定日期转换成时间戳
select concat(t,substring('2021-08-22 13:02:50.845',21,23))
from (
    select unix_timestamp(
        concat(substring('2021-08-22 13:02:50.845',1,19),' +0800')
        ,'yyyy-MM-dd HH:mm:ss Z') as t
)tmp;
输出结果：
1629608570845

1、读者也可以使用自定义函数完成，实现过程也很简单。

2、from_unixtime存在一些小坑，后文将会具体介绍。

关于UTC时间

世界的每个地区都有自己的本地时间，整个地球分为二十四时区，每个时区都对应着自己的本地时间。在国际无线电通信中，为了统一时间，而普遍使用一个标准时间，称为 通用协调时 ( UTC , Universal Time Coordinated)。UTC与 格林尼治平均时 ( GMT , Greenwich Mean Time)一样，都与英国伦敦的本地时相同。UTC与GMT含义完全相同。

以北京时间东八区（UTC+8）为例，需要将UTC时间转换成北京时间，可以使用以下两个函数：

1、 to_utc_timestamp(timestamp, timezone)        --将timestamp转换成UTC时间

2、 from_utc_timestamp(timestamp, timezone)    --UTC时间转换成 timezone时间

--UTC时间转换成北京时间
select from_utc_timestamp(1629637370845,'PRC');
select from_utc_timestamp(1629637370845,'Asia/Shanghai');
输出结果：
2021-08-22 21:02:50.845
2021-08-22 21:02:50.845
--北京时间转换成UTC/GMT时间
select to_utc_timestamp(1629637370845,'UTC');
select to_utc_timestamp(1629637370845,'GMT');
输出结果：
2021-08-22 13:02:50.845
2021-08-22 13:02:50.845

备注：北京时间可以使用PRC（the People's Republic of China，中华人民共和国）简写，也可以使用Asia/Shanghai（亚洲/上海）。

获取当前时间

UNIX_TIMESTAMP()         -- 得到时间戳

--返回当天三种方式
SELECT CURRENT_DATE();
SELECT CURRENT_TIMESTAMP();
select unix_timestamp();
输出结果：
2021-08-22
2021-08-22 14:48:18.455
1629614898
--返回当月的第一天
select trunc('2021-08-22','MM');
--返回当年的第一天
select trunc('2021-08-22','YEAR');
输出结果：
2021-08-01
2021-01-01

from_unixtime（）填坑

1、from_unixtime(unix_time, format)         --返回指定时间内的unix_时间

2、date_format(date/timestamp/string, fmt)

--将日期/时间戳/字符串转换为日期格式fmt指定格式的字符串值。

select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss');
select date_format(current_timestamp,'yyyy-MM-dd HH:mm:ss');

输出结果：
2021-08-22 09:34:11
2021-08-22 17:34:11

当前日期：2021-08-22 17:34:11

读者可能会发现两者时间相差8个小时，翻找官方文档查看unix_timestamp获取时间属性：

Converts time string in format yyyy-MM-dd HH:mm:ss to Unix timestamp (in seconds), using the default timezone and the default locale, return 0 if fail；
翻译：
将时间字符串转换为Unix时间戳(以秒为单位)，使用默认时区和默认语言环境，如果失败，返回0。

所以想要获取正确的时间，可以修改虚拟机的默认时区和默认语言环境，或者简单的使用 select date_format(current_timestamp,'yyyy-MM-dd HH:mm:ss') 代替。实际上，当在使用 unix_timestamp()时，官方已经提示unix_timestamp(void)被弃用，如图所示：

小提示：我们还可以使用 select from_utc_timestamp(unix_timestamp() * 1000,'PRC');来获取正确的时间。

固定日期转换成时间戳

1、to_unix_timestamp(date[, pattern])        --返回UNIX时间戳

2、unix_timestamp(date[, pattern])            --将时间转换为数字

select to_unix_timestamp('2021-08-22 14:51:30 +0800','yyyy-MM-dd HH:mm:ss Z');
select unix_timestamp('2021-08-22 14:51:30 +0800','yyyy-MM-dd HH:mm:ss Z');
返回结果：
1629615090
1629615090
--注意：如果不给定时区，会比正确结果领先8小时。
select unix_timestamp('2021-08-22 14:51:30 +0800','yyyy-MM-dd HH:mm:ss');
返回结果：
1629643890

1. 特殊的，将22/Aug/2021:15:05:01 +0800转换成时间戳：

select unix_timestamp('22/Aug/2021:15:05:01 +0800','dd/MMM/yyy:HH:mm:ss Z');
输出结果：
1629615901

2.特殊的，将22/Aug/2021:15:05:01 +0800转换成正常格式（yyyy-MM-dd HH:mm:ss）：

select from_unixtime(
        to_unix_timestamp('22/Aug/2021:15:05:01 +0800', 'dd/MMM/yyy:HH:mm:ss')
输出结果：
2021-08-22 15:05:01

备注：如果将写成

select from_unixtime(
to_unix_timestamp('22/Aug/2021:15:05:01 +0800', 'dd/MMM/yyy:HH:mm:ss Z ')
);

则会晚8小时，输出结果为：

2021-08-22 07:05:01

返回日期时间字段中的日期部分

返回日期：2021-08-22
select to_date('2021-08-22 14:48:18');

返回日期中的年：2021
select year('2021-08-22 14:48:18');

返回日期中的月：8
select month('2021-08-22 14:48:18');

返回日期中的日：22
select day('2021-08-22 14:48:18') ;

返回日期中的时：14
select hour('2021-08-22 14:48:18') ;

返回日期中的分：48
select minute('2021-08-22 14:48:18') ;

返回日期中的秒：18
select second('2021-08-22 14:48:18') ;

返回日期在当前的周数：33
select weekofyear('2021-08-22 14:48:18') ;

返回日期中date是星期几：1（周日--1、周一--2、周二--3.......）

select dayofweek('2021-08-22 14:48:18');

返回日期的增减操作

返回结束日期减去开始日期的天数：10
select datediff('2021-08-22','2021-08-12')

返回开始日期增加days天后的日期：2021-09-01
select date_add('2021-08-22',10)

返回开始日期减少days天后的日期：2021-08-12
select date_sub('2021-08-22',10)

有问题可以一起探讨，希望对你有帮助；

我的一小步，填坑一大步！！！

将 13 位 整数的 毫秒 时间戳 转 化成本地普通 时间 (datetime格式)将 10 位 整数的秒级 时间戳 转 化成本地普通 时间 (datetime格式)将 13 位 整数的 毫秒 级 时间戳 转 化成 本地普通 时间 (字符串格式)将 10 位 整数的秒级 时间戳 转 化成 本地普通 时间 (字符串格式) TIMESTAMP 的值可以是整数，也就是距离Unix新纪元 时间 (1970年1月1日，午夜12点)的秒数；也可以是浮点数，即距离Unix新纪元 时间 的秒数，精确到纳秒(小数点后保留9 位 数)；还可以是字符串，即JDBC所约定的 时间 字符串格式，格式为YYYY-MM-DD hh:mm:ss.fffffffff。 TIMESTAMP 表示的是 UTC 时间 ，即协调世界时。 UTC 现在作为世界标准 时间 使用。可以用from... Hive 不支持update的操作。数据一旦导入，则不可修改。要么drop掉整个表，要么建立新的表，导入新的数据。 load：加载数据到表 load data [local] inpath ‘filepath’ overwrite | into table 表名 [partition (partcol1=val1,…)]; 单纯的复制/移动：将 filepath 中指定的文件复制到目标文件所指定的目录中。如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。其中： load data：表示加载数据 local：表示从本地加载数 用from_ utc _ timestamp 把 UTC 标准 时间 切换到北京 时间 ： --假如现在有 UTC 时间 ：'1970-01-01 00:00:00'，把它 转 为北京 时间 ， hive S. 一、 时间 函数的使用1.from_unixtime(bigint unixtime[, string format])：将是将戳 转 化为 日期 将 时间 的秒值 转 换成format格式(format可为“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等等)如from_unixtime(1250111000,"yyyy-MM-dd") 得到2009-... Hive 处理 13 位 时间戳 ，得到年月日时分秒（北京 时间 ） 使用 Hive 自带函数 将 13 位 时间戳 ： 1682238448915 转 成 今天的 时间 （北京 时间 ），格式样例：'2023-04-23 16:27:28' 例如：select unix_ timestamp () --1565858389 2.unix_ timestamp (string timestame) 输入的 时间戳 格式必须为’yyyy-MM-dd HH:mm:ss’,如不符合则返回null select unix_ timestamp ('2019-08-15 16:40:00') --1565858400 select unix_t 使用 hive 3.1.2和spark3.0.0配置 hive on spark的时候，发现官方下载的 hive 3.1.2和spark3.0.0不兼容， hive 3.1.2对应的版本是spark2.3.0，而spark3.0.0对应的hadoop版本是hadoop2.6或hadoop2.7。 所以，如果想要使用高版本的 hive 和hadoop，我们要重新编译 hive ，兼容spark3.0.0。除了兼容spark3.0.0外，还将 hive 3.1.2的guava的版本进行了提升，和hadoop3.x保持一致，以便兼容hadoop3.1.3。 17. Hive 中HQL的基本语法（一） 18. Hive 中HQL的基本语法（二） 19. Hive 中order by、sort by、distribute by与cluster by的使用 20. Hive 中分析函数与窗口函数 21. Hive 中UDF的介绍 22. Hive 中使用自定义UDF实现 日期 格式转换 23. Hive Server2的介绍及三种连接方式 24. Hive 元数据、fetch task和严格模式的介绍 第3章：Sqoop Sqoop及用户行为分析案例 25.CDH版本框架的介绍 26. CDH版本框架的环境部署 27.Sqoop的介绍及其实现原理 28.Sqoop的安装部署及连接测试 29.Sqoop将MySQL数据导入到HDFS（一） 30.Sqoop将MySQL数据导入到HDFS（二） 31.Sqoop中的增量导入与Sqoop job 32.Sqoop将MySQL数据导入 Hive 表中 33.Sqoop的导出及脚本中使用的方式 34.案例分析-动态分区的实现 35.案例分析-源表的分区加载创建 36.案例分析-指标分析使用Sqoop导出 第4章： Hive 复杂用户行为案例分析及优化 37.自动批量加载数据到 hive 38. Hive 表批量加载数据的脚本实现（一） 39. Hive 表批量加载数据的脚本实现（二） 40. HIve 中的case when、cast及unix_ timestamp 的使用 41.复杂日志分析-需求分析 42.复杂日志分析-需求字段讲解及过滤 43.复杂日志分析-字段提取及临时表的创建 44.复杂日志分析-指标结果的分析实现 45. Hive 中数据文件的存储格式介绍及对比 46.常见的压缩格式及MapReduce的压缩介绍 47.Hadoop中编译配置Snappy压缩 48.Hadoop及 Hive 配置支持snappy压缩 49. Hive 中的常见调优 50. Hive 中的数据倾斜及解决方案-三种join方式 51. Hive 中的数据倾斜及解决方案-group by 52. Hive 中使用正则加载数据 53. Hive 中使用Python脚本进行预处理 第5章：Zeus任务资源调度工具 54.资源任务调度框架介绍 55.企业中常见的任务调度框架 56.Zeus的介绍及基本实现原理 57.Zeus安装部署-基本环境配置 58.Zeus安装部署-配置文件的修改 59.Zeus安装部署-编译打包 60.Zeus平台的调度使用 61.Zeus平台实现任务调度应用（一） 62.Zeus平台实现任务调度应用（二） 63.Zeus平台实现任务调度应用（三）