一、时间
1. 日期转时间戳（to_unixtime）
to_unixtime
(_timestamp_) → double
Returns
timestamp
as a UNIX timestamp.
返回时间戳转换成UNIX 时间戳格式。
select to_unixtime(cast ('2020-02-03 22:22:22.022' as timestamp))
2. 时间戳转日期时间
2.1 from_unixtime
from_unixtime
(_unixtime_) → timestamp
Returns the UNIX timestamp
unixtime
as a timestamp.
将UNIX时间戳转换成时间戳。
from_unixtime
(_unixtime_, _string_) → timestamp with time zone
Returns the UNIX timestamp
unixtime
as a timestamp with time zone using
string
for the time zone.
将UNIX时间戳返回时间戳+时区格式。
from_unixtime
(_unixtime_, _hours_, _minutes_) → timestamp with time zone
Returns the UNIX timestamp
unixtime
as a timestamp with time zone using
hours
and
minutes
for the time zone offset.
**将UNIX时间戳返回时间戳+小时分钟的时间差。
select from_unixtime(1580739742.022)
select from_unixtime(1580739742.022,'US/Pacific')
select from_unixtime(1580739742.022,8,00)
2.2 format_datetime
format_datetime
(_timestamp_, _format_) → varchar
Formats
timestamp
as a string using
format
. **
将时间戳格式转换成特定形式的字符串。
--时间戳转时间格式（精确到毫秒）--24小时制
select format_datetime(from_unixtime(1.580739742022E9) ,'yyyy-MM-dd HH:mm:ss.mmm')
--时间戳转时间格式（精确到毫秒）--12小时制
select format_datetime(from_unixtime(1.580739742022E9) ,'yyyy-MM-dd hh:mm:ss.mmm')
--时间戳转时间格式（精确到秒）
select format_datetime(from_unixtime(1580739742022/1000) ,'yyyy-MM-dd HH:mm:ss')
3.时间间隔
3.1 date_add
date_add
(_unit_, _value_, _timestamp_) → [same as input]
Adds an interval
value
of type
unit
to
timestamp
. Subtraction can be performed by using a negative value.
加减相应单位的间隔时间 算出新的时间戳，通过加负值的方式实现减法计算
。
--计算对应时间戳减去2小时的时间戳
select date_add('hour', -2, cast ('2020-02-03 22:22:22.022' as timestamp))
3.2 date_diff
用法：返回两个时间戳相差（指定单位的）时间。
date_diff
(_unit_, _timestamp1_, _timestamp2_) → bigint
Returns
timestamp2 - timestamp1
expressed in terms of
unit
.
返回两个时间戳相差（指定单位的）时间。
select date_diff('hour', cast ('2020-02-01 22:22:22' as timestamp) , cast ('2020-02-03 22:22:22' as timestamp))
3.3 presto 时间单位参数表
The functions in this section support the following interval units:
本部分函数支持的时间差单位
|
Unit 单位
|
Description 单位解释
|
millisecond
|
Milliseconds 毫秒
|
second
|
Seconds 秒
|
minute
|
Minutes 分钟
|
hour
|
Hours 小时
|
day
|
Days 天
|
week
|
Weeks 周
|
month
|
Months 月
|
quarter
|
Quarters of a year 季度
|
year
|
Years 年
|
4.时间及日期加减
Date and Time Operators
| Operator | Example | Result |
+
|
date '2012-08-08' + interval '2' day
|
2012-08-10
|
+
|
time '01:00' + interval '3' hour
|
04:00:00.000
|
+
|
timestamp '2012-08-08 01:00' + interval '29' hour
|
2012-08-09 06:00:00.000
|
+
|
timestamp '2012-10-31 01:00' + interval '1' month
|
2012-11-30 01:00:00.000
|
+
|
interval '2' day + interval '3' hour
|
2 03:00:00.000
|
+
|
interval '3' year + interval '5' month
|
3-5
|
-
|
date '2012-08-08' - interval '2' day
|
2012-08-06
|
-
|
time '01:00' - interval '3' hour
|
22:00:00.000
|
-
|
timestamp '2012-08-08 01:00' - interval '29' hour
|
2012-08-06 20:00:00.000
|
-
|
timestamp '2012-10-31 01:00' - interval '1' month
|
2012-09-30 01:00:00.000
|
-
|
interval '2' day - interval '3' hour
|
1 21:00:00.000
|
-
|
interval '3' year - interval '5' month
|
2-7
|
二、字符串
1. 拼接
1.1 concat
concat
(_string1_, _..._, _stringN_) → varchar
Returns the concatenation of
string1
,
string2
,
...
,
stringN
. This function provides the same functionality as the SQL-standard concatenation operator (
||
)
返回 多个字符串的拼接结果，类似SQL里的 || 运算结果。
-- 多字符串拼接
select concat('too',' ','young,',' ','too',' ','simple')
2. 截取
2.1 substr
substr
(_string_, _start_) → varchar
Returns the rest of
string
from the starting position
start
. Positions start with
1
. A negative starting position is interpreted as being relative to the end of the string.
返回对应字符串开始位之后的字符串，负数为字符串末尾倒数的顺序。
substr
(_string_, _start_, _length_) → varchar
Returns a substring from
string
of length
length
from the starting position
start
. Positions start with
1
. A negative starting position is interpreted as being relative to the end of the string.
返回对应字符串开始位开始，对应长度的字符串，负数为字符串末尾倒数的顺序。
-- 截取字符串，第一个参数为目标字符串，第二个参数的是开始位
select substr('Too young, too simple!',11)
-- 截取字符串，第一个参数为目标字符串，第二个参数的是开始位，第三个参数为截取字符长度
select substr('Too young, too simple!',5,5)
3. 替换
3.1 replace
replace
(_string_, _search_) → varchar
Removes all instances of
search
from
string
.
在字符串内删除相应字符。
replace
(_string_, _search_, _replace_) → varchar
Replaces all instances of
search
with
replace
in
string
.
If
search
is an empty string, inserts
replace
in front of every character and at the end of the
string
.
在字符串内替换字符，如果对应字符为空，会在字符串每个字符前面插入替换字符。
-- 删除相应字符
select replace('Too young, too simple!',' ')
-- 替换字符
select replace('Too young, too simple!',' ','-')
4. 分割
4.1 split
split
(_string_, _delimiter) -> array(varchar_)
Splits
string
on
delimiter
and returns an array.
将字符串按照分隔符进行分割并返回array
。
split
(_string_, _delimiter_, _limit) -> array(varchar_)
Splits
string
on
delimiter
and returns an array of size at most
limit
. The last element in the array always contain everything left in the
string
.
limit
must be a positive number.
将字符串按照分隔符进行分割，并按限定长度（小于或者等于）返回array，array最后一个元素包含所有剩余的字符，所有限定长度必须为正数。
-- 分割字符
select split('Too young, too simple!',' ')
-- 按设定长度分割字符
select split('Too young, too simple!',' ',3)
4.2 split_part
split_part
(_string_, _delimiter_, _index_) → varchar
Splits
string
on
delimiter
and returns the field
index
. Field indexes start with
1
. If the index is larger than than the number of fields, then null is returned.
按照分隔符切分字符串，并返回对应索引的子串。
-- 分割字符,并返回对应index的字符串
select split_part('Too young, too simple!',' ',4)
三、正则表达式
1. 包含关系判断
1.1 regexp_like
regexp_like
(_string_, _pattern_) → boolean
Evaluates the regular expression
pattern
and determines if it is contained within
string
.
This function is similar to the
LIKE
operator, except that the pattern only needs to be contained within
string
, rather than needing to match all of
string
. In other words, this performs a
contains
operation rather than a
match
operation. You can match the entire string by anchoring the pattern using
^
and
$
对字符串是否含有对应片段进行判断，返回对应的布尔值。这个函数类似 LIKE 判断，但与之不同的只要包含在字符串中即可，而不是完美匹配字符串。简而言之，这个函数用于检测包含而不是匹配操作。你可以通过^和$符号的限定来匹配整个字符串。
select regexp_like('Too young, too simple!', 'simple')
2. 提取
2.1 regexp_extract
regexp_extract
(_string_, _pattern_) → varchar
Returns the first substring matched by the regular expression
pattern
in
string
:
返回字符串与模式匹配的第一个子字符串
select regexp_extract('too young, too simple!', 'too')
regexp_extract
(_string_, _pattern_, _group_) → varchar
Finds the first occurrence of the regular expression
pattern
in
string
and returns the
capturing group number
group
返回字符串里和模式匹配分组对应的字符串。
select regexp_extract('2020-02-05','(\d{4})-((\d{2})-(\d{2}))',2)
|
组编号
|
捕获组
|
匹配
|
|
0
|
(\d{4})-((\d{2})-(\d{2}))
|
2020-02-05
|
|
1
|
(\d{4})
|
2020
|
|
2
|
((\d{2})-(\d{2}))
|
02-05
|
|
3
|
(\d{2})
|
02
|
|
4
|
(\d{2})
|
05
|
2.2 regexp_extract_all
和 regexp_extract 类似，返回匹配的所有子串。
regexp_extract_all
(_string_, _pattern) -> array(varchar_)
Returns the substring(s) matched by the regular expression
pattern
in
string
:
SELECT regexp_extract_all('1a 2b 14m', 'd+'); -- [1, 2, 14]
regexp_extract_all
(_string_, _pattern_, _group) -> array(varchar_)
Finds all occurrences of the regular expression
pattern
in
string
and returns the
capturing group number
group
:
SELECT regexp_extract_all('1a 2b 14m', '(d+)([a-z]+)', 2); -- ['a', 'b', 'm']
select regexp_extract_all('2020-02-05','-\d{2}') as result
3. 替换
3.1 regexp_replace
regexp_replace
(_string_, _pattern_) → varchar
Removes every instance of the substring matched by the regular expression
pattern
from
string
删除字符串内每一个和模式匹配的子字符串。
regexp_replace
(_string_, _pattern_, _replacement_) → varchar
Replaces every instance of the substring matched by the regular expression
pattern
in
string
with
replacement
.
Capturing groups
can be referenced in
replacement
using
$g
for a numbered group or
${name}
for a named group. A dollar sign (
$
) may be included in the replacement by escaping it with a backslash (
\$
):
替换字符串内和模式匹配的子串。也可以指定分组编号或者分组名称来实现捕获组。
select regexp_replace('2020-02-05','-\d{2}','/01') as result
select regexp_replace('2020-02-05','(\d{4})-((\d{2})-(\d{2}))','$4') as result
4. 分割
4.1 regexp_split
regexp_split
(_string_, _pattern) -> array(varchar_)
Splits
string
using the regular expression
pattern
and returns an array. Trailing empty strings are preserved
按照模式分解字符串，并返回一个数组。
SELECT regexp_split('1a2b3c6f', '[a-z]') as result
