postgres常用查询

一.字符串截取

left: 从左往右截取字符
right: 从右往左截取字符

如截取4个字符:

SELECT left( column_name, 4 ) from table
SELECT right( column_name, 4 ) from table

二.条件统计

COUNT(CASE WHEN column_name= ‘value’ THEN 1 END) AS count_name

如截统计值1,值2的数量:

SELECT
COUNT(CASE WHEN column_name= ‘value1’ THEN 1 END) AS count_name1,
COUNT(CASE WHEN column_name= ‘value2’ THEN 1 END) AS count_name2,
COUNT(*) AS total
from table

三.时间转字符

to_char( column_name, ‘YYYY年MM月’ )
如:

select to_char( column_name, ‘YYYY年MM月’ ) from table

四.时间格式化

:Date
时间强制转换,把时间戳转换为日期
如:

select create_time:Date as create_date from table

date_trunc(‘year’, now())
获取本年1月1日0时0分时间
date_trunc(‘month’, now())
获取本月1日0时0分时间
date_trunc(‘day’, now())
获取今天0时0分时间
如查询今年创建的数据:

SELECT * FROM table WHERE create_time >date_trunc(‘year’, now())