首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >Hive 时间转换函数使用心得

Hive 时间转换函数使用心得

原创
作者头像
mikealzhou
发布于 2018-01-20 14:34:45
发布于 2018-01-20 14:34:45
37.2K1
举报
文章被收录于专栏:大数据平台TBDS大数据平台TBDS

导语:Hive sql 与传统的 oracle 或者mysql 的时间转换函数有一些不同,对于想将传统数据库迁移到hdfs 用 hive sql 进行处理的任务,如何用 hive sql 实现传统数据库sql 时间转换函数,是一个必须要解决的问题。

【客户案例背景】

腾讯云大数据的一个客户,将oracle数据迁移到 hdfs ,做离线大数据处理。 数据处理过程中,会采用 hive sql 去实现 oracle sql 的一些相同功能。

本次案例,客户想要取得时间:月份减一个月。被修改的时间字段是 “年-月”格式的,效果就是2015-09,减一个月得到2015-08。如果用oracle去做,就很简单,直接调用三个函数:

代码语言:js
AI代码解释
复制
SQL> select to_char(add_months(to_date('2016-09','yyyy-mm'),-1),'yyyy-mm') from dual;
------------------------------
2016-08

客户想要在 hive 里面实现上述 oracle sql 的同样效果,必须要满足以下两个条件:

(1) 首先,hive 里面得有函数识别 “年-月”这种形式的时间格式;

(2) 然后,还得能够有函数能够实现 “年-月”时间格式的 “+1”或者“-1”功能;

而最难之处在于: hive 的时间函数 “无法返回到月份级别”。

(1) 首先,hive里面的to_date函数:日期时间转日期函数: to_date语法:   to_date(string timestamp)

返回值:   string

说明: 返回日期时间字段中的日期部分。只能识别到 “年-月-日” 级别的时间,无法识别 “年-月” 级别的时间。

举例:

代码语言:js
AI代码解释
复制
hive> select to_date('2016-09-10');
OK
2016-09-10
Time taken: 0.048 seconds, Fetched: 1 row(s)
hive> select to_date('2016-09');
OK
NULL
Time taken: 0.045 seconds, Fetched: 1 row(s)

(2) 其次,add_months 函数也无法识别 月份 级别的时间:add_months(string start_date, int num_months)

代码语言:js
AI代码解释
复制
hive> select add_months('2016-09-10',-1);
OK
2016-08-10
Time taken: 0.039 seconds, Fetched: 1 row(s)
hive> select add_months('2016-09',-1);
OK
NULL
Time taken: 0.042 seconds, Fetched: 1 row(s)

add_months只能识别到 “年-月-日” 级别的时间,无法识别 “年-月” 级别的时间。

(3)最后,hive 没有 to_char函数。

使用 date_sub 函数也不行: 无论是 date_sub 函数还是 cast函数都无法识别是 “年-月” 级别的时间格式

代码语言:js
AI代码解释
复制
hive> select date_sub('2016-09',30);
OK
NULL
Time taken: 0.055 seconds, Fetched: 1 row(s)
hive> select date_sub('2016-09-10',30);
OK
2016-08-11
Time taken: 0.042 seconds, Fetched: 1 row(s)
hive> select date_sub(cast('2016-09' as date),30);
OK
NULL
Time taken: 0.046 seconds, Fetched: 1 row(s)
hive> select date_sub(cast('2016-09-10' as date),30);
OK
2016-08-11
Time taken: 0.04 seconds, Fetched: 1 row(s)

我采用的方法是:

代码语言:js
AI代码解释
复制
select from_unixtime((unix_timestamp('2015-09','yyyy-MM')-1296000),'yyyy-MM');
OK
2015-08
Time taken: 0.082 seconds, Fetched: 1 row(s)

解释一下原理:

unix_timestamp(string date, string pattern) 函数表示把 对应格式的时间 转换为 一个整数(这个整数表示 1970-01-01 00:00:00 到 指定时间的经历的秒数),然后减去1296000(表示半个月15天的总秒数)。

然后调用 from_unixtime 函数 ,将上面计算得到的整数转换为 ‘yyyy-MM’月份形式

【下面详细介绍hive 常见的时间转换函数】

注: 以下的 sql 语句,没有以 “from dual”结尾。某些 hive 版本可能需要在 sql 语句结尾加上from dual。

1、from_unixtime

日期函数UNIX时间戳转日期函数: from_unixtime语法:   from_unixtime(bigint unixtime[, string format])

返回值: string

说明: 转化UNIX时间戳(从1970-01-01 00:00:00 UTC到指定时间的秒数)到当前时区的时间格式

举例:

