1、日期截取
where trunc( lesson_start_time) = '2022-05-10',后方日期必须加引号,不然无法识别;
select date_trunc('month',getdate()),返回当月第一天;
select salesid, extract(week from saletime) as weeknum 返回第几周;可以返回日、月或年、小时、分钟、秒、毫秒或微秒等;
select date_trunc('week', saletime) DATE_TRUNC 函数使用“周”日期部分返回每周星期一的日期
select date_part(mon,getdate()) 从表达式中提取日期部分值;
select DATE_PART_YEAR('2022-05-12') 返回日期中的年份
TO_DATE 会将以字符串形式表示的日期转换为 DATE 数据类型。
select sysdate,
to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as seconds
日期格式转化:从左到右
12/05/22 07:53 |
2022-05-12 07:53:24 |
日期格式参考
2、日期缩写
3、字符串连接:concat可以使用,也可以替换为||
示例:select tutor_user_id||student_user_id||'hello' from t1;
4、CHAR_LENGTH 函数等同于len函数,length函数
5、select charindex('fish', 'dogfish'); 返回指定字符串在字符串中的位置;
6、select position('dog' in 'fish');返回指定子字符串在字符串中的位置,如果在字符串中未找到子字符串,POSITION 将返回 0:
7、SUBSTRING 函数=substr函数,select substring('caterpillar',6,4);
8、TRANSLATE 函数:对于给定表达式,将指定字符的所有匹配项替换为指定替代项
select translate('mint tea', 'inea', 'osin'); translate ----------- most tin
9、自定义变量的替代方法:创建临时表
with tmp_date as (select '20220511' as date1)
SELECT *
FROM dws_cls_student_trail_lesson_ss_daily
WHERE ds=(select date1 from tmp_date)
AND trunc(lesson_start_time) = CURRENT_DATE
LIMIT 5
10、手动输入变量方法:双大括号
SELECT *
FROM dws_cls_student_trail_lesson_ss_daily
WHERE ds='{{datet}}'
AND trunc(lesson_start_time) = CURRENT_DATE
LIMIT 5
11、日期增减:
select add_months(current_date,-2)
select dateadd(MONTH,-2,current_date)
结果一样;
12、返回星期几
select date_part(weekday,'2022-06-05'),返回对应的星期,其中星期日对应0,星期一到六对应1-6;
星期一至星期日对应1-7的写法:
date_part(weekday,dateadd(day,-1,'2022-06-06'))+1
13、mysql中group_concat函数替代:
mysql:select a, group_concat(b separator ',') from table group by a;
Presto:select a, array_join(array_agg(b), ',') from table group by a
redshift:select a, listagg(b, ',') from table group by a
LISTAGG( [DISTINCT] expression [, 'delimiter' ] ) [ WITHIN GROUP (ORDER BY order_list) ] OVER ( [PARTITION BY partition_expression] )
示例1:
listagg(distinct t3.edition_name,',') within group(order by t3.edition_name asc) over (partition by tut.tutor_user_id) banben
示例2:
listagg(
DISTINCT class_type_desc,','
) within group (order by class_type_id ) over(partition by tutor_user_id,student_user_id) 季度班型,
示例3:
listagg(DISTINCT datediff('minute',lesson_start_time,lesson_end_time),',') within group (order by datediff('minute',lesson_start_time,lesson_end_time) ) over(partition by tutor_user_id,student_user_id) 课时长,
14、年周判断:
SELECT
to_char(dateadd(day,-3,next_day('2022-01-03','Monday')),'YYYY') yyr,
extract(week from timestamp ' 2022-01-03') wk
若本身为时间格式,则不需要timestamp
SELECT
to_char(dateadd(day,-3,next_day(lesson_start_time,'Monday')),'YYYY')*100+
extract(week from lesson_start_time)
返回某年某周的6位数字
15、时间戳转化
函数 |
解释 |
语法 |
返回值 |
---|---|---|---|
TIMEZONE('timezone'{timestamp|timestamptz}) |
返回指定时区和时间戳值的一个时间戳。 |
TIMEZONE('UTC+8', (timestamp 'epoch' + 1650347023 * interval '1 second')) |
timestamp |
CONVERT_TIMEZONE (['timezone',] 'timezone', timestamp) |
将一个时区的时间戳转换为另一个时区的时间戳。 |
convert_timezone('GMT+8','UTC','2022-04-19 05:43:43') |convert_timezone('UTC-8','2022-04-19 05:43:43') |
timestamp |
DATEADD (datepart, interval,{date|time|timetz|timestamp}) |
按指定的时间间隔递增日期或时间。 |
dateadd(day,30,'2022-04-30') |
TIMESTAMP |
DATEDIFF ( datepart, {date|time|timetz|timestamp}, {date|time|time|timestamp} ) |
返回两个日期或时间之间的差值。 |
select datediff(day, '2022-04-30', '2022-04-30'); |
TIMESTAMP |
TO_TIMESTAMP ('timestamp', 'format') |
将 TIMESTAMP 字符串转换为 TIMESTAMPTZ |
to_timestamp('2022-04-19 05:43:43','YYYY-MM-DD HH24:MI:SS'); |
TIMESTAMPTZ |
json函数嵌套处理13位时间戳为日期格式:
JSON_EXTRACT_PATH_TEXT(info,'adjustDetailInfoList') 调前virtual_class_id,
JSON_EXTRACT_PATH_TEXT(info,'repairStartTime') 调后时间,
JSON_EXTRACT_PATH_TEXT(btrim(btrim(JSON_EXTRACT_PATH_TEXT(info,'adjustDetailInfoList'),'['),']'),'adjustVirtualclassId') tiaoqianvir,
JSON_EXTRACT_PATH_TEXT(btrim(btrim(JSON_EXTRACT_PATH_TEXT(info,'adjustDetailInfoList'),'['),']'),'adjustStartTime') tiaoqiantime,
dateadd(s , CAST(JSON_EXTRACT_PATH_TEXT(btrim(btrim(JSON_EXTRACT_PATH_TEXT(info,'adjustDetailInfoList'),'['),']'),'adjustStartTime') / 1000 AS INTEGER), '1970-01-01 00:00:00') datetime1 ,
date(dateadd(s , CAST(JSON_EXTRACT_PATH_TEXT(btrim(btrim(JSON_EXTRACT_PATH_TEXT(info,'adjustDetailInfoList'),'['),']'),'adjustStartTime') / 1000 AS INTEGER), '1970-01-01 00:00:00')) date1,
TIMESTAMP 'epoch' + JSON_EXTRACT_PATH_TEXT(btrim(btrim(JSON_EXTRACT_PATH_TEXT(info,'adjustDetailInfoList'),'['),']'),'adjustStartTime')/1000*interval'1second' time2
以上提供两种方法转化
16、最后一个值last_value函数
示例:
last_value(class_type_desc) over(partition by tutor_user_id,student_user_id order by lesson_start_time ASC rows between unbounded preceding and unbounded following ) 最后一课班型,
last_value(datediff('minute',lesson_start_time,lesson_end_time)) over(partition by tutor_user_id,student_user_id order by lesson_start_time ASC rows between unbounded preceding and unbounded following ) 最后一课时长,
last_value(lesson_start_time) over(partition by tutor_user_id,student_user_id order by lesson_start_time ASC rows between unbounded preceding and unbounded following ) 最后一课时间
未完待续。。。