PostGreSQL常用sql

create_date 是一个timestamp的数据;
current_date 是pgsql数据一个获取当前日期的字段；
to_char(timestamp，text)把timestamp数据转换成字符串；
substring(text from int for int) 截取想要的文本格式 ‘yyyy-MM-dd’；
to_timestamp(text,'yyyy-MM-dd')转换成timestamp格式；
age(timestamp,timestamp)获取两个时间之差 返回 days
to_timestamp(dynamic_time,'yyyy-MM-dd') between (SELECT current_timestamp - interval '6 day') and (SELECT current_timestamp - interval '3 day') 查询三天前的数据，从六天前开始查，三天前结束
to_timestamp(dynamic_time,'yyyy-MM-dd') between (SELECT current_timestamp - interval '3 day') 查询最近三天的数据
replace(dynamic_time,substr(a.dynamic_time, 0, 11), to_char(CURRENT_DATE,'yyyy-mm-dd')) as dynamic_time 替换所查数据年月日为当前年月
to_char(last_active,'yyyy-mm-dd hh24:mi:ss') timestamp类型提取相对性的时间属性`

示例1：查询表中当天的数据，表中时间字段（String）类型

SELECT
	dynamic_time 
FROM
	baj_dynamic 
WHERE
	to_timestamp(dynamic_time,
	'yyyy-MM-dd') = CURRENT_DATE ORDER BY dynamic_time DESC LIMIT 1

示例2：统计每个月表中数据，表中时间字段（String）类型

SELECT
	log_year,
	log_month,
	count (id) AS round_count
FROM
	(
		SELECT
			EXTRACT (YEAR FROM to_timestamp(createtdate,'yyyy-MM-dd')) AS log_year,
			EXTRACT (MONTH FROM to_timestamp(createtdate,'yyyy-MM-dd')) AS log_month,
			id
		FROM
			baj_checkimgs
	) AS A
GROUP BY
	log_year,
	log_month
ORDER BY
	log_year,
	log_month

示例2：统计每周表中数据，表中时间字段（String）类型

SELECT
	log_year,
	log_week,
	count (id) AS round_count
FROM
	(
		SELECT
			EXTRACT (YEAR FROM to_timestamp(createtdate,'yyyy-MM-dd')) AS log_year,
			EXTRACT (week FROM to_timestamp(createtdate,'yyyy-MM-dd')) AS log_week,
			id
		FROM
			baj_checkimgs
	) AS A
GROUP BY
	log_year,
	log_week
ORDER BY
	log_year,
	log_week