程序员经验分享-hwhale

[1144]Hive常用日期格式转换

2023-11-13

获取当前时间

  • 获取当前时间戳
-- Hive中获取当前时间戳，默认使用unix_timestamp()函数，精确到秒
select unix_timestamp(); -- 1677062942
  • Hive中获取毫秒级别的时间戳
select current_timestamp() as current_time, cast(current_timestamp() as double) * 1000 as timestamp;

2019-06-02 15:44:23.324	1559461463324
  • 把时间戳转为正常的日期
select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')
select from_unixtime(unix_timestamp(),'yyyy-MM-dd')
 
SELECT from_unixtime(1571709884123/1000,'yyyy-MM-dd HH:dd:ss.sss');
2019-10-22 10:22:44.044
  • 业务中有时存放的是包含毫秒的整数，需要先转换为秒
select from_unixtime(cast(create_time/1000 as bigint),'yyyyMMdd') as dt
  • 返回当天日期或时间
SELECT CURRENT_DATE;   --2017-06-15
select current_date();   -- 2021-10-22

-- Hive中使用current_timestamp()函数获取当前时间，精确到毫秒
select current_timestamp;  --2018-06-18 10:37:53.278
select current_timestamp();  --2023-02-22 18:46:18.406
select from_unixtime(unix_timestamp());  --2017-06-15 19:55:04
select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss.SSS') as etl_time;  --2023-02-22 18:46:18.406

select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') as etl_time;  --2023-02-22 18:46:18
select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss') as etl_time;  --2023-02-22 18:46:18

Hive中处理毫秒级别的时间戳

desc function extended to_utc_timestamp;

to_utc_timestamp(timestamp, string timezone) - Assumes given timestamp is in given timezone and converts to UTC (as of Hive 0.8.0)


select to_utc_timestamp(1559461463324, 'GMT');
+--------------------------+--+
|           _c0            |
+--------------------------+--+
| 2019-06-02 15:44:23.324  |
+--------------------------+--+
1 row selected (2.745 seconds)
  • 将毫秒级别的时间戳转换为指定格式的毫秒时间，SSS代表毫秒
select date_format(to_utc_timestamp(1559461463324, 'GMT'), 'yyyyMMddHHmmssSSS');
+--------------------+--+
|        _c0         |
+--------------------+--+
| 20190602154423324  |
+--------------------+--+
1 row selected (0.323 seconds)


select date_format(to_utc_timestamp(1559461463324, 'GMT'), 'yyyy/MM/dd HH:mm:ss.SSS');
+--------------------------+--+
|           _c0            |
+--------------------------+--+
| 2019/06/02 15:44:23.324  |
+--------------------------+--+
1 row selected (0.183 seconds)
  • 将毫秒级时间转化为毫秒级时间戳
select cast(from_utc_timestamp('2023-01-30 12:00:00.123', 'yyyy-MM-dd HH:mm:ss.SSS') as double) * 1000 as timestamp1;--1675051200123

日期格式转换

  • 日期格式转换 yyyyMMdd—>yyyy-MM-dd
select from_unixtime(unix_timestamp('20211022','yyyyMMdd'),"yyyy-MM-dd");
2021-10-22
  • 固定日期转换成时间戳
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
类似sql中的date
  • 截取日期部分
select substr('2021-10-22 17:34:56',1,10)
2021-10-22

select date_format('2021-10-22 17:34:56','yyyy-MM-dd')
2021-10-22

返回日期中的年，月，日，时，分，秒，当前的周数

  • 返回日期中的年
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 trunc('2016-08-16','MM') --2016-08-01

select date_format(to_date(trunc(current_date(),'MM')),"yyyy-MM-dd");
2021-10-01
  • 返回当年的第一天
select trunc('2016-08-16','YEAR') --2016-01-01

计算日期差值

返回结束日期减去开始日期的天数

