
1.point查询:SELECT id, ST_AsText(the_geom), ST_AsEwkt(the_geom), ST_X(the_geom), ST_Y(the_geom) FROM cities;
2.point插入:INSERT INTO public.run_packinfo(the_geom) VALUES (ST_GeomFromText('POINT(26.074508 119.296494)',4326));
3.数据导入:INSERT into run_gpsinfo(devid,uploadtime,the_geom) select devid,uploadtime,st_geometryfromtext('POINT('||to_char(x,'999D9999')||' '||to_char(y,'999D9999')||')',4326)
from gpsinfo where x>100 LIMIT 50000 OFFSET 0
4.insert into run_packinfo(baledensity,uploadtime) select baledensity,iidspeed,baledatetime from baleinfo;
5.年:select to_char(uploadtime,'yyyy'),count(*) from run_packinfo group by to_char(uploadtime,'yyyy')
6.select to_char(exportDate,‘yyyy-mm’),sum(amount) from table1 group by to_char(exportDate,‘yyyy-mm’)
order by to_char(exportDate,‘yyyy-mm’);
7.select to_char(exportDate,‘yyyy-Q’),sum(amount) from table1 group by to_char(exportDate,‘yyyy-Q’)
order by to_char(exportDate,‘yyyy-Q’);
8.select to_char(exportDate,‘yyyy-IW’),sum(amount) from table1 group by to_char(exportDate,‘yyyy-IW’)
order by to_char(exportDate,‘yyyy-IW’);
今天
select * from 表名 where to_days(时间字段名) = to_days(now());
昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
2.point插入:INSERT INTO public.run_packinfo(the_geom) VALUES (ST_GeomFromText('POINT(26.074508 119.296494)',4326));
3.数据导入:INSERT into run_gpsinfo(devid,uploadtime,the_geom) select devid,uploadtime,st_geometryfromtext('POINT('||to_char(x,'999D9999')||' '||to_char(y,'999D9999')||')',4326)
from gpsinfo where x>100 LIMIT 50000 OFFSET 0
4.insert into run_packinfo(baledensity,uploadtime) select baledensity,iidspeed,baledatetime from baleinfo;
5.年:select to_char(uploadtime,'yyyy'),count(*) from run_packinfo group by to_char(uploadtime,'yyyy')
6.select to_char(exportDate,‘yyyy-mm’),sum(amount) from table1 group by to_char(exportDate,‘yyyy-mm’)
order by to_char(exportDate,‘yyyy-mm’);
7.select to_char(exportDate,‘yyyy-Q’),sum(amount) from table1 group by to_char(exportDate,‘yyyy-Q’)
order by to_char(exportDate,‘yyyy-Q’);
8.select to_char(exportDate,‘yyyy-IW’),sum(amount) from table1 group by to_char(exportDate,‘yyyy-IW’)
order by to_char(exportDate,‘yyyy-IW’);
今天
select * from 表名 where to_days(时间字段名) = to_days(now());
昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
- 上一篇 mybtis多参数传递
查看评论