相关文章推荐
欢快的南瓜  ·  HTTP请求示例--AI中台公用文档-火山引擎·  3 天前    · 
小百科  ›  hivesql常见的与不常见的日期格式转换 (持续更新)_CCESARE的博客
date 时间戳 bigint date函数
神勇威武的葫芦
1 年前

故事起源于某日接手了 ↑ 这种日期格式的数据 mm /dd/yyyy HH:mm:ss ，

需要将数据放进hive里，肯定得转成标准格式呀。baidu了很久没找到所需函数，只找到了类似的 ：

16/Mar/2017:12:25:01 +0800 转成正常格式（yyyy-MM-dd hh:mm:ss）

select from_unixtime(to_unix_timestamp('16/Mar/2017:12:25:01 +0800', 'dd/MMM/yyy:HH:mm:ss Z'))

所以本文将持续记录各种 常用/不常用 的各类日期转换函数 。(后期视情况可能会加入自定义函数udf)

[ps:当前更新日期20200306]

首先，mm /dd/yyyy HH:mm:ss 这种格式， You can try this: 

select from_unixtime(unix_timestamp('9/26/2011 00:00:00' ,'MM/dd/yyyy HH:mm:ss'), 'yyyy-MM-dd HH:mm:ss');

timstamp毫秒时间戳(13位) 格式化 ：

毫秒时间戳是13位的，所以可以选择两种方法：1.除以1000，再格式化 

select from_unixtime(cast(updata_time/1000 as bigint), 'yyyy-MM-dd') date
from access_log;

2.字符串切割掉后3位，再格式化 

select from_unixtime(cast(substring(updata_time, 1, 10) as bigint),'yyyy-MM-dd HH') date
from access_log;

因为updata_time为bigint格式，需要转化为秒，结果会变成double格式，所以需要用cast转换

from_unixtime(cast(updata_time/1000 as bigint)) as updata_time

hive里不填写格式会默认转化为标准时间格式，所以也可以写成自己所需格式： 

from_unixtime(cast(updata_time/1000 as bigint),'yyyy-MM-dd HH:mm:ss') as updata_time

///描述一种比较坑的情况：

时间戳转时间：数据中我们希望看到的是 yyyy-MM-dd HH:mm:ss ，然而实际传来的数据可能是 ‘ 1234567890 ’ ，或者 ‘ 1234567890 ’ 

from_unixtime(cast(SUBSTR(updata_time, 1, 10) as bigint) , 'yyyy-MM-dd HH:mm:ss')

这行代码即可，槽点在于输入是10位的 bigint or double (有时候时间戳是按毫秒级，所以会有13位)：

1.如果输入 updata_time 是 int or double 类型：直接 updata_time/1000 即可；

