PostgreSQL/Oracle/Mysql查询最近12个月写法
2021/7/21 19:07:09
本文主要是介绍PostgreSQL/Oracle/Mysql查询最近12个月写法,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
PostgreSQL中查询前12个月的数据,举例: with seq as (select 1 as rownum union all select 2 as rownum union all select 3 as rownum union all select 4 as rownum union all select 5 as rownum union all select 6 as rownum union all select 7 as rownum union all select 8 as rownum union all select 9 as rownum union all select 10 as rownum union all select 11 as rownum union all select 12 as rownum) select cTime.stt_date AXIS,cTime.ABBREVAXIS "abbrevAXIS",ROUND(nvl(a.VALUE1,0)/10000,2) VALUE1,ROUND(nvl(a.VALUE2,0)/10000,2) VALUE2 from ( select to_char(now()::timestamp + (-(rownum-1) || ' months')::interval,'YYYY-MM') as stt_date,to_char(now()::timestamp + (-(rownum-1)|| ' months')::interval,'MM') as ABBREVAXIS from seq ) cTime
--oracle 未来12个月
SELECT TO_CHAR(ADD_MONTHS(ADD_MONTHS(SYSDATE, 0), ROWNUM - 1),
'YYYY-MM') AS YEARMONTH
FROM ALL_OBJECTS
WHERE ROWNUM <= 12;
--oracle 前12个月
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 1 - ROWNUM),
'YYYY-MM') AS YEARMONTH
FROM ALL_OBJECTS
WHERE ROWNUM <= 12;
--mysql 前12个月
SELECT DATE_FORMAT(
date_add(NOW(), interval 1-(@rownum :=@rownum + 1) MONTH),
'%Y-%m') AS YEARMONTH
FROM information_schema.tables t1,(SELECT @rownum := 0) t2
limit 12;
--mysql 未来12个月
SELECT DATE_FORMAT(
date_add(NOW(), interval (@rownum :=@rownum + 1) -1 MONTH),
'%Y-%m') AS YEARMONTH
FROM information_schema.tables t1,(SELECT @rownum := 0) t2
limit 12;
-- 在PostgreSQL中可以直接对时间进行加减运算:SELECT now()::timestamp + '1 year';、
--当前时间加1年
SELECT now()::timestamp + '1 year';
'SELECT create_time + '1 year' from table;
--当前时间加一个月
SELECT now()::timestamp + '1 month';
SELECT create_time + '1month' from table;
--加1年1月1天1时1分1秒
select now()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec';
selectcreate_time + '1 year 1 month 1 day 1 hour 1 min 1 sec';
--把col字段转换成天 然后相加
SELECT now()::timestamp + (col || ' day')::interval FROM table
这篇关于PostgreSQL/Oracle/Mysql查询最近12个月写法的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-12-07MySQL读写分离入门:轻松掌握数据库读写分离技术
- 2024-12-07MySQL读写分离入门教程
- 2024-12-07MySQL分库分表入门详解
- 2024-12-07MySQL分库分表入门指南
- 2024-12-07MySQL慢查询入门:快速掌握性能优化技巧
- 2024-12-07MySQL入门:新手必读的简单教程
- 2024-12-07MySQL入门:从零开始学习MySQL数据库
- 2024-12-07MySQL索引入门:新手快速掌握MySQL索引技巧
- 2024-12-06BinLog学习:MySQL数据库BinLog入门教程
- 2024-12-06Binlog学习:MySQL数据库的日志管理入门教程