Oracle查询每天一分钟 [英] Oracle query with every minute a day

问题描述

我想在oracle sql中编写一个查询，该查询在一列中返回当天的每一分钟.但是我不知道如何开始:-(

I want to write a query in oracle sql, which returns every minute of the current day in one column. But i've got no idea, how to start :-(

min
27.03.2014 00:00
27.03.2014 00:01
27.03.2014 00:02
...
27.03.2014 23:59

谢谢

推荐答案

我们可以使用算术来操作日期，并使用简单的CONNECT BY技巧来生成行流.

We can use arithmetic to manipulate dates, and the simple CONNECT BY trick to generate a stream of rows.

alter session set nls_date_format='dd-mon-yyyy hh24:mi'
/

with cte as ( select trunc(sysdate) as start_date from dual )
select start_date + ((level-1)/(24*60)) as date_w_mins
from cte
connect by level <= (24*60)
/

这篇关于Oracle查询每天一分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！