1.时间表示
pg中的LOCALTIMESTAMP(0),相当于oracle中的sysdate
2.时间进行格式转换
2.1 to_Char
to_char(LOCALTIMESTAMP(0), 'YYYY-MM-DD HH24:MI:SS') as detect_time
2.2 to_Date
to_date('2018-03-12 18:47:35','yyyy-MM-dd')
to_date(#{birthdate},'yyyy-MM-dd')
3.时间范围
3.1大于小于
G.created_time > current_date AND G.created_time < LOCALTIMESTAMP(0)
modify_time BETWEEN LOCALTIMESTAMP(0) - interval '1 month' and LOCALTIMESTAMP(0) --前一个月到现在
4.从时间里面取年月日
4.1通过函数获取
SELECT EXTRACT(YEAR FROM moditime) FROM TB(从moditime字段中提取年份)
SELECT EXTRACT(MONTH FROM moditime) FROM TB(从moditime字段中提取月份)
SELECT EXTRACT(DAY FROM moditime) FROM TB(从moditime字段中提取日)
SELECT EXTRACT(HOUR FROM moditime) FROM TB(从moditime字段中提取时)
SELECT EXTRACT(MINUTE FROM moditime) FROM TB(从moditime字段中提取分)
SELECT EXTRACT(SECOND FROM moditime) FROM TB(从moditime字段中提取秒)
4.2直接获取
to_char(modify_time, 'DD')||'日' --表示天