You Asked

select dpd.position_data_date,
dpd.creation_date,
axle_weight_voltage,
axle_weight,
dpd.device_id,
dpd.vehicle_id
-- ,dpd.*
from DEVICE_POSITION_DATA dpd
where dpd.device_id = 795
and dpd.creation_date > sysdate - 1
and nvl(dpd.axle_weight_voltage,0) > 0
order by dpd.creation_date desc

note :- when i am using " dpd.creation_date > systimestamp - numtodsinterval(15,'MINUTE') " no rows are returning

data Sample
===============================

POSITION_DATA_DATE CREATION_DATE AXLE_WEIGHT_VOLTAGE AXLE_WEIGHT DEVICE_ID VEHICLE_ID
1 3/6/2016 3:23:12 PM 3/6/2016 3:04:12 PM 2848.0000000000 13.5897435897 795 875
2 3/6/2016 3:22:42 PM 3/6/2016 3:03:54 PM 2833.0000000000 13.6498397436 795 875
3 3/6/2016 3:22:12 PM 3/6/2016 3:03:12 PM 2848.0000000000 13.5897435897 795 875
4 3/6/2016 3:21:42 PM 3/6/2016 3:02:48 PM 2848.0000000000 13.5897435897 795 875
5 3/6/2016 3:21:12 PM 3/6/2016 3:02:13 PM 2840.0000000000 13.6217948718 795 875
6 3/6/2016 3:20:42 PM 3/6/2016 3:01:43 PM 2825.0000000000 13.6818910256 795 875
7 3/6/2016 3:20:12 PM 3/6/2016 3:01:12 PM 2855.0000000000 13.5616987179 795 875
8 3/6/2016 3:19:42 PM 3/6/2016 3:00:43 PM 2848.0000000000 13.5897435897 795 875
9 3/6/2016 3:19:12 PM 3/6/2016 3:00:13 PM 2871.0000000000 13.4975961538 795 875
10 3/6/2016 3:18:42 PM 3/6/2016 2:59:42 PM 2878.0000000000 13.4695512821 795 875
11 3/6/2016 3:18:12 PM 3/6/2016 2:59:12 PM 2871.0000000000 13.4975961538 795 875
12 3/6/2016 3:17:42 PM 3/6/2016 2:58:42 PM 2886.0000000000 13.4375000000 795 875
13 3/6/2016 3:17:12 PM 3/6/2016 2:58:12 PM 2886.0000000000 13.4375000000 795 875
14 3/6/2016 3:16:42 PM 3/6/2016 2:57:42 PM 2878.0000000000 13.4695512821 795 875

and Connor said...

SQL> create table DEVICE_POSITION_DATA ( id int, other_date timestamp, creation_date timestamp, x number, y number, device_id int, z int  );


Table created.

SQL>
SQL> alter session set nls_date_format = 'MM/DD/YYYY HH:MI:SS AM';

Session altered.

SQL> alter session set nls_timestamp_format = 'MM/DD/YYYY HH:MI:SS AM';

Session altered.

SQL>
SQL> insert into DEVICE_POSITION_DATA values (1, '3/7/2016 3:23:12 PM', '3/7/2016 3:04:12 PM', 2848.0000000000, 13.5897435897 ,795, 875);

1 row created.

SQL> insert into DEVICE_POSITION_DATA values (2, '3/7/2016 3:22:42 PM', '3/7/2016 3:03:54 PM', 2833.0000000000, 13.6498397436 ,795, 875);

1 row created.

SQL> insert into DEVICE_POSITION_DATA values (3, '3/7/2016 3:22:12 PM', '3/7/2016 3:03:12 PM', 2848.0000000000, 13.5897435897 ,795, 875);

1 row created.

SQL> insert into DEVICE_POSITION_DATA values (4, '3/7/2016 3:21:42 PM', '3/7/2016 3:02:48 PM', 2848.0000000000, 13.5897435897 ,795, 875);

1 row created.

SQL> insert into DEVICE_POSITION_DATA values (5, '3/7/2016 3:21:12 PM', '3/7/2016 3:02:13 PM', 2840.0000000000, 13.6217948718 ,795, 875);

