【SQL】時間遞增的兩種方法
經常被問到:如何對現有的日期欄位增加一秒鐘、增加一分鐘、增加一小時、增加一天等。
這裡給出兩種方法,供參考。
1.增加一秒鐘
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+1/24/60/60 from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-14 00:00:01
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+ interval '1' second from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-14 00:00:01
2.增加一分鐘
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+1/24/60 from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-14 00:01:00
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+ interval '1' minute from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-14 00:01:00
3.增加一小時
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+1/24 from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-14 01:00:00
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+ interval '1' hour from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-14 01:00:00
4.增加一天
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+1 from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-15 00:00:00
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+ interval '1' day from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-15 00:00:00
5.小結
只要掌握了日期型別的遞增規律一切都是那樣的自然與和諧。
Good luck.
secooler
10.05.14
-- The End --
這裡給出兩種方法,供參考。
1.增加一秒鐘
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+1/24/60/60 from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-14 00:00:01
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+ interval '1' second from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-14 00:00:01
2.增加一分鐘
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+1/24/60 from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-14 00:01:00
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+ interval '1' minute from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-14 00:01:00
3.增加一小時
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+1/24 from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-14 01:00:00
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+ interval '1' hour from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-14 01:00:00
4.增加一天
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+1 from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-15 00:00:00
sec@ora10g> select to_date('20100514000000','yyyymmddhh24miss'),to_date('20100514000000','yyyymmddhh24miss')+ interval '1' day from dual;
TO_DATE('2010051400 TO_DATE('2010051400
------------------- -------------------
2010-05-14 00:00:00 2010-05-15 00:00:00
5.小結
只要掌握了日期型別的遞增規律一切都是那樣的自然與和諧。
Good luck.
secooler
10.05.14
-- The End --
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/519536/viewspace-662804/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- RAC各節點時間同步的兩種方法
- 計算頁面執行時間的兩種方法
- Qt中使用QLabel顯示時間的兩種方法QT
- RAC時間同步的兩種方式
- PostgreSQL建立自增主鍵的兩種方法SQL
- sql 流水號的遞增SQL
- 時間遞增迴圈執行指令碼指令碼
- Win10系統如何修改睡眠時間 win10修改睡眠時間的兩種方法Win10
- Java遍歷資料夾的兩種方法(非遞迴和遞迴)Java遞迴
- 更改Linux時區的兩種方法Linux
- SQL SERVER日誌清除的兩種方法(轉)SQLServer
- 不同apk間共享資料的兩種方法APK
- Spark SQL中列轉行(UNPIVOT)的兩種方法SparkSQL
- ASP建立SQL Server資料庫的兩種方法SQLServer資料庫
- SQL字串的數字部分遞增SQL字串
- Win10時間線怎麼關閉?關閉Win10時間線功能功能的兩種方法Win10
- RabbitMQ實現延時訊息的兩種方法MQ
- 兩個時間戳的時間差時間戳
- sql取兩個值之間的資料方法(轉)SQL
- Laravel 登入失敗次數限制 等待時間遞增Laravel
- java 傳遞引數的兩種方式Java
- Qt獲取當前時間的兩種方式筆記QT筆記
- Linux時間修改及CMOS時間修改同步的4種方法Linux
- 使用SQL語句去掉重複記錄的兩種方法SQL
- Sql字串分組Split函式的兩種實現方法SQL字串函式
- 修復SQL隱碼攻擊漏洞 兩種方法SQL
- php中計算時間差的幾種方法PHP
- Treeset的兩種排序方法排序
- js 計算兩個時間的時間差JS
- 計算兩段時間的時間差 (轉)
- 透過PHP連線My SQL的兩種方法簡介(轉)PHPSQL
- SQL Server在分頁獲取資料的同時獲取到總記錄數的兩種方法SQLServer
- JavaScript計算兩個時間點之間的時間差JavaScript
- android 中Service 和activity之間的資料傳遞的幾種方法Android
- 兩種遞迴方式實現迴文字遞迴
- vue 跳轉的兩種方法Vue
- 兩種快速打造App的方法APP
- sql server 計算兩個時間 相差的 幾天幾時幾分幾秒SQLServer