SPARK-SQL内置函数之时间日期类
转载请注明转自：http://www.cnblogs.com/feiyumo/p/8760846.html
一、获取当前时间
1.current_date获取当前日期
2018-04-09
2.current_timestamp/now()获取当前时间
2018-04-09 15:20:49.247
二、从日期时间中提取字段
1.year,month,day/dayofmonth,hour,minute,second
Examples:> SELECT day('2009-07-30'); 30
2.dayofweek (1 = Sunday, 2 = Monday, ..., 7 = Saturday),dayofyear
Examples:> SELECT dayofweek('2009-07-30'); 5
Since: 2.3.0
3.weekofyear
weekofyear(date) - Returns the week of the year of the given date. A week is considered to start on a Monday and week 1 is the first week with >3 days.
Examples:> SELECT weekofyear('2008-02-20'); 8
4.trunc截取某部分的日期，其他部分默认为01
第二个参数 ["year", "yyyy", "yy", "mon", "month", "mm"]
Examples:
> SELECT trunc('2009-02-12', 'MM');
2009-02-01
> SELECT trunc('2015-10-27', 'YEAR');
2015-01-01
5.date_trunc ["YEAR", "YYYY", "YY", "MON", "MONTH", "MM", "DAY", "DD", "HOUR", "MINUTE", "SECOND", "WEEK", "QUARTER"]
Examples:> SELECT date_trunc('2015-03-05T09:32:05.359', 'HOUR'); 2015-03-05T09:00:00
Since: 2.3.0
6.date_format将时间转化为某种格式的字符串
Examples:> SELECT date_format('2016-04-08', 'y'); 2016
三、日期时间转换
1.unix_timestamp返回当前时间的unix时间戳
Examples:
> SELECT unix_timestamp(); 1476884637
> SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd'); 14600412002.from_unixtime将时间戳换算成当前时间，to_unix_timestamp将时间转化为时间戳
Examples:
> SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss'); 1970-01-01 00:00:00
>SELECT to_unix_timestamp('2016-04-08', 'yyyy-MM-dd'); 1460041200
3.to_date/date将字符串转化为日期格式，to_timestamp（Since: 2.2.0）
> SELECT to_date('2009-07-30 04:17:52'); 2009-07-30
> SELECT to_date('2016-12-31', 'yyyy-MM-dd'); 2016-12-31
> SELECT to_timestamp('2016-12-31 00:12:00'); 2016-12-31 00:12:004.quarter 将1年4等分(range 1 to 4)
Examples:> SELECT quarter('2016-08-31'); 3
四、日期、时间计算
1.months_between两个日期之间的月数
months_between(timestamp1, timestamp2) - Returns number of months between timestamp1 and timestamp2.
Examples:> SELECT months_between('1997-02-28 10:30:00', '1996-10-30'); 3.94959677
2. add_months返回日期后n个月后的日期
Examples:> SELECT add_months('2016-08-31', 1); 2016-09-30
3.last_day(date),next_day(start_date, day_of_week)
Examples:
> SELECT last_day('2009-01-12'); 2009-01-31> SELECT next_day('2015-01-14', 'TU'); 2015-01-204.date_add,date_sub(减)
date_add(start_date, num_days) - Returns the date that is num_days after start_date.
Examples:
> SELECT date_add('2016-07-30', 1); 2016-07-315.datediff（两个日期间的天数）
datediff(endDate, startDate) - Returns the number of days from startDate to endDate.
Examples:> SELECT datediff('2009-07-31', '2009-07-30'); 1
6.关于UTC时间
to_utc_timestamp
to_utc_timestamp(timestamp, timezone) - Given a timestamp like '2017-07-14 02:40:00.0', interprets it as a time in the given time zone, and renders that time as a timestamp in UTC. For example, 'GMT+1' would yield '2017-07-14 01:40:00.0'.
Examples:> SELECT to_utc_timestamp('2016-08-31', 'Asia/Seoul'); 2016-08-30 15:00:0
from_utc_timestamp
from_utc_timestamp(timestamp, timezone) - Given a timestamp like '2017-07-14 02:40:00.0', interprets it as a time in UTC, and renders that time as a timestamp in the given time zone. For example, 'GMT+1' would yield '2017-07-14 03:40:00.0'.
Examples:> SELECT from_utc_timestamp('2016-08-31', 'Asia/Seoul'); 2016-08-31 09:00:00
最新文章
- mui 动态加载数据出现的问题处理 (silder )
- LVS+keepalived配置
- 2015 Multi-University Training Contest 2
- Android网络传输中必用的两个加密算法:MD5 和 RSA
- 浅尝key-value数据库（三）——MongoDB的分布式
- phpStudy环境配置多个站点，绑定域名
- 如何用 Graylog 管理日志？- 每天5分钟玩转 Docker 容器技术（93）
- vue2 递归组件--树形
- Hadoop HA高可用集群搭建（Hadoop+Zookeeper+HBase）
- mysql原理~创建用户的那些事情
- 初步了解three.js
- Deepin系统安装mysql教程及相关操作
- Ionic 2 中生命周期的命名改变及说明
- Socket网络编程--聊天程序(3)
- HDU - 5785：Interesting （回文树，求相邻双回文的乘积）
- day 51 html 学习 js 学习
- 控制器隐藏了导航 下页pop 导航位置看到黑条
- 【bzoj2005】 [Noi2010]能量采集 数学结论(gcd)
- MySQL InnoDB简介
- python中常用模块详解一