1 row created.

SQL> insert into DEVICE_POSITION_DATA values (6, '3/7/2016 3:20:42 PM', '3/7/2016 3:01:43 PM', 2825.0000000000, 13.6818910256 ,795, 875);

1 row created.

SQL> insert into DEVICE_POSITION_DATA values (7, '3/7/2016 3:20:12 PM', '3/7/2016 3:01:12 PM', 2855.0000000000, 13.5616987179 ,795, 875);

1 row created.

SQL> insert into DEVICE_POSITION_DATA values (8, '3/7/2016 3:19:42 PM', '3/7/2016 3:00:43 PM', 2848.0000000000, 13.5897435897 ,795, 875);

1 row created.

SQL> insert into DEVICE_POSITION_DATA values (9, '3/7/2016 3:19:12 PM', '3/7/2016 3:00:13 PM', 2871.0000000000, 13.4975961538 ,795, 875);

1 row created.

SQL> insert into DEVICE_POSITION_DATA values (10, '3/7/2016 3:18:42 PM', '3/7/2016 2:59:42 PM', 2878.0000000000, 13.4695512821 ,795, 875);

1 row created.

SQL> insert into DEVICE_POSITION_DATA values (11, '3/7/2016 3:18:12 PM', '3/7/2016 2:59:12 PM', 2871.0000000000, 13.4975961538 ,795, 875);

1 row created.

SQL> insert into DEVICE_POSITION_DATA values (12, '3/7/2016 3:17:42 PM', '3/7/2016 2:58:42 PM', 2886.0000000000, 13.4375000000 ,795, 875);

1 row created.

SQL> insert into DEVICE_POSITION_DATA values (13, '3/7/2016 3:17:12 PM', '3/7/2016 2:58:12 PM', 2886.0000000000, 13.4375000000 ,795, 875);

1 row created.

SQL> insert into DEVICE_POSITION_DATA values (14, '3/7/2016 3:16:42 PM', '3/7/2016 2:57:42 PM', 2878.0000000000, 13.4695512821 ,795, 875 );

1 row created.


SQL> with
  2  row_every_5_mins as
  3  ( select trunc(sysdate) + (rownum-1)*5/1440 t_from,
  4           trunc(sysdate) + rownum*5/1440 t_to
  5    from dual
  6    connect by level <= 1440/5
  7  )
  8  select r.t_From, d.device_id, d.creation_date
  9  from   row_every_5_mins r,
 10         DEVICE_POSITION_DATA d
 11  where  d.device_id(+) = 795
 12  and    d.creation_date(+) >= r.t_From
 13  and    d.creation_date(+) <  r.t_to
 14  order by t_From;