语法：datediff(string enddate,string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。

select datediff('2016-08-16','2016-08-11') ;
5

需要注意的是datediff两个日期必须是’yyyy-MM-dd’的格式，否则执行结果会是NULL
例如：

select datediff('20221220','20221231');

执行结果：NULL

而在mysql中，

select datediff('20221220','20221231');

这种写法是支持的，执行结果 -11。

返回开始日期startdate增加days天后的日期

语法：date_add(string startdate, intdays)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。

select date_add('2016-08-16',10)

需要注意的是intdays 可以是正数、也可以是负数，正数代表加，负数代表减。

返回开始日期startdate减少days天后的日期

语法：date_sub (string startdate,int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。

select date_sub('2016-08-16',10)

前一日/昨日
select date_sub(current_date(),1);
2021-10-21

最近一个月/30天
select date_sub(current_date(),30);
2021-09-22

hive日期比较函数，虽然只是一个很小的知识点，但它在实际应用有很大的作用，掌握了往往有事半功倍的效果。

比如获取某张表昨日的分区 可以使用

regexp_replace(date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1) ,'-','')

或者

date_format(date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1),'yyyyMMdd')
前一日12点/昨日12点
select concat(date_format(date_sub(current_date(),1),'yyyy-MM-dd'),' ','12');
2021-10-21 12
计算时间相差的秒数，unix_timestamp精确到秒
select nvl(unix_timestamp(substr('2023-02-13 23:59:59.199',1,19)) - unix_timestamp(substr('2023-02-13 23:59:58.800',1,19)),0) as seconds；

select nvl(unix_timestamp('2023-02-13 23:59:59.199') - unix_timestamp('2023-02-13 23:59:58.800'),0) as seconds；

select nvl(unix_timestamp('2023-02-13 23:59:59.199','yyyy-MM-dd HH:mm:ss.SSS') - unix_timestamp('2023-02-13 23:59:58.800','yyyy-MM-dd HH:mm:ss.SSS'),0) as seconds；

特殊函数

weekofyear(date1)**：返回日期date1位于该年第几周。
如weekofyear('2019-03-06')=10

datediff(date1,date2)**:返回日期date1与date2相差的天数
如datediff('2019-03-06','2019-03-05')=1

date_add(date1,int1)**:返回日期date1加上int1的日期
如date_add('2019-03-06',1)='2019-03-07'

date_sub(date1,int1)**:返回日期date1减去int1的日期
如date_sub('2019-03-06',1)='2019-03-05'

months_between(date1,date2)**:返回date1与date2相差月份
如months_between('2019-03-06','2019-01-01')=2

add_months(date1,int1)**:返回date1加上int1个月的日期，int1可为负数
如add_months('2019-02-11',-1)='2019-01-11'

last_day(date1)**:返回date1所在月份最后一天
如last_day('2019-02-01')='2019-02-28'

next_day(date1,day1)**:返回日期date1的下个星期day1的日期。day1为星期X的英文前两字母
如next_day('2019-03-06','MO') 返回'2019-03-11'

trunc(date1,string1):**返回日期最开始年份或月份。string1可为年(YYYY/YY/YEAR)或月(MONTH/MON/MM)。
如trunc('2019-03-06','MM')='2019-03-01'，trunc('2019-03-06','YYYY')='2019-01-01'

select split('12','\\.')[1]
select concat(1,'.',3)
select if(split('1.2','\\.')[1] is NULL,0,split('1.2','\\.')[1])

总结

Hive中获取时间戳的方式为unix_timestamp()函数，该函数只能够精确到秒级别的时间，对于时间精确到要求高的应用则该函数并不适合。

Hive获取当前时间毫秒级别的时间戳时需要使用cast函数将current_timestamp()转为double类型并乘以1000，则得到毫秒级别的时间戳。

对于Hive库中存储的毫秒精度的时间戳，为了确保时间精度不损失则需要使用to_utc_timestamp()函数，该函数支持毫秒级别的时间错，但需要指定当前时区。

参考：https://blog.csdn.net/wufagang/article/details/124025258
https://www.jianshu.com/p/55f9683c63f1
https://blog.csdn.net/m0_46689661/article/details/117734151
Hive中TimeStamp精度问题分析：https://blog.csdn.net/lz6363/article/details/90740061
https://blog.csdn.net/X8i0Bev/article/details/128391689

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

大数据

hive

sql

Hadoop

