时间
获取时间
select current_date;
# 2021-06-18
select current_time;
# 14:59:04.229038+08
select now();
#14:59:19.208956+08
时间的计算
# 时间的计算
# min/year/month/day/hour/sec
select now() + interval'10 min'
# 获取今年的第一天
SELECT date_trunc('year',CURRENT_DATE)
# 获取去年的第一天
select date_trunc('year',now() + '-1 year')
时间格式转换
# to_date 转换为 普通的时间格式
select to_date('2021-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss')
# 2021-03-12
#to_timestamp 转换可为 时间戳格式
select to_timestamp('2021-03-12 18:47:35','yyyy-MM-dd hh24:mi:ss')
# 2021-03-12 18:47:35+08
#to_char 时间转为字符
SELECT to_char(now(),'yyyy-MM-dd hh24:mi:ss')
# 2021-06-18 15:06:40
时间序列
近12个月
SELECT
to_char( b, 'YYYY-MM' ) AS target
FROM
generate_series ( to_timestamp( '2020-01', 'YYYY-MM' ), to_timestamp( to_char( now( ), 'yyyy-MM' ), 'YYYY-MM' ), '1 months' ) AS b
ORDER BY
target DESC
LIMIT 12
近5年
SELECT
to_char( b, 'YYYY' ) AS target
FROM
generate_series ( to_timestamp( '2010', 'YYYY' ), to_timestamp( to_char( now( ), 'yyyy' ), 'YYYY' ), '1 year' ) AS b
ORDER BY
target DESC
LIMIT 5
近30天
SELECT to_char( b, 'YYYY-MM-dd' ) AS target
FROM generate_series ( to_timestamp( '2021-05-01', 'YYYY-MM-dd' ), to_timestamp( to_char( now( ), 'YYYY-MM-dd' ), 'YYYY-MM-dd' ), '1 day' ) AS b
ORDER BY target DESC LIMIT 30
同期
select '2021-06' as ny;
select to_char(to_date('2021-06','YYYY-MM')-interval'1year','YYYY-MM') as lny;
字符串
判断空
select COALESCE ( mtable, 0 )
- 0
数组
select unnest(string_to_array('小米,小亮,小红',',')) as name
数组长度
select array_length(string_to_array('小米,小亮,小红',','),1)
保留2位小数,类型转换
使用CAST或者使用::
,时间不可以,使用to_date,to_char
CAST(XX AS DECIMAL(18,2))
XX::DECIMAL(18,2)
替换replace
SELECT replace('350-180米','米','') AS ls
字符分割函数split_part(text,sep, part)
参数:
- text : 需要分裂的字段
- sep :分割的分割符号,比如“-”、“,”
- part : 选择保存哪部分,填写数字:1、2
SELECT split_part(replace('350-180米','米',''),'-',1) AS ls1
聚合函数列转行
string_agg
SELECT
string_agg ( aa, ',' )
FROM
( SELECT '1' AS aa UNION ALL SELECT '1' AS aa ) AS ffd