T_FROM                              DEVICE_ID CREATION_DATE
---------------------------------- ---------- ----------------------------------
03/07/2016 12:00:00 AM
03/07/2016 12:05:00 AM
03/07/2016 12:10:00 AM
03/07/2016 12:15:00 AM
03/07/2016 12:20:00 AM
03/07/2016 12:25:00 AM
03/07/2016 12:30:00 AM
03/07/2016 12:35:00 AM
03/07/2016 12:40:00 AM
03/07/2016 12:45:00 AM
03/07/2016 12:50:00 AM
03/07/2016 12:55:00 AM
03/07/2016 01:00:00 AM
03/07/2016 01:05:00 AM
03/07/2016 01:10:00 AM
03/07/2016 01:15:00 AM
03/07/2016 01:20:00 AM
03/07/2016 01:25:00 AM
03/07/2016 01:30:00 AM
03/07/2016 01:35:00 AM
03/07/2016 01:40:00 AM
03/07/2016 01:45:00 AM
03/07/2016 01:50:00 AM
03/07/2016 01:55:00 AM
03/07/2016 02:00:00 AM
03/07/2016 02:05:00 AM
03/07/2016 02:10:00 AM
03/07/2016 02:15:00 AM
03/07/2016 02:20:00 AM
03/07/2016 02:25:00 AM
03/07/2016 02:30:00 AM
03/07/2016 02:35:00 AM
03/07/2016 02:40:00 AM
03/07/2016 02:45:00 AM
03/07/2016 02:50:00 AM
03/07/2016 02:55:00 AM
03/07/2016 03:00:00 AM
03/07/2016 03:05:00 AM
03/07/2016 03:10:00 AM
03/07/2016 03:15:00 AM
03/07/2016 03:20:00 AM
03/07/2016 03:25:00 AM
03/07/2016 03:30:00 AM
03/07/2016 03:35:00 AM
03/07/2016 03:40:00 AM
03/07/2016 03:45:00 AM
03/07/2016 03:50:00 AM
03/07/2016 03:55:00 AM
03/07/2016 04:00:00 AM
03/07/2016 04:05:00 AM
03/07/2016 04:10:00 AM
03/07/2016 04:15:00 AM
03/07/2016 04:20:00 AM
03/07/2016 04:25:00 AM
03/07/2016 04:30:00 AM
03/07/2016 04:35:00 AM
03/07/2016 04:40:00 AM
03/07/2016 04:45:00 AM
03/07/2016 04:50:00 AM
03/07/2016 04:55:00 AM
03/07/2016 05:00:00 AM
03/07/2016 05:05:00 AM
03/07/2016 05:10:00 AM
03/07/2016 05:15:00 AM
03/07/2016 05:20:00 AM
03/07/2016 05:25:00 AM
03/07/2016 05:30:00 AM
03/07/2016 05:35:00 AM
03/07/2016 05:40:00 AM
03/07/2016 05:45:00 AM
03/07/2016 05:50:00 AM
03/07/2016 05:55:00 AM
03/07/2016 06:00:00 AM
03/07/2016 06:05:00 AM
03/07/2016 06:10:00 AM
03/07/2016 06:15:00 AM
03/07/2016 06:20:00 AM
03/07/2016 06:25:00 AM
03/07/2016 06:30:00 AM
03/07/2016 06:35:00 AM
03/07/2016 06:40:00 AM
03/07/2016 06:45:00 AM
03/07/2016 06:50:00 AM
03/07/2016 06:55:00 AM
03/07/2016 07:00:00 AM
03/07/2016 07:05:00 AM
03/07/2016 07:10:00 AM
03/07/2016 07:15:00 AM
03/07/2016 07:20:00 AM
03/07/2016 07:25:00 AM
03/07/2016 07:30:00 AM
03/07/2016 07:35:00 AM
03/07/2016 07:40:00 AM
03/07/2016 07:45:00 AM
03/07/2016 07:50:00 AM
03/07/2016 07:55:00 AM
03/07/2016 08:00:00 AM
03/07/2016 08:05:00 AM
03/07/2016 08:10:00 AM
03/07/2016 08:15:00 AM
03/07/2016 08:20:00 AM
03/07/2016 08:25:00 AM
03/07/2016 08:30:00 AM
03/07/2016 08:35:00 AM
03/07/2016 08:40:00 AM
03/07/2016 08:45:00 AM
03/07/2016 08:50:00 AM
03/07/2016 08:55:00 AM
03/07/2016 09:00:00 AM
03/07/2016 09:05:00 AM
03/07/2016 09:10:00 AM
03/07/2016 09:15:00 AM
03/07/2016 09:20:00 AM
03/07/2016 09:25:00 AM
03/07/2016 09:30:00 AM
03/07/2016 09:35:00 AM
03/07/2016 09:40:00 AM
03/07/2016 09:45:00 AM
03/07/2016 09:50:00 AM
03/07/2016 09:55:00 AM
03/07/2016 10:00:00 AM
03/07/2016 10:05:00 AM
03/07/2016 10:10:00 AM
03/07/2016 10:15:00 AM
03/07/2016 10:20:00 AM
03/07/2016 10:25:00 AM
03/07/2016 10:30:00 AM
03/07/2016 10:35:00 AM
03/07/2016 10:40:00 AM
03/07/2016 10:45:00 AM
03/07/2016 10:50:00 AM
03/07/2016 10:55:00 AM
03/07/2016 11:00:00 AM
03/07/2016 11:05:00 AM
03/07/2016 11:10:00 AM
03/07/2016 11:15:00 AM
03/07/2016 11:20:00 AM
03/07/2016 11:25:00 AM
03/07/2016 11:30:00 AM
03/07/2016 11:35:00 AM
03/07/2016 11:40:00 AM
03/07/2016 11:45:00 AM
03/07/2016 11:50:00 AM
03/07/2016 11:55:00 AM
03/07/2016 12:00:00 PM
03/07/2016 12:05:00 PM
03/07/2016 12:10:00 PM
03/07/2016 12:15:00 PM
03/07/2016 12:20:00 PM
03/07/2016 12:25:00 PM
03/07/2016 12:30:00 PM
03/07/2016 12:35:00 PM
03/07/2016 12:40:00 PM
03/07/2016 12:45:00 PM
03/07/2016 12:50:00 PM
03/07/2016 12:55:00 PM
03/07/2016 01:00:00 PM
03/07/2016 01:05:00 PM
03/07/2016 01:10:00 PM
03/07/2016 01:15:00 PM
03/07/2016 01:20:00 PM
03/07/2016 01:25:00 PM
03/07/2016 01:30:00 PM
03/07/2016 01:35:00 PM
03/07/2016 01:40:00 PM
03/07/2016 01:45:00 PM
03/07/2016 01:50:00 PM
03/07/2016 01:55:00 PM
03/07/2016 02:00:00 PM
03/07/2016 02:05:00 PM
03/07/2016 02:10:00 PM
03/07/2016 02:15:00 PM
03/07/2016 02:20:00 PM
03/07/2016 02:25:00 PM
03/07/2016 02:30:00 PM
03/07/2016 02:35:00 PM
03/07/2016 02:40:00 PM
03/07/2016 02:45:00 PM
03/07/2016 02:50:00 PM
03/07/2016 02:55:00 PM                    795 03/07/2016 02:59:42 PM
03/07/2016 02:55:00 PM                    795 03/07/2016 02:59:12 PM
03/07/2016 02:55:00 PM                    795 03/07/2016 02:58:42 PM
03/07/2016 02:55:00 PM                    795 03/07/2016 02:58:12 PM
03/07/2016 02:55:00 PM                    795 03/07/2016 02:57:42 PM
03/07/2016 03:00:00 PM                    795 03/07/2016 03:04:12 PM
03/07/2016 03:00:00 PM                    795 03/07/2016 03:03:54 PM
03/07/2016 03:00:00 PM                    795 03/07/2016 03:03:12 PM
03/07/2016 03:00:00 PM                    795 03/07/2016 03:02:48 PM
03/07/2016 03:00:00 PM                    795 03/07/2016 03:00:13 PM
03/07/2016 03:00:00 PM                    795 03/07/2016 03:01:43 PM
03/07/2016 03:00:00 PM                    795 03/07/2016 03:01:12 PM
03/07/2016 03:00:00 PM                    795 03/07/2016 03:00:43 PM
03/07/2016 03:00:00 PM                    795 03/07/2016 03:02:13 PM
03/07/2016 03:05:00 PM
03/07/2016 03:10:00 PM
03/07/2016 03:15:00 PM
03/07/2016 03:20:00 PM
03/07/2016 03:25:00 PM
03/07/2016 03:30:00 PM
03/07/2016 03:35:00 PM
03/07/2016 03:40:00 PM
03/07/2016 03:45:00 PM
03/07/2016 03:50:00 PM
03/07/2016 03:55:00 PM
03/07/2016 04:00:00 PM
03/07/2016 04:05:00 PM
03/07/2016 04:10:00 PM
03/07/2016 04:15:00 PM
03/07/2016 04:20:00 PM
03/07/2016 04:25:00 PM
03/07/2016 04:30:00 PM
03/07/2016 04:35:00 PM
03/07/2016 04:40:00 PM
03/07/2016 04:45:00 PM
03/07/2016 04:50:00 PM
03/07/2016 04:55:00 PM
03/07/2016 05:00:00 PM
03/07/2016 05:05:00 PM
03/07/2016 05:10:00 PM
03/07/2016 05:15:00 PM
03/07/2016 05:20:00 PM
03/07/2016 05:25:00 PM
03/07/2016 05:30:00 PM
03/07/2016 05:35:00 PM
03/07/2016 05:40:00 PM
03/07/2016 05:45:00 PM
03/07/2016 05:50:00 PM
03/07/2016 05:55:00 PM
03/07/2016 06:00:00 PM
03/07/2016 06:05:00 PM
03/07/2016 06:10:00 PM
03/07/2016 06:15:00 PM
03/07/2016 06:20:00 PM
03/07/2016 06:25:00 PM
03/07/2016 06:30:00 PM
03/07/2016 06:35:00 PM
03/07/2016 06:40:00 PM
03/07/2016 06:45:00 PM
03/07/2016 06:50:00 PM
03/07/2016 06:55:00 PM
03/07/2016 07:00:00 PM
03/07/2016 07:05:00 PM
03/07/2016 07:10:00 PM
03/07/2016 07:15:00 PM
03/07/2016 07:20:00 PM
03/07/2016 07:25:00 PM
03/07/2016 07:30:00 PM
03/07/2016 07:35:00 PM
03/07/2016 07:40:00 PM
03/07/2016 07:45:00 PM
03/07/2016 07:50:00 PM
03/07/2016 07:55:00 PM
03/07/2016 08:00:00 PM
03/07/2016 08:05:00 PM
03/07/2016 08:10:00 PM
03/07/2016 08:15:00 PM
03/07/2016 08:20:00 PM
03/07/2016 08:25:00 PM
03/07/2016 08:30:00 PM
03/07/2016 08:35:00 PM
03/07/2016 08:40:00 PM
03/07/2016 08:45:00 PM
03/07/2016 08:50:00 PM
03/07/2016 08:55:00 PM
03/07/2016 09:00:00 PM
03/07/2016 09:05:00 PM
03/07/2016 09:10:00 PM
03/07/2016 09:15:00 PM
03/07/2016 09:20:00 PM
03/07/2016 09:25:00 PM
03/07/2016 09:30:00 PM
03/07/2016 09:35:00 PM
03/07/2016 09:40:00 PM
03/07/2016 09:45:00 PM
03/07/2016 09:50:00 PM
03/07/2016 09:55:00 PM
03/07/2016 10:00:00 PM
03/07/2016 10:05:00 PM
03/07/2016 10:10:00 PM
03/07/2016 10:15:00 PM
03/07/2016 10:20:00 PM
03/07/2016 10:25:00 PM
03/07/2016 10:30:00 PM
03/07/2016 10:35:00 PM
03/07/2016 10:40:00 PM
03/07/2016 10:45:00 PM
03/07/2016 10:50:00 PM
03/07/2016 10:55:00 PM
03/07/2016 11:00:00 PM
03/07/2016 11:05:00 PM
03/07/2016 11:10:00 PM
03/07/2016 11:15:00 PM
03/07/2016 11:20:00 PM
03/07/2016 11:25:00 PM
03/07/2016 11:30:00 PM
03/07/2016 11:35:00 PM
03/07/2016 11:40:00 PM
03/07/2016 11:45:00 PM
03/07/2016 11:50:00 PM
03/07/2016 11:55:00 PM

300 rows selected.

SQL>
SQL>

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Other alternatives

Rajeshwaran, Jeyabal, March 07, 2016 - 10:16 am UTC

Partitioned Outer joins

rajesh@ORA11G> with datas as
  2  ( select trunc(sysdate) + (level-1)*5/ (24*60) as from_dt,
  3           trunc(sysdate) + (level)*5/ (24*60) as thru_dt
  4    from dual
  5    connect by level <= 24*12 )
  6  select *
  7  from device_position_data t1 partition by (device_id)
  8        right outer join datas t2
  9        on (t1.creation_date >= t2.from_dt and
 10            t1.creation_date < t2.thru_dt )
 11  /

.........

300 rows selected.

rajesh@ORA11G>

Model could be the other alternatives too.

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Other alternatives

More to Explore

Live SQL

Dev Gym

need query for One day data with every five minutes timestamp gap. Records in the table exists for every 30 seconds.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Other alternatives

More to Explore

Live SQL

Dev Gym