[1144]Hive常用日期格式转换 的相关文章

  • 在 Oracle 中使用触发器记录对表的更改

    我的一门课有一个项目 当我们的两个表发生更改时 我们需要创建一个日志 插入 更新 删除 我们需要使用Oracle触发器和PL SQL 在日志文件中 我们需要记录用户ID 日期时间 IP地址和事件 插入 更新 删除 我知道如何设置触发器 但我

  • PreparedStatement setnull方法中Types.INTEGER和Types.NULL的区别

    下面的说法有什么区别 PreparedStatement setNull 1 java sql Types NULL and PreparedStatement setNull 1 java sql Types INTEGER 第一个指示驱

  • 是否允许在流水线 PL/SQL 表函数中使用 SELECT？

    管道函数的文档指出 在 SQL 语句 通常是SELECT 并且在大多数示例中 管道函数用于数据生成或转换 接受客户作为参数 但不发出任何 DML 语句 现在 从技术上讲 可以使用 SELECT 而不会出现 Oracle 中的任何错误 ORA

  • Postgres、更新和锁定顺序

    我正在研究 Postgres 9 2 有 2 个更新 每个更新都有自己的事务 一个看起来像 UPDATE foo SET a 1 WHERE b IN 1 2 3 4 另一个也类似 UPDATE foo SET a 2 WHERE b IN

  • 在存储过程结束时显式删除本地临时表有什么好处？

    考虑以下伪 T SQL 代码 由存储过程执行 CREATE TABLE localTable

  • 外键引用多个表

    我有4张桌子 A ida name B ida B specific stuff C ida C specific stuff D ida D specific stuff 我希望另一个表 E 可以仅引用 B 或 C 而不是 D 我可以在其

  • 使用包含空值列的 WHERE 子句的更新语句

    我正在使用另一个表中的数据更新一个表上的列 这WHERE子句基于多个列 并且某些列为空 根据我的想法 这个空值是什么throwing off你的标准UPDATE TABLE SET X Y WHERE A B陈述 See 这个 SQL 小提

  • ActiveRecord 嵌套 SELECT——我可以在没有手动 SQL 的情况下完成它吗？

    我有一张桌子 上面有 除其他外 一个名字和一个等级 我想返回所有唯一名称的集合 但对于返回的每个名称 我想选择排名最高的行 这很简单 有两个嵌套的 SELECT 语句 SELECT FROM SELECT FROM foo ORDER BY

  • 部署 dacpac 所需的权限

    我正在尝试使用 sqlpackage exe 在租户上部署 dacpac 目前 我正在向将部署此功能的帐户授予 SysAdmin 或 db owner 权限 并且它工作正常 但在生产中 如果目标租户数据库属于其他应用程序 我可能无法获得这些

  • 多级排序

    我有一个表 其中包含一些记录 其中包含名称 评级等字段 我首先想要根据评级将结果限制为 20 进行排序 然后在此结果集上想要进一步应用基于名称的排序 我知道要排序我们需要使用像这样的查询 Select from table order by

  • 计算树中值的总和（递归查询）

    我在表员工 id name parentid 中有树结构 并且该表可以嵌套 employees 与另一个具有列 id employeeid quantity 的 Sales 表是一对多关系 每个员工都有销售数量 我想计算每个员工以及儿童员工

  • 按时间戳聚合

    搜索引擎优化 gt 搜索引擎优化 gt 付费 1 付费 gt 付费 gt 联盟 gt 付费 1 SEO gt 会员 1我有一个查询 结果包含客户 ID 号 营销渠道 时间戳和购买日期的数据 所以 结果可能看起来像这样 id marketin

  • 优化 LINQ 查询 - 如何缩短执行时间？

    我想知道是否有一个好的方法来优化我的 LINQ 查询 我正在使用类似于以下内容的 LINQ 查询从数据库检索数据 PKs is a list of integers var import context table Where x gt P

  • 使用 LIMIT/OFFSET 运行查询并获取总行数

    出于分页目的 我需要使用以下命令运行查询LIMIT and OFFSET条款 但我还需要计算该查询将返回的行数 而不需要LIMIT and OFFSET条款 我想运行 SELECT FROM table WHERE whatever ORD

  • 尝试使用 sql 获取单行结果？ [复制]

    这个问题在这里已经有答案了 我正在尝试显示所有员工 ID 我需要这样的结果 emp id 10 11 12 13 14 15 当尝试时 SELECT LISTAGG emp id WITHIN GROUP ORDER BY emp id A

  • Netezza SQL 将 VARCHAR 转换为二进制字符串

    我有一个位图存储为VARCHAR在内特扎 需要转换一下VARCHAR转换为 Netezza 中的二进制字符串 输入 Netezza col 值 VARCHAR 0xFFFFFFFFFFFFFFFF 期望的输出 VARCHAR gt 1111

  • MySQL：为什么 IN 子句中的第 5 个 ID 会极大地改变查询计划？

    给出以下两个查询 Query 1 SELECT log id FROM log WHERE user id IN 188858 188886 189854 203623 204072 and type in 14 15 17 ORDER B

  • 搜索多个表 (SQL)

    我需要能够有一个 SQL 查询来使用简单的搜索来搜索我的数据库 这是我的表格现在的样子 Table artists id name Table albums id artistID name Table songs id albumID n

  • PDO 连接字符串：最好的方法是什么？ [关闭]

    Closed 这个问题是基于意见的 help closed questions 目前不接受答案 我想使用 php pdo 制作一个后端应用程序 我发现了很多不同的方法来处理 PDO 连接字符串 我想知道使用 pdo 执行连接字符串的最佳方法

  • 如何使用 SQL - INSERT...ON DUPLICATE KEY UPDATE？

    我有一个脚本可以捕获推文并将其放入数据库中 我将在 cronjob 上运行脚本 然后在我的网站上显示数据库中的推文 以防止达到 Twitter API 的限制 所以我不想在我的数据库中有重复的推文 我知道我可以使用 INSERT ON DU