2.如果输入是string，则： cast(SUBSTR(updata_time, 1, 10) as bigint 

from_unixtime(unix_timestamp('2018-11-05','yyyy-MM-dd'),'yyyy/MM/dd')
date_format('2015-04-08 10:10:01', 'yyyy-MM')
concat(substr('2018-09-05',1,4),substr('2018-09-05',6,2),substr('2018-09-05',9,2))

（时间仓促，有时间了再补上这三种方法的说明）

常用日期格式转换part1: 

固定日期转换成时间戳
select unix_timestamp('2016-08-16','yyyy-MM-dd') --1471276800
select unix_timestamp('20160816','yyyyMMdd') --1471276800
select unix_timestamp('2016-08-16T10:02:41Z', "yyyy-MM-dd'T'HH:mm:ss'Z'") --1471312961
16/Mar/2017:12:25:01 +0800 转成正常格式（yyyy-MM-dd hh:mm:ss）
select from_unixtime(to_unix_timestamp('16/Mar/2017:12:25:01 +0800', 'dd/MMM/yyy:HH:mm:ss Z'))
时间戳转换程固定日期
select from_unixtime(1471276800,'yyyy-MM-dd') --2016-08-16
select from_unixtime(1471276800,'yyyyMMdd') --20160816
select from_unixtime(1471312961) --    2016-08-16 10:02:41
select from_unixtime( unix_timestamp('20160816','yyyyMMdd'),'yyyy-MM-dd')  --2016-08-16
select date_format('2016-08-16','yyyyMMdd') --20160816
返回日期时间字段中的日期部分
select to_date('2016-08-16 10:03:01') --2016-08-16
取当前时间
select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')
select from_unixtime(unix_timestamp(),'yyyy-MM-dd') 
返回日期中的年
select year('2016-08-16 10:03:01') --2016
返回日期中的月
select month('2016-08-16 10:03:01') --8
返回日期中的日
select day('2016-08-16 10:03:01') --16
返回日期中的时
select hour('2016-08-16 10:03:01') --10
返回日期中的分
select minute('2016-08-16 10:03:01') --3
返回日期中的秒
select second('2016-08-16 10:03:01') --1
返回日期在当前的周数
select weekofyear('2016-08-16 10:03:01') --33
返回结束日期减去开始日期的天数
select datediff('2016-08-16','2016-08-11') 
返回开始日期startdate增加days天后的日期
select date_add('2016-08-16',10)
返回开始日期startdate减少days天后的日期
select date_sub('2016-08-16',10)
返回当天三种方式
SELECT CURRENT_DATE;
--2017-06-15
SELECT CURRENT_TIMESTAMP;--返回时分秒
--2017-06-15 19:54:44
SELECT from_unixtime(unix_timestamp());
--2017-06-15 19:55:04
返回当前时间戳
Select current_timestamp--2018-06-18 10:37:53.278
返回当月的第一天
select trunc('2016-08-16','MM') --2016-08-01
返回当年的第一天
select trunc('2016-08-16','YEAR') --2016-01-01

part2: 

--把 20180123 转换成 2018-01-23，可以使用：
select from_unixtime(unix_timestamp('${p_date}','yyyymmdd'),'yyyy-mm-dd') from table;
1.日期函数UNIX时间戳转日期函数
from_unixtime语法:from_unixtime(bigint unixtime[, stringformat]) 
返回值: string 说明: 转化UNIX时间戳（从1970-01-0100:00:00 UTC到指定时间的秒数）到当前时区的时间格式
select from_unixtime(1323308943,'yyyyMMdd') from dual;
20111208
2.获取当前UNIX时间戳函数: unix_timestamp语法:   unix_timestamp() 
返回值: bigint 说明: 获得当前时区的UNIX时间戳
select unix_timestamp() from dual;
1323309615
3.日期转UNIX时间戳函数: unix_timestamp语法:unix_timestamp(string date) 
返回值:   bigint
说明: 转换格式为“yyyy-MM-dd HH:mm:ss“的日期到UNIX时间戳。如果转化失败，则返回0。
select unix_timestamp('2011-12-07 13:01:03') from dual;
1323234063
4.指定格式日期转UNIX时间戳函数: unix_timestamp语法:   unix_timestamp(string date,string pattern) 
返回值:   bigint  说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。
select unix_timestamp('20111207 13:01:03','yyyyMMddHH:mm:ss') from dual;
1323234063
5.日期时间转日期函数: to_date语法:   to_date(string timestamp) 
返回值:   string  说明: 返回日期时间字段中的日期部分。
select to_date('2011-12-08 10:03:01') from dual;
2011-12-08
6.日期转年函数: year语法:   year(string date) 
返回值: int  说明: 返回日期中的年。
select year('2011-12-08 10:03:01') from dual;
select year('2012-12-08') fromdual;
7.日期转月函数: month语法: month   (string date) 
返回值: int  说明: 返回日期中的月份。
select month('2011-12-08 10:03:01') from dual;
select month('2011-08-08') fromdual;
8.日期转天函数: day语法: day   (string date) 
返回值: int  说明: 返回日期中的天。
select day('2011-12-08 10:03:01') from dual;
select day('2011-12-24') fromdual;
9.日期转小时函数: hour语法: hour   (string date) 
返回值: int 说明: 返回日期中的小时。
select hour('2011-12-08 10:03:01') from dual;
10.日期转分钟函数: minute语法: minute   (string date) 
返回值: int 说明: 返回日期中的分钟。
select minute('2011-12-08 10:03:01') from dual;
11.日期转秒函数: second语法: second   (string date) 
返回值: int 说明: 返回日期中的秒。
select second('2011-12-08 10:03:01') from dual;
12.日期转周函数: weekofyear语法:   weekofyear (string date) 
返回值: int 说明: 返回日期在当前的周数。
select weekofyear('2011-12-08 10:03:01') from dual;
13.日期比较函数: datediff语法:   datediff(string enddate,string startdate) 
返回值: int  说明: 返回结束日期减去开始日期的天数。
select datediff('2012-12-08','2012-05-09')from dual;
14.日期增加函数: date_add语法:   date_add(string startdate, intdays) 
返回值: string 说明: 返回开始日期startdate增加days天后的日期。
select date_add('2012-12-08',10)from dual;
2012-12-18
15.日期减少函数: date_sub语法:   date_sub (string startdate,int days) 
返回值: string  说明: 返回开始日期startdate减少days天后的日期。
select date_sub('2012-12-08',10)from dual;
2012-11-28

part3:
 

Input FormatCodeOutput Format
ddMMyyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’ddMMyyyy’)))yyyy-MM-dd
dd-MM-yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd-MM-yyyy’)))yyyy-MM-dd
dd/MM/yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd/MM/yyyy’)))yyyy-MM-dd
dd MM yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd MM yyyy’)))yyyy-MM-dd
dd.MM.yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd.MM.yyyy’)))yyyy-MM-dd
ddMMMyyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’ddMMMyyyy’)))yyyy-MM-dd
dd-MMM-yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd-MMM-yyyy’)))yyyy-MM-dd
dd/MMM/yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd/MMM/yyyy’)))yyyy-MM-dd
dd MMM yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd MMM yyyy’)))yyyy-MM-dd
dd.MMM.yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd.MMM.yyyy’)))yyyy-MM-dd
ddMMMMyyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’ddMMMMyyyy’)))yyyy-MM-dd
dd-MMMM-yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd-MMMM-yyyy’)))yyyy-MM-dd
dd/MMMM/yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd/MMMM/yyyy’)))yyyy-MM-dd
dd MMMM yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd MMMM yyyy’)))yyyy-MM-dd
dd.MMMM.yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd.MMMM.yyyy’)))yyyy-MM-dd
ddMMyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’ddMMyy’)))yyyy-MM-dd
dd-MM-yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd-MM-yy’)))yyyy-MM-dd
dd/MM/yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd/MM/yy’)))yyyy-MM-dd
dd MM yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd MM yy’)))yyyy-MM-dd
dd.MM.yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd.MM.yy’)))yyyy-MM-dd
ddMMMyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’ddMMMyy’)))yyyy-MM-dd
dd-MMM-yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd-MMM-yy’)))yyyy-MM-dd
dd/MMM/yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd/MMM/yy’)))yyyy-MM-dd
dd MMM yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd MMM yy’)))yyyy-MM-dd
dd.MMM.yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd.MMM.yy’)))yyyy-MM-dd
ddMMMMyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’ddMMMMyy’)))yyyy-MM-dd
dd-MMMM-yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd-MMMM-yy’)))yyyy-MM-dd
dd/MMMM/yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd/MMMM/yy’)))yyyy-MM-dd
dd MMMM yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd MMMM yy’)))yyyy-MM-dd
dd.MMMM.yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd.MMMM.yy’)))yyyy-MM-dd
MMddyyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMddyyyy’)))yyyy-MM-dd
MM-dd-yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM-dd-yyyy’)))yyyy-MM-dd
MM/dd/yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM/dd/yyyy’)))yyyy-MM-dd
MM dd yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM dd yyyy’)))yyyy-MM-dd
MM.dd.yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM.dd.yyyy’)))yyyy-MM-dd
MMMddyyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMddyyyy’)))yyyy-MM-dd
MMM-dd-yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM-dd-yyyy’)))yyyy-MM-dd
MMM/dd/yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM/dd/yyyy’)))yyyy-MM-dd
MMM dd yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM dd yyyy’)))yyyy-MM-dd
MMM.dd.yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM.dd.yyyy’)))yyyy-MM-dd
MMMMddyyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMMddyyyy’)))yyyy-MM-dd
MMMM-dd-yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM-dd-yyyy’)))yyyy-MM-dd
MMMM/dd/yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM/dd/yyyy’)))yyyy-MM-dd
MMMM dd yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM dd yyyy’)))yyyy-MM-dd
MMMM.dd.yyyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM.dd.yyyy’)))yyyy-MM-dd
MMddyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMddyy’)))yyyy-MM-dd
MM-dd-yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM-dd-yy’)))yyyy-MM-dd
MM/dd/yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM/dd/yy’)))yyyy-MM-dd
MM dd yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM dd yy’)))yyyy-MM-dd
MM.dd.yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM.dd.yy’)))yyyy-MM-dd
MMMddyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMddyy’)))yyyy-MM-dd
MMM-dd-yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM-dd-yy’)))yyyy-MM-dd
MMM/dd/yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM/dd/yy’)))yyyy-MM-dd
MMM dd yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM dd yy’)))yyyy-MM-dd
MMM.dd.yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM.dd.yy’)))yyyy-MM-dd
MMMMddyyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMMddyy’)))yyyy-MM-dd
MMMM-dd-yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM-dd-yy’)))yyyy-MM-dd
MMMM/dd/yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM/dd/yy’)))yyyy-MM-dd
MMMM dd yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM dd yy’)))yyyy-MM-dd
MMMM.dd.yyto_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM.dd.yy’)))yyyy-MM-dd
yyyyMMddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyyMMdd’)))yyyy-MM-dd
yyyy-MM-ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy-MM-dd’)))yyyy-MM-dd
yyyy/MM/ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy/MM/dd’)))yyyy-MM-dd
yyyy MM ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy MM dd ‘)))yyyy-MM-dd
yyyy.MM.ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy.MM.dd’)))yyyy-MM-dd
yyyyMMMddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyyMMMdd’)))yyyy-MM-dd
yyyy-MMM-ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy-MMM-dd’)))yyyy-MM-dd
yyyy/MMM/ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy/MMM/dd’)))yyyy-MM-dd
yyyy MMM ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy MMM dd ‘)))yyyy-MM-dd
yyyy.MMM.ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy.MMM.dd’)))yyyy-MM-dd
yyyyMMMMddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyyMMMMdd’)))yyyy-MM-dd
yyyy-MMMM-ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy-MMMM-dd’)))yyyy-MM-dd
yyyy/MMMM/ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy/MMMM/dd’)))yyyy-MM-dd
yyyy MMMM ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy MMMM dd ‘)))yyyy-MM-dd
yyyy.MMMM.ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy.MMMM.dd’)))yyyy-MM-dd
yyMMddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyMMdd’)))yyyy-MM-dd
yy-MM-ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy-MM-dd’)))yyyy-MM-dd
yy/MM/ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy/MM/dd’)))yyyy-MM-dd
yy MM ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy MM dd ‘)))yyyy-MM-dd
yy.MM.ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy.MM.dd’)))yyyy-MM-dd
yyMMMddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyMMMdd’)))yyyy-MM-dd
yy-MMM-ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy-MMM-dd’)))yyyy-MM-dd
yy/MMM/ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy/MMM/dd’)))yyyy-MM-dd
yy MMM ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy MMM dd ‘)))yyyy-MM-dd
yy.MMM.ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy.MMM.dd’)))yyyy-MM-dd
yyMMMMddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyMMMMdd’)))yyyy-MM-dd
yy-MMMM-ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy-MMMM-dd’)))yyyy-MM-dd
yy/MMMM/ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy/MMMM/dd’)))yyyy-MM-dd
yy MMMM ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy MMMM dd ‘)))yyyy-MM-dd
yy.MMMM.ddto_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy.MMMM.dd’)))yyyy-MM-dd
   
 

