PostgreSQL 实现按月按年,按日统计 分组统计

endtime 是 timestamp

select to_char(endtime, 'yyyy-MM-dd') as d ,  count(objectid) 
FROM sde.polygon where endtime>='2004-09-01 00:00:00' and endtime <='2004-10-01 23:59:59' group by d

endtime 是 字符串时

select to_char(to_timestamp(endtime::bigint), 'YYYY-MM-DD') as d ,  count(objectid) 
FROM sde.polygon where endtime>='2004-09-01 00:00:00' and endtime <='2004-10-01 23:59:59' group by d

 按时统计时,显示24小时 

COALESCE设置默认值
select to_char(time, 'yyyy-MM-dd HH24') as d, COALESCE(max(rainfall), 0) as m FROM mis.table_site_rainfall_detail where time>'"+startTime+"' and time <='"+endTime+"' group by d ORDER BY d

YYYY-MM-DD HH24:MI:SS
yyyy-mm-dd HH24:MI:ss


mysql日期格式化
sql.append(" and DATE_FORMAT(create_date,'%Y-%m-%d %H:%i:%s') >='" + startTime + "'");


posted on 2019-08-22 09:12  james-roger  阅读(4528)  评论(0编辑  收藏  举报