sql日期转【pd读取csv文件踩坑】读取csv文件时报错：UnicodeDecodeError: ‘utf-8‘ codec can‘t decode byte 0xb5 in position 0

【pd读取csv文件踩坑】读取csv文件时报错：UnicodeDecodeError: ‘utf-8‘ codec can‘t decode byte 0xb5 in position 0

select trunc('2020-08-10 10:43:32' ,'yyyy-MM-dd hh24:mi:ss'  )

1.当前时间
select CURRENT_TIMESTAMP
2020-08-10 10:41:22.943473+08
select CURRENT_TIME
10:41:36.494312+08
select CURRENT_DATE
2020-08-10
select now()
2020-08-10 11:49:24.7987+08
 我们还可以控制now()的返回格式，如下
 select now()::timestamp(0)without time zone;  （current_timestamp 是和now（）一样的）
 2020-08-10 11:50:31

2.to_char()  时间类型 
select to_char(CURRENT_TIMESTAMP,'yyyy-mm-dd hh24:mi:ss')
2020-08-10 10:43:32
select to_char(CURRENT_TIMESTAMP,'yyyymmdd')
20200810
select to_char(CURRENT_TIMESTAMP,'yyyymm')
202008


3.to_date    字符类型 
select to_date('2020-08-10 10:43:32' ,'yyyy-MM-dd hh24:mi:ss')
2020-08-10
select to_date('2020-08-10 10:43:32' ,'yyyy')
2020-01-01

3.字符串转时间类型
select '2020-08-09 00:00:00' :: TIMESTAMP ;
2020-08-09 00:00:00
select cast( '2020-08-09 00:00:00' as  TIMESTAMP  )
2020-08-09 00:00:00


4.date_trunc()  日期精确到本周、本月、本季度第一天
分钟
select date_trunc('min',now()) 
2020-08-10 11:52:00+08
小时
select date_trunc('hour',now());    
2020-08-10 11:00:00+08
年、月、日
select date_trunc('year',now());
2020-01-01 00:00:00+08
select date_trunc('month',now());
2020-08-01 00:00:00+08
select date_trunc('day',now());
2020-08-10 00:00:00+08
本季度第一天
 select date_trunc('quarter',now())
 2020-07-01 00:00:00+08
本周周一
select date_trunc('week','2020-08-09 00:00:00' :: TIMESTAMP);
2020-08-03 00:00:00

5.时间的计算方式，如下
 select now() + interval '10 min/year/month/day/hour/sec/ (1 year 1 month 1 day 1 hour 1 min 1 sec)' 
 //本季度的第15天,15小时 15分 30秒    
 select date_trunc('quarter',now()) + interval '15 d 15 h 15 minute 30 second';        
 2020-07-16 15:15:30+08
 //每个季度最后一天的晚上11点        
select date_trunc('quarter',now() + interval '3 month') - interval '1 h';
2020-09-30 23:00:00+08
1. 日期  往前推多少天

日期往前推6个月  跨年问题
occur_period =  to_char(to_timestamp(concat(left(p_occur_period,4),'-',right(p_occur_period,2)),'yyyy-MM') -interval'6 month' ,'yyyymm')

TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。如果Num_digits为正数，则截取小数点后Num_digits位；如果为负数，则先保留整数部分，然后从个位开始向前数，并将遇到的数字都变为0。
TRUNC()函数在截取时不进行四舍五入，直接截取。
针对数字的案例，如：
select trunc(123.458) from dual 
select trunc(123.458,0) from dual 
select trunc(123.458,1) from dual 
select trunc(123.458,-1) from dual 
select trunc(123.458,-4) from dual 
select trunc(123.458,4) from dual 
select trunc(123) from dual 
select trunc(123,1) from dual 
select trunc(123,-1) from dual 
 
针对日期的案例，如：
select trunc(sysdate) from dual 
select trunc(sysdate,'yyyy') from dual   
select trunc(sysdate,'mm') from dual  
select trunc(sysdate,'d') from dual  
select trunc(sysdate,'dd') from dual  
select trunc(sysdate,'hh') from dual  
select trunc(sysdate,'mi') from dual  
 

2.日期格式化

select to_char(CURRENT_TIMESTAMP,'yyyy-mm-dd hh24:mi:ss')

select to_char(CURRENT_TIMESTAMP,'yyyy-mm-dd hh24:mi:ss')

 

select to_char(cast('2020-02-01 15:37:05' as timestamp), 'yyyymmdd') 
先把字符串类型转化为时间戳类型
to_char(cast(create_time as timestamp), 'yyyymmdd') =to_char(current_timestamp, 'yyyymmdd')

 

 

3.生成一段连续时间段内所有的数据期

generate_series，产生连续数字、步长值

select TO_CHAR(generate_series,'yyyymmdd') as occur_period
from generate_series('20200201'::TIMESTAMP, to_char(CURRENT_TIMESTAMP,'yyyymmdd') ::TIMESTAMP, '1 day')


select to_char(generate_series(to_date('20130403','yyyymmdd'), to_date('20130404','yyyymmdd'), '1 hours'), 'hh24');

 

4.查询本周周一的日期

select to_char(date_trunc('week',to_date(to_char(current_date, 'YYYYww')+1, 'YYYYww')),'YYYYMMDD') as monday_date,current_date;