CODEWITHSUNDEEP
dateplsql
Tizianoreica
Tizianoreica

Reputation: 2236

Generate all minutes between two times in plsql

So I have this kind of data

DATA COUNT 29/11/2016 22:11:00 120 29/11/2016 22:12:00 132 29/11/2016 22:13:00 551 29/11/2016 22:14:00 1 29/11/2016 22:33:00 1

and I need to obtain a list with all minutes, where if cannot match my join condition on date, it will display 0.

So in short:

I need to generate all minutes between two times in plsql, so I can join this list with my data

Upvotes: 0

Views: 63

Answers (2)

Boneist
Boneist

Reputation: 23578

This should do the trick, and will take into account times which cross days (e.g. 23:50 - 00:10):

WITH boundary_dates AS (SELECT to_date('29/11/2016 23:50:45', 'dd/mm/yyyy hh24:mi:ss') start_date,
                               to_date('30/11/2016 00:10:00', 'dd/mm/yyyy hh24:mi:ss') end_date
                        FROM   dual)
SELECT trunc(start_date, 'mi') + (LEVEL -1)/1440 dt
FROM   boundary_dates
CONNECT BY LEVEL <= (end_date - trunc(start_date, 'mi'))*1440 + 1;

DT
----------------
29/11/2016 23:50
29/11/2016 23:51
29/11/2016 23:52
29/11/2016 23:53
29/11/2016 23:54
29/11/2016 23:55
29/11/2016 23:56
29/11/2016 23:57
29/11/2016 23:58
29/11/2016 23:59
30/11/2016 00:00
30/11/2016 00:01
30/11/2016 00:02
30/11/2016 00:03
30/11/2016 00:04
30/11/2016 00:05
30/11/2016 00:06
30/11/2016 00:07
30/11/2016 00:08
30/11/2016 00:09
30/11/2016 00:10

Then you can simply left join your other table/view/query to this and use nvl/coalesce to output 0 for the "missing" values.

N.B. my example uses a subquery to pass in the start/end dates. Yours would presumably use variables (probably passed in as parameters), so you wouldn't need the boundary_dates subquery.

Upvotes: 1

Tizianoreica
Tizianoreica

Reputation: 2236

Ok.

It took me less than I thought:

SELECT
  (to_date('29-11-2016 20:59:00','DD-MM-YYYY HH24:MI:SS') + level/1440) AS date
FROM
  dual
CONNECT BY LEVEL <= (round((to_date('23:59:59','HH24:MI:SS') - to_date('21:00:00','HH24:MI:SS'))*1440,0));

Trying to explain:

with (round((to_date('23:59:59','HH24:MI:SS') - to_date('21:00:00','HH24:MI:SS'))*1440,0) I calculate different of minutes between two times (so now I get 180 minutes)

With to_date('29-11-2016 20:59:00','DD-MM-YYYY HH24:MI:SS') + level/1440 I sum to my date 29-11-2016 20:59:00 all minutes before calculated by to level/1440

Upvotes: 0

Related Questions