[ps:当前更新日期20200306] 后边有遗漏再补充
 

部分转载，感谢以下文章及其作者：
 

https://www.cnblogs.com/littlewu/p/9364273.html
 

https://www.cnblogs.com/0xcafedaddy/p/8341690.html
 

https://www.cnblogs.com/ychang/p/10688833.html
 
                    12/31/2015 00:00:0012/31/2015 00:00:001/4/2016 00:00:001/4/2016 00:00:00故事起源于某日接手了 ↑ 这种日期格式的数据 mm/dd/yyyy HH:mm:ss ，需要将数据放进hive里，肯定得转成标准格式呀。baidu了很久没找到所需函数，只找到了类似的 ：16/Mar/2017:12:25:01 +08...
Year:
yy     two digits   两位年                显示值:07
yyy    three digits 三位年                显示值:007
yyyy   four digits  四位年                显示值:2007
Month:
select unix_timestamp('2020-05-29','yyyy-MM-dd')  --返回结果 1590681600
select unix_timestamp('20200529','yyyyMMdd') --返回结果 1590681600
select unix_timestamp('2020-05-29T13:12:23Z', "yyyy-MM-dd'T'HH:mm:ss'Z'") --返回结果 1590729143.
              now//查看时区
postgres=# show timezone;
 TimeZone修改pg时区session级配置用户级配置数据库级配置全局配置（永久生效）//修改完配置时重新加载
[root@localhost ~]$ pg_ctl reload
server signaled
[root@localhost ~]$ psql
postgres=# show t
				
在HiveSQL中，可以使用FROM_UNIXTIME函数将时间戳转换成日期格式，例如：
SELECT FROM_UNIXTIME(1612345678,'yyyy-MM-dd HH:mm:ss');
这将返回一个字符串，表示时间戳对应的日期和时间，例如："2021-02-03 12:34:38"。
 
推荐文章
欢快的南瓜  ·  HTTP请求示例--AI中台公用文档-火山引擎
3 天前
Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
小百科 - 百科知识指南