SPARK-SQL内置时间日期类函数

最新推荐文章于 2025-03-26 06:55:38 发布

扫地增

最新推荐文章于 2025-03-26 06:55:38 发布

阅读量2.1k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： spark 文章标签： spark

本文链接：https://blog.csdn.net/qq_41018861/article/details/108738349

spark 专栏收录该内容

10 篇文章

订阅专栏

本文介绍了如何使用SQL进行日期和时间的操作，包括获取当前时间、从日期时间中提取字段、日期时间转换及计算等关键功能。通过具体示例展示了如何应用这些函数来处理日期和时间数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、获取当前时间

1.current_date获取当前日期

SELECT current_date();
2018-04-09

2.current_timestamp/now()获取当前时间

SELECT current_timestamp();
SELECT 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');　　
 1460041200

2.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:00

4.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-20

4.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-31
SELECT date_sub('2016-07-30', 1);　　
2016-07-29

5.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