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

Dear 丶 2023-01-10 01:18 101阅读 0赞

参考文章：[SPARK-SQL内置函数之时间日期类][SPARK-SQL]

## 一、获取当前时间 ##

### 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');　　 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

### 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`*

[SPARK-SQL]: https://www.cnblogs.com/qiaoqifa/p/13042006.html