随机推荐

  • 星星之火：国产讯飞星火大模型的实际使用体验（与GPT对比）

    AIGC技术内容创作征文 全网寻找AI创作者 快来释放你的创作潜能吧 文章目录 1 前言 2 测试详情 2 1 文案写作 2 2 知识写作 2 3 阅读理解 2 4 语意测试 重点关注 2 5 常识性测试 重点关注 2 6 代码理解与生成

  • 【17 ＞ 分布式接口幂等性】1. 概述与接口重试的问题

    一 本章概述 二 接口幂等性 2 1 定义 2 2 运用场景 2 3 保证幂等性的策略有哪些 2 4 如何实现 2 5 如何操作 一 本章概述 什么是幂等性 幂等性设计的 核心思想 select updates delete insert

  • HTML的列表标签，表格标签

    前端学习经验 3 HTML 列表标签 li 此标签定义列表项目 用于有序列表 ol 和无序列表 ul 中 无序列表标签 ul 无序列表 ul 此标签定义无序列表 例 ul li 无序列表1 li li 无序列表2 li li 无序列表3 l

  • C#驱动ESP32控制机器臂

    物料清单 介绍 NET nanoFramework 是一个免费的开源平台 可以为受限嵌入式设备编写托管代码应用程序 它适用于多种类型的项目 包括物联网传感器 可穿戴设备 学术概念验证 机器人技术 业余爱好者 创客创作甚至复杂的工业设备 通过

  • 【VTK】FindPokedRenderer函数

    最近写VTK的响应程序发现鼠标响应函数最后都会有InvokeEvent函数 部分有FindPokedRenderer 对这两个函数的运行机制还不是很了解 现在具体介绍一下 1 FindPokedRenderer 作为actor的管理工具 还

  • 基于linux下的dm9000网卡移植全分析

    DM9000可以直接与ISA总线相连 也可以与大多数CPU直接相连 Mini2440采用的是dm9000直接连接CPU s3c2440 上 就像是nandflash一样直接被挂在CUP上 被挂在s3c2440的bank4上 小插曲1 s3c

  • web项目部署到某云Linux服务器的详细步骤

    一 安装xshell 和 xftp 1 xshell连接服务器 方式有几种 这里只介绍其中之一 在 某云 密钥对创建密钥对 然后会得到下载的密钥对文件 打开xshell 打开 新建 上图中的主机填某云 实例 中的 然后在xshell 用户身

  • Python3，Pandas这4种高频使用的筛选数据的方法，不得不说，确实挺好。

    Pandas数据筛选方法 1 引言 2 4种高频使用数据筛选方法 2 1 布尔索引 2 2 isin 方法 2 3 query 方法 2 4 loc 方法 3 总结 1 引言 小屌丝 鱼哥 share一下 数据筛选的方法呗 小鱼 Excel

  • 精度 vs 效率：模型越小，精度就一定越低吗？

    导语 深度学习是否朝着正确的方向发展 以下是我最近在伦敦 O Reilly AI Conference 和 DroidCon 上的两次谈话的改编 今年早些时候 NVIDIA 的研究人员发布了 MegatronLM 这是一个拥有 83 亿个参

  • 东方财富choice金融终端研究笔记

    东方财富choice金融终端研究笔记 最近在研究 东方财富choice金融终端 我就搞不懂了 他们弄这个东西是存心不让人懂的吗 说明PDF第四页 方式一 使用激活工具 适用于有图形界面 根据所用系统环境 运行接口激活工具LoginActiv

  • 第一节:Keras深度学习框架之环境搭建

    请在学习本节前阅读我们之前的预热课程 卷积神经网络的框架解读 上 BBM的开源HUB的博客 CSDN博客 卷积神经网络的框架解读 下 BBM的开源HUB的博客 CSDN博客 从本节开始 我们将进入到Keras的详细介绍和代码精读 为开始我们

  • 02-编写单个字节设备模块的驱动套路

    目录 1 单字节设备和多字节设备的区别 2 单字节设备 LED设备驱动 的驱动套路 2 1 头文件 2 2 定义设备驱动相关的变量 2 3 编写file operations 相关操作的函数 2 3 1 open函数 2 3 2 relea

  • matlab做角谱传播代码_AI

    运筹OR帷幄 转载 作者 机器之心 编者按 对python语言有所了解的人都知道Numpy这个数学处理工具包 而它在机器学习中也有很重要的地位 通过合理的使用Numpy这个工具 可以简单快速地搭建模型的数学计算流程 可以说是一把 利剑 普林

  • 阿里云：网络编程 bind：cannot assign requested address errno:99 问题

    解决方案 阿里云上的服务器代码绑定的 IP 需要时内网 IP ifconfig 查看 其他客户端连接服务器时所用的 IP 得是阿里云的外网 IP 查看实例即可 分析思路 猜想1 bind cannot assign requested ad

  • 解决“Pycharm中用Install Package 安装第三库出错”问题的经验

    1 问题描述 在PyCharm中通过Install Package 安装第三库 requests 报错了 但是可以通过终端Terminal可以安装 报错信息如图 2 问题分析及方法对策 根据我自己的经历以及网上搜寻所得 大致总结出如下原因

  • Python Selenium UI自动化测试

    1 自动化测试基础 1 1 自动化测试的定义 将人为的测试行为转化为机器自动执行的过程 1 2 自动化测试的目的 减少成本 提高测试效率 减少人为因素对测试的影响 1 3 什么项目适合做自动化测试 项目界面稳定 需求明确 项目周期长 测试脚

  • robot framework 接口自动化测试（2）get方式传递token

    之前介绍了get请求头不需要传递参数的方式 那么对于需要传入登录状态的token接口我们怎么测试呢 下面介绍一下 首先先做post的接口自动化获取到token token实时更新 所以每次调用需要token的get接口测试都需要post的t

  • postman-post格式报文接口的配置

    post格式报文接口的配置 1 新建请求 2 配置请求报文 输入接口URL authorization界面可配置授权信息 header界面可以配置请求头 body界面可以配置请求体 非作者允许 严禁转载 http接口有许多格式 post格式

  • pymysql中 execute 和 executemany 性能对比，以及与原生SQL 相比如何

    今天在mysql中插入大批量数据时 突然想起pymysql 还有executemany 方法 那么这两个方法到底谁快 快多少 测试环境 python3 mysql pymysql 老规矩 先上测试代码 class IN sql def in

  • [1144]Hive常用日期格式转换

    文章目录 获取当前时间 Hive中处理毫秒级别的时间戳 日期格式转换 返回日期中的年 月 日 时 分 秒 当前的周数 返回当月或当年的第一天 计算日期差值 返回结束日期减去开始日期的天数 返回开始日期startdate增加days天后的日期

热门标签