代码语言:js
AI代码解释
复制
hive>   select from_unixtime(1323308943,'yyyy-MM-dd HH:mm:ss');
2011-12-08 09:49:03
hive>   select from_unixtime(1323308943,'yyyyMMdd');
20111208
hive>   select from_unixtime(1323308943,'yyyy-MM-dd');
2011-12-08
hive> select from_unixtime(1323308943,'yyyy-MM');
2011-12

可以识别到 月 一级的时间

2、unix_timestamp: 三种使用方法:unix_timestamp(),  unix_timestamp(string date),  unix_timestamp(string date, string pattern)

获取当前UNIX时间戳函数: unix_timestamp语法:   unix_timestamp()

返回值:   bigint

说明: 获得当前时区的UNIX时间戳

举例:

代码语言:js
AI代码解释
复制
hive>   select unix_timestamp();
1323309615

日期转UNIX时间戳函数: unix_timestamp语法:   unix_timestamp(string date)

返回值:   bigint

说明: 转换格式为“yyyy-MM-dd HH:mm:ss“的日期到UNIX时间戳。如果转化失败,则返回0。

举例:

代码语言:js
AI代码解释
复制
hive>   select unix_timestamp('2011-12-07 13:01:03');
1323234063

hive> select unix_timestamp('2011-12-07');  注:这个时候,只能识别 2011-12-07 13:01:03 这种完全格式的时间

NULL

指定格式日期转UNIX时间戳函数: unix_timestamp语法:   unix_timestamp(string date, string pattern)

返回值:   bigint

说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败,则返回0。

举例:

代码语言:js
AI代码解释
复制
hive>   select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
1323234063
hive> select unix_timestamp('2011-12-07 13:05','yyyy-MM-dd HH:mm');
1323234300
hive> select unix_timestamp('2011-12','yyyy-MM');
1322668800

注: 这个是可以识别到 的时间格式一级的。可以识别到 月 一级的时间

3、date_format

date_format 的语法: date_format(date/timestamp/string ts, string fmt)

代码语言:js
AI代码解释
复制
hive> select date_format('2015-04-08', 'y');
2015
hive> select date_format('2015-04-08', 'yyyy');
2015
hive> select date_format('2015-04-08', 'yyyy-MM');
2015-04
hive> select date_format('2015-04-08 10:10:01', 'yyyy-MM');
2015-04
hive> select date_format('2015-04-08', 'yyyy-MM-dd');
2015-04-08

可以识别到 一级的时间

4、to_date

日期时间转日期函数: to_date语法:   to_date(string timestamp)

返回值:   string

说明: 返回日期时间字段中的日期部分。

举例:

代码语言:js
AI代码解释
复制
hive>   select to_date('2011-12-08 10:03:01');
2011-12-08
hive> select to_date('2011-12-08');
2011-12-08
hive> select to_date('2011-12');
NULL

所以 to_date 只能识别到 的时间一级

5、year

日期转年函数: year语法:   year(string date)

返回值: int

说明: 返回日期中的年。

举例:

代码语言:js
AI代码解释
复制
hive>   select year('2011-12-08 10:03:01');
2011
hive>   select year('2012-12-08');
2012
hive> select year('2012-12');
NULL

所以  year 只能识别到 的时间一级

6、month

日期转月函数: month语法: month   (string date)

返回值: int

说明: 返回日期中的月份。

举例:

代码语言:js
AI代码解释
复制
hive>   select month('2011-12-08 10:03:01');
12
hive>   select month('2011-08-08');
8
hive>   select month('2011-08');
NULL

所以  month 只能识别到 的时间一级

7、day

一样的,day 只能识别到 的时间一级

日期转天函数: day语法: day   (string date)

返回值: int

说明: 返回日期中的天。

举例:

代码语言:js
AI代码解释
复制
hive>   select day('2011-12-08 10:03:01');
8
hive>   select day('2011-12-24');
24
hive>   select day('2011-12');
NULL

注: 以下所有函数,都只能识别到 的时间一级,不能识别到 这么大的时间一级

日期转小时函数: hour语法: hour   (string date)

返回值: int

说明: 返回日期中的小时。

举例:

代码语言:js
AI代码解释
复制
hive>   select hour('2011-12-08 10:03:01');
10

日期转分钟函数: minute语法: minute   (string date)

返回值: int

说明: 返回日期中的分钟。

举例:

代码语言:js
AI代码解释
复制
hive>   select minute('2011-12-08 10:03:01');
3

日期转秒函数: second语法: second   (string date)

返回值: int

说明: 返回日期中的秒。

举例:

代码语言:js
AI代码解释
复制
hive>   select second('2011-12-08 10:03:01');
1

8、weekofyear

日期转周函数: weekofyear语法:   weekofyear (string date)

返回值: int

说明: 返回日期在当前的周数。

举例:

代码语言:js
AI代码解释
复制
hive>   select weekofyear('2011-12-08 10:03:01');
49

9、datediff

日期比较函数: datediff语法:   datediff(string enddate, string startdate)

返回值: int

说明: 返回结束日期减去开始日期的天数。

举例:

代码语言:js
AI代码解释
复制
hive>   select datediff('2012-12-08','2012-05-09');
213

10、date_add

日期增加函数: date_add语法:   date_add(string startdate, int days)

返回值: string

说明: 返回开始日期startdate增加days天后的日期。

举例:

代码语言:js
AI代码解释
复制
hive>   select date_add('2012-12-08',10);
2012-12-18

11、date_sub

日期减少函数: date_sub语法:   date_sub (string startdate, int days)

返回值: string

说明: 返回开始日期startdate减少days天后的日期。

举例:

代码语言:js
AI代码解释
复制
hive>   select date_sub('2012-12-08',10) from dual;
2012-11-28

【结论】

hive 所有函数都只能识别到 的时间一级,不能识别到 这么大的时间一级。但是,from_unixtime unix_timestamp 可以识别到 一级的时间。

 

【附录】

更详细的 Hive 时间函数使用方法,请参考官方使用文档:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
1 条评论
热度
最新
过于繁琐了
过于繁琐了
回复回复1举报
推荐阅读
编辑精选文章
换一批
有手就行?从0开始一步步教你搭建自己的网站(全篇7000字、102张截图说明,力求每个人看完都能做到)
这篇文章我花了两天的时间,全篇7000字,102张截图说明,精细到每一次点击、每一个输入说明
浩说编程
2021/12/18
3.5K0
有手就行?从0开始一步步教你搭建自己的网站(全篇7000字、102张截图说明,力求每个人看完都能做到)
零基础搭建WordPress博客全流程指南!
2.1 点击「网站」-「添加站点」,填写已解析到服务器的域名,数据库选择「MySQL」,设置数据库账号密码。
云惑雨问
2025/03/24
2480
零基础搭建WordPress博客全流程指南!
小白安装Discuz!Q教程,学不会你过来打我!
你知道吗?小白也可以安装Discuz!Q啦!你不需要敲代码也不用懂太多的技术,严格按照下面的安装教程操作,就可以成功安装上Discuz!Q。 在开始之前,我们先来了解一下,安装Discuz!Q的两大要素是:域名和服务器。简单来说,域名是用来打开网站的大门,在浏览器上输入域名,你就能打开相应的网页;而云服务器则是一个用来存放网站内容的地方。 当然,想要让别人正常访问你搭建的站点,你需要给网站做备案;想要使用域名打开相应的站点,你需要将域名解析到对应的IP地址;想要在浏览器上安全访问你的站点,你还需要安装SS
腾讯云DNSPod团队
2020/06/28
10.6K3
【原创教程】夏末浅笑教你如何利用宝塔面板安装Discuz!Q
在开始之前,我们先来了解一下,安装Discuz!Q的两大要素是:域名和服务器。简单来说,域名是用来打开网站的大门,在浏览器上输入域名,你就能打开相应的网页;而云服务器则是一个用来存放网站内容的地方。
夏末浅笑
2020/06/20
4.3K0
【原创教程】夏末浅笑教你如何利用宝塔面板安装Discuz!Q
WordPress零基础新手建站完整教程
WordPress主题:选择一款合适的WordPress主题,免费的,付费的都有不错的。
青梅煮码
2023/01/12
1.1K1
【玩转腾讯云】基于serverless搭建wordpress详细教程及配置体验
serverless中的server是服务器的意思,less是没有的意思,顾名思义也就是无服务器
炒香菇的书呆子
2021/05/03
12K12
腾讯云:基于腾讯云搭建WordPress(领取腾讯云优惠券)
连接你的服务器(很多人比较纠结,Linux系统不懂怎么办,这个教程不需要你懂Linux都能操作),需要下载一个软件,putty,putty是Linux链接工具,百度搜索一下
主机优惠教程
2019/03/27
107.2K0
腾讯云:基于腾讯云搭建WordPress(领取腾讯云优惠券)
腾讯云搭建WordPress网站教程详解
建网站本身是没有什么难度的,只是配套需要的东西有些繁琐,我们需要提前准备一些软件之类的必备的东西,其中主要包括:
主机精选@主机点评
2019/09/19
9K0
腾讯云搭建WordPress网站(图文教程)
建网站本身是没有什么难度的,只是配套需要的东西有些繁琐,我们需要提前准备一些软件之类的必备的东西,其中主要包括:
用户2416682
2019/11/02
5.1K0
腾讯云搭建WordPress网站(图文教程)
写给小白,从零开始拥有一个酷炫上线的网站!
两个网站都是基于宝塔进行搭建的,cxyxiaowu 是 WordPress 网站系统,algomooc 是 Halo 动态博客系统。
五分钟学算法
2020/04/22
2.4K0
写给小白,从零开始拥有一个酷炫上线的网站!
零基础的萌新搭建属于自己的Typecho/WordPress等博客教程
说明:一直以来老有同学问博主怎么搭建博客,最好出个教程,然后就拖到了现在,这里就发个超小白教程,适合对建站不懂又想拥有一个自己的博客的同学,有闲置的VPS服务器的也可以搭建玩玩,不然就很浪费了。
子润先生
2021/06/01
1.5K0
如何使用Cloudways搭建WordPress网站
如今,搭建网站已经变得非常简单,这主要得益于开源的CMS建站系统的兴起。即使是不懂编程的人也能轻松搭建自己的网站,这些CMS系统提供了丰富的主题模板和插件,使用户可以通过简单的拖放和配置操作来建立自己的网站。
无代码Dev
2024/05/27
3550
如何使用Cloudways搭建WordPress网站
腾讯云轻量应用服务器配置及建网站教程
腾讯云轻量应用服务器如何使用?新手站长以搭建WordPress为例,使用宝塔镜像,先在防火墙开通端口。腾讯云百科来详细说下腾讯云服务器从购买、配置到网站上线全流程,包括轻量服务器配置选择、应用镜像选择、重置密码、防火墙开放端口教程等详细教程:
用户9264446
2022/08/10
9.7K0
腾讯云轻量应用服务器配置及建网站教程
5分钟玩转Lighthouse|零基础也能拥有WordPress个人博客
【5分钟玩转Lighthouse】系列文章将为大家分享轻量应用服务器使用教程,提供丰富的实践指南,帮助大家快速上手并获得最佳产品体验。本期主要介绍如何借力 Lighthouse 实例,快速搭建基于 WordPress 的个人博客网站。 背景概述 腾讯云轻量应用服务器 Lighthouse 是新一代面向中小企业及开发者的云服务器产品,简单易用,一站式融合多款云服务,能帮助用户在云端快速构建网站、博客、电商、论坛等各类应用以及各类开发测试环境。 WordPress 作为全球最流行的开源博客和内容管理网站的
腾讯云计算产品团队
2020/09/25
2.6K1
不会建站?10分钟教你零基础快速搭建个人网站!
轻量应用服务器(Light server)是一种内置应用型镜像或系统型镜像的小规格云服务器, 绑定适配大小的磁盘和带宽,为中小型企业和个人用户提供官网搭建、web应用搭建、云上学习和测试等场景的服务。
网络安全自修室
2022/12/06
2.6K0
不会建站?10分钟教你零基础快速搭建个人网站!
【干货】保姆级超细教程从购买服务器到网站搭建成功!
云服务器(Elastic Compute Service, ECS)是一种简单高效、安全可靠、处理能力可弹性伸缩的计算服务。其管理方式比物理服务器更简单高效。用户无需提前购买硬件,即可迅速创建或释放任意多台云服务器。
释然IT杂谈
2022/10/27
5.1K0
【干货】保姆级超细教程从购买服务器到网站搭建成功!
腾讯云Linux服务器搭建一个WordPress博客
看到网络上很多人买了腾讯云服务器后却不知道怎么搭建WordPress博客,看了一下腾讯云出的WordPress教程,我觉得对新手来说难度还是太大了,所以就根据自己的建站经历写了一篇,本文采用腾讯云服务器CVM+宝塔管理面板+WordPress的方式搭建,力求简单易懂,希望能帮助到需要的朋友们。
主机优惠教程
2019/05/28
18.2K3
腾讯云Linux服务器搭建一个WordPress博客
【图文】腾讯云服务器搭建网站教程(宝塔面板+wordpress)
建网站本身是没有什么难度的,只是配套需要的东西有些繁琐,我们需要提前准备一些软件之类的必备的东西,其中主要包括:
云上云
2019/07/02
10.5K2
【图文】腾讯云服务器搭建网站教程(宝塔面板+wordpress)
利用云服务器搭建网站教程
进入到了腾讯云界面,我们输入我们想注册的域名,然后查询,如果我们想要的域名没有被注册的话,我们就可以注册一个,各个后缀价格不同,看自己喜好。域名注册完成之后需要进行实名认证,腾讯云的速度的话我这里是几分钟就实名认证审核通过了,速度还是蛮快的.
Chuanrui 初见之旅
2022/11/14
5.1K0
利用云服务器搭建网站教程
我的Serverless实战—基于Serverless搭建WordPress个人博客图文教程
serverless中的server是服务器的意思,less是没有的意思,顾名思义也就是无服务器
炒香菇的书呆子
2021/05/06
1.9K0
推荐阅读
相关推荐
有手就行?从0开始一步步教你搭建自己的网站(全篇7000字、102张截图说明,力求每个人看完都能做到)
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档