SQL ORACLE每10行向sysdate添加1秒 [英] SQL ORACLE add 1 second to sysdate every 10 rows
本文介绍了SQL ORACLE每10行向sysdate添加1秒的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在动态SQL中,我想每10行添加1秒
In dynamic SQL, I'd like to add 1 second every 10 rows
以下查询无效,显示时间间隔无效"
Following query didn't work, got "the interval is invalid"
select
rownum
,(round(rownum/10,0)+1)
,sysdate + interval '(round(rownum/10,0)+1)' SECOND
from anytable_with_lots_of_rows
where rownum < 100;
有人吗?谢谢!
推荐答案
经典方法是用一天中的秒数除以,例如
Classic way is the division by the number of seconds in a day, e.g.
with rn as (
select rownum-1 id from dual connect by level <= 100),
rn2 as (select
id, trunc(id/10) tr_id from rn)
select
id, tr_id,
sysdate + tr_id / (24*3600) my_date
from rn2;
给予
ID TR_ID MY_DATE
---------- ---------- -------------------
0 0 28-06-2018 19:05:34
1 0 28-06-2018 19:05:34
2 0 28-06-2018 19:05:34
3 0 28-06-2018 19:05:34
4 0 28-06-2018 19:05:34
5 0 28-06-2018 19:05:34
6 0 28-06-2018 19:05:34
7 0 28-06-2018 19:05:34
8 0 28-06-2018 19:05:34
9 0 28-06-2018 19:05:34
10 1 28-06-2018 19:05:35
11 1 28-06-2018 19:05:35
12 1 28-06-2018 19:05:35
13 1 28-06-2018 19:05:35
14 1 28-06-2018 19:05:35
15 1 28-06-2018 19:05:35
16 1 28-06-2018 19:05:35
17 1 28-06-2018 19:05:35
18 1 28-06-2018 19:05:35
Alternatively, if you want to use intervals - use the function NUMTODSINTERVAL
并用以下表达式替换除法
and replace the division with the following expression
sysdate + NUMTODSINTERVAL(tr_id,'SECOND') my_date
这篇关于SQL ORACLE每10行向sysdate添加1秒的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文