Skip to Main Content
  • Questions
  • How to group timestamps in 10 minute buckets and aggregate

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Emily.

Asked: July 13, 2017 - 5:34 pm UTC

Last updated: August 07, 2017 - 3:23 am UTC

Version: Oracle 11g

Viewed 10K+ times! This question is

You Asked

I've been grappling with this problem for almost a week. Other threads on this site have gotten me close but not all the way.

I created a table and populated it using sqlldr. The queries for that are below this question.

I want dateValidFrom grouped by 10 minute intervals and the average speed of each 10 minute interval.

This first query I tried yielded results not in my database. Results go all the way to Sep 2016 but my rows are all Dec 2015.

Query1:
SELECT TO_TIMESTAMP(dateValidFrom, 'YYYY-MM-DD HH24:MI:SS.FF1') + numtodsinterval(rownum*10,'MINUTE'),
AVG(speed) AS maxspeed 
FROM trafficdata 
GROUP BY TO_TIMESTAMP(dateValidFrom, 'YYYY-MM-DD HH24:MI:SS.FF1') + numtodsinterval(rownum*10,'MINUTE');

sample output:
2006-09-26 07:27:31.0                                                      |                   65.24
2016-09-25 07:57:31.0                                                      |                   47.22
2016-09-25 08:37:40.0                                                      |                   47.85


Query2:
SELECT to_char(TRUNC(dateValidFrom,'HH24') + ((trunc(dateValidFrom,'mi')/10)*10)/24/60) AS DVF, 
AVG(speed) AS avgspeed FROM trafficdata 
GROUP BY to_char(TRUNC(dateValidFrom,'HH24') + ((trunc(dateValidFrom,'mi')/10)*10)/24/60);


Result:
"missing right parenthesis" *Note: all parentheses match.

I've tried a host of other queries, usually result in an error but I didn't save them all.

HOW I WANT MY DATA TO LOOK:

 2015-12-02 12.47.00.0 | 26.72 |
 2015-12-02 20.57.00.0 | 38.20 |
 2015-12-02 22.17.00.0 | 6.84 |
 2015-12-02 22.27.00.0 | 39.15 |
 2015-12-02 23.07.00.0 | 41.64 |
 2015-12-02 23.17.00.0 | 36.04 |
 2015-12-02 23.37.00.0 | 1.86 |
 2015-12-02 23.47.00.0 | 1.86 |
 2015-12-02 23.57.00.0 | 42.55 |


_______________________________________________________________

Create table in Oracle:

CREATE TABLE TRAFFICDATA (
 id int,
        speed float,
        traveltime int,
        status int,
        dateValidFrom timestamp,
        linkId int,
        owner varchar(25),
        transcomID int,
        borough varchar(25),
        linkName varchar(25),
        dateValidTo timestamp);



sql loader control file
LOAD DATA 
 infile 'traffData.csv'
 badfile 'nyBad.txt'
 discardfile 'nyDiscard.txt'
 
INSERT
 INTO TABLE TRAFFICDATA
 fields terminated by "," optionally enclosed by '"'
 TRAILING NULLCOLS
  (
  id integer,
        speed float,
        traveltime integer,
        status integer,
        dateValidFrom timestamp  "YYYY-MM-DD HH24.MI.SS.FF1",
        linkId integer,
        owner char(25),
        transcomID integer,
        borough char(25),
        linkName char(25)
  )

Sample of CSV file:

2,21.75,67,0,2015-12-3 00.02.07.000,4616325,NYC_DOT_LIC,4616325,Manhattan,11th ave s ganservoort - west st @ spring st
3,19.26,327,0,2015-12-3 00.02.07.000,4616324,NYC_DOT_LIC,4616324,Manhattan,12th ave @ 45th - 11 ave ganservoort st
4,13.05,250,0,2015-12-3 00.02.07.000,4616338,NYC_DOT_LIC,4616338,Manhattan,12th Ave N 40th - 57th St
106,19.26,119,0,2015-12-3 00.02.07.000,4616323,NYC_DOT_LIC,4616323,Manhattan,12th Ave S 57th St - 45th St
107,49.71,217,0,2015-12-3 12.26.11.000,4616279,NYC_DOT_LIC,4616279,Staten Island,278 E BRUNSWICK AVENUE - SIE E SOUTH AVENUE
119,34.18,191,0,2015-12-3 00.01.28.000,4456502,MTA Bridges & Tunnels,4456502,Manhattan,BBT E Manhattan Portal - Toll Plaza
122,18.02,294,0,2015-12-3 00.02.07.000,4616344,NYC_DOT_LIC,4616344,Manhattan,BBT Manhattan Portal inbound - West St N Watts St
123,26.72,54,0,2015-12-2 12.47.07.000,4616345,NYC_DOT_LIC,4616345,Manhattan,BBT Manhattan Portal inbound - West St S Battery Place
124,37.28,175,0,2015-12-3 00.01.28.000,4456501,MTA Bridges & Tunnels,4456501,Manhattan,BBT W Toll Plaza - Manhattan Portal
129,52.20,84,0,2015-12-3 00.02.06.000,4616246,NYC_DOT_LIC,4616246,Bronx,BE N STRATFORD AVENUE - CASTLE HILL AVE
137,57.17,75,0,2015-12-3 00.02.07.000,4616260,NYC_DOT_LIC,4616260,Bronx,BE S CASTLE HILL AVENUE - STRATFORD AVENUE
140,32.93,92,0,2015-12-3 00.01.27.000,4456479,MTA Bridges & Tunnels,4456479,Queens,BE S TBB EXIT RAMP - MANHATTAN LIFT SPAN
141,44.12,163,0,2015-12-3 00.01.27.000,4456478,MTA Bridges & Tunnels,4456478,Queens,BE S TBB EXIT RAMP - QUEENS ANCHORAGE
145,31.69,80,0,2015-12-3 00.02.07.000,4616342,NYC_DOT_LIC,4616342,Manhattan,BKN Bridge Manhattan Side - FDR N Catherine Slip
149,14.29,484,0,2015-12-3 00.02.07.000,4616339,NYC_DOT_LIC,4616339,Manhattan,BQE N Atlantic Ave - BKN Bridge Manhattan Side
150,13.05,721,0,2015-12-3 00.02.07.000,4616340,NYC_DOT_LIC,4616340,Manhattan,BQE N Atlantic Ave - MAN Bridge Manhattan Side
153,47.22,153,0,2015-12-3 00.02.06.000,4616235,NYC_DOT_LIC,4616235,Brooklyn,BQE N LEONARD STREET - 46TH STREET
154,47.22,87,0,2015-12-3 00.02.06.000,4616229,NYC_DOT_LIC,4616229,Brooklyn,BQE S - GOW S ALTANTIC AVENUE - 9TH STREET
155,42.87,164,0,2015-12-3 00.02.06.000,4616232,NYC_DOT_LIC,4616232,Brooklyn,BQE S 46TH STREET - LEONARD STREET
157,47.22,325,0,2015-12-3 00.02.07.000,4616271,NYC_DOT_LIC,4616271,Brooklyn,BQE S LEONARD STREET - ATLANTIC AVENUE
159,52.82,105,0,2015-12-3 00.02.07.000,4616252,NYC_DOT_LIC,4616252,Bronx,BRP N WATSON AVENUE - FORDHAM ROAD 
160,57.17,97,0,2015-12-3 00.02.07.000,4616266,NYC_DOT_LIC,4616266,Bronx,BRP S FORDHAM ROAD - WATSON AVENUE
164,37.28,124,0,2015-12-3 00.01.28.000,4456497,NYC_DOT_LIC,4456497,Queens,BWB N Queens Anchorage - Toll Plaza 
165,50.95,49,0,2015-12-3 00.02.07.000,4616305,NYC_DOT_LIC,4616305,Bronx,BWB N Toll Plaza - HRP N Lafayatte Ave
167,52.82,119,0,2015-12-3 00.02.07.000,4616312,NYC_DOT_LIC,4616312,Queens,BWB S Queens Anchorage - WSE S Exit 14 (Linden Pl)
168,47.22,98,0,2015-12-3 00.01.28.000,4456496,NYC_DOT_LIC,4456496,Bronx,BWB S Toll Plaza - Queens Anchorage
169,57.17,161,0,2015-12-3 00.02.07.000,4616355,NYC_DOT_LIC,4616355,Queens,Belt Pkwy E 182nd St - Laurelton Pkwy N @ SSP
170,55.30,68,0,2015-12-3 00.02.07.000,4616356,NYC_DOT_LIC,4616356,Queens,Belt Pkwy W 182nd St - JFK Expressway
171,8.08,1459,0,2015-12-3 00.02.07.000,4616357,NYC_DOT_LIC,4616357,Queens,Belt Pkwy W JFK Expressway - VWE N Jamaica Ave
172,55.30,78,0,2015-12-3 00.02.07.000,4616310,NYC_DOT_LIC,4616310,Bronx,CBE AMSTERDAM AVE (L/LVL) - MORRIS AVE
177,49.71,86,0,2015-12-3 00.02.07.000,4616309,NYC_DOT_LIC,4616309,Bronx,CBE E AMSTERDAM AVE(U/LVL) - MORRIS AVE
178,54.06,154,0,2015-12-3 00.02.07.000,4616297,NYC_DOT_LIC,4616297,Bronx,CBE E CASTLE HILL AVE - BE N WATERBURY AVE
184,59.03,43,0,2015-12-3 00.02.07.000,4616253,NYC_DOT_LIC,4616253,Bronx,CBE E TAYLOR AVENUE - CASTLE HILL AVENUE
185,59.03,43,0,2015-12-3 00.02.07.000,4616267,NYC_DOT_LIC,4616267,Bronx,CBE W CASTLE HILL AVENUE - TAYLOR AVENUE
186,37.28,222,0,2015-12-3 00.01.42.000,4620315,PA-GWBridge,4620315,Bronx,CBE W L/LE V AMSTERDAM AVE - I 95 S LOC LNS
190,3.73,932,0,2015-12-3 00.00.42.000,4620332,PA-GWBridge,4620332,Bronx,CBE W MORRIS AVE - GWB W AMSTERDAM AVE (L/LVL)
191,1.86,1550,0,2015-12-3 00.00.42.000,4620331,PA-GWBridge,4620331,Bronx,CBE W MORRIS AVE - GWB W AMSTERDAM AVE (U/LVL)
195,31.69,257,0,2015-12-3 00.01.42.000,4620310,PA-GWBridge,4620310,Bronx,CBE W U/LEV AMSTERDAM AVE - I95 S EXP LNS
199,59.03,185,0,2015-12-3 00.02.07.000,4616351,NYC_DOT_LIC,4616351,Queens,CIP N Hempstead Tpk - LIE
204,6.84,1500,0,2015-12-3 00.02.07.000,4616320,NYC_DOT_LIC,4616320,Queens,CIP N TNB - Whitestone Expwy S Exit 14 (Linden Pl)
205,57.79,277,0,2015-12-3 00.01.56.000,4362247,NYC-DOT-Region 10,4362247,Queens,CIP NB GCP - TNB
207,59.03,129,0,2015-12-3 00.02.07.000,4616352,NYC_DOT_LIC,4616352,Queens,CIP S Hempstead Tpk - Laurelton Pkwy @ SSP
208,59.03,185,0,2015-12-3 00.02.07.000,4616350,NYC_DOT_LIC,4616350,Queens,CIP S LIE - Hempstead Tpk
211,57.79,284,0,2015-12-3 00.01.56.000,4362250,NYC-DOT-Region 10,4362250,Queens,CVE NB GCP - WILLETS PT BLVD
212,52.20,210,0,2015-12-3 00.01.56.000,4362244,NYC-DOT-Region 10,4362244,Queens,CVE NB LIE - WILLETS PT BLVD
213,39.15,60,0,2015-12-3 00.01.27.000,4456450,MTA Bridges & Tunnels,4456450,Manhattan,FDR N - TBB E 116TH STREET - MANHATTAN TRUSS
215,47.22,146,0,2015-12-3 00.02.07.000,4616329,NYC_DOT_LIC,4616329,Manhattan,FDR N 25th - 63rd St
217,49.71,202,0,2015-12-3 00.02.07.000,4616328,NYC_DOT_LIC,4616328,Manhattan,FDR N Catherine Slip - 25th St
221,52.20,196,0,2015-12-3 00.02.07.000,4616333,NYC_DOT_LIC,4616333,Manhattan,FDR S 25th St - Catherine Slip
222,49.09,139,0,2015-12-3 00.02.07.000,4616332,NYC_DOT_LIC,4616332,Manhattan,FDR S 63rd - 25th St
223,31.07,84,0,2015-12-3 00.02.07.000,4616341,NYC_DOT_LIC,4616341,Manhattan,FDR S Catherine Slip - BKN Bridge Manhattan Side
224,8.70,184,0,2015-12-3 00.02.07.000,4616334,NYC_DOT_LIC,4616334,Manhattan,FDR S Catherine Slip - Whitehall St
225,31.69,111,0,2015-12-3 10.56.05.000,4456495,NYC_DOT_LIC,4456495,Manhattan,FDR S Whitehall St - BBT Manhattan Portal outbound
257,50.95,249,0,2015-12-3 00.02.07.000,4616318,NYC_DOT_LIC,4616318,Brooklyn,GOW N 7TH AVENUE - 9TH STREET
258,55.30,78,0,2015-12-3 00.02.06.000,4616220,NYC_DOT_LIC,4616220,Brooklyn,GOW N 92ND STREET - 7TH AVENUE
259,42.87,94,0,2015-12-3 00.02.06.000,4616223,NYC_DOT_LIC,4616223,Brooklyn,GOW N 9TH STREET - ATLANTIC AVENUE
261,8.08,481,0,2015-12-3 00.02.06.000,4616226,NYC_DOT_LIC,4616226,Brooklyn,GOW S 7TH AVENUE - 92ND STREET
262,49.09,259,0,2015-12-3 00.02.07.000,4616319,NYC_DOT_LIC,4616319,Brooklyn,GOW S 9TH STREET - 7TH AVENUE
263,1.86,800,0,2015-12-3 00.01.28.000,4763657,Verrazano-Narrows-Bridge,4763657,Brooklyn,GOW S VNB W 92ND STREET - BKLYN GANTRY LOWER LEVEL
264,1.86,827,0,2015-12-2 23.44.27.000,4763655,Verrazano-Narrows-Bridge,4763655,Brooklyn,GOW S VNB W 92ND STREET - BKLYN GANTRY UPPER LEVEL
265,57.79,127,0,2015-12-3 00.01.42.000,4620298,PA-GWBridge,4620298,Manhattan,GWB E LOWER LEVEL PLAZA - CBE E LOWER LEVEL AMSTERDAM AVE
295,62.14,66,0,2015-12-3 00.02.07.000,4616250,NYC_DOT_LIC,4616250,Bronx,HRP N LAFAYETTE AVENUE - E TREMONT AVENUE
298,45.98,55,0,2015-12-3 00.01.28.000,4456498,NYC_DOT_LIC,4456498,Bronx,HRP S Lafayette Ave - BWB S Toll Plaza
311,55.30,90,0,2015-12-3 00.01.57.000,4362342,NYC_DOT_LIC,4362342,Queens,LIE E 84TH ST - 108TH ST
313,52.82,286,0,2015-12-3 00.02.07.000,4616365,NYC_DOT_LIC,4616365,Queens,LIE E QMT TOLL PLAZA - 84TH ST
315,3.73,1173,0,2015-12-3 00.02.07.000,4616364,NYC_DOT_LIC,4616364,Queens,LIE W 108TH ST - 84TH ST
316,21.13,720,0,2015-12-3 00.01.28.000,4456516,NYC_DOT_LIC,4456516,Queens,LIE W 84TH ST - QMT TOLL PLAZA
318,52.20,75,0,2015-12-3 00.01.56.000,4362249,NYC-DOT-Region 10,4362249,Queens,LIE WB LITTLE NECK PKWY - NB CIP
319,55.92,171,0,2015-12-3 00.01.56.000,4362251,NYC-DOT-Region 10,4362251,Queens,LIE WB LITTLE NECK PKWY - NB CVE


and Chris said...

The output you want doesn't match the data you've provided? (most of the dates are on the 3rd Dec, most just after 00:00, but you're expecting values on the 2nd...).

Anyway, dateValidFrom is a timestamp, so you can pull the minute values out with extract (you can't get minutes from date data types...)

extract ( minute from dateValidFrom )


So convert the datetimes to 10 minute groups, you just need to:

- Divide this by 10 and take the floor
- Multiply back up by ten
- Add these minutes back onto the hour (trunc(dt, 'hh24')). You can do this either by
- dividing by the minutes in a day (1440)
- converting it to a minute interval using numtodsinterval(..., 'minute')

For example:

select trunc(dateValidFrom, 'hh24') + numtodsinterval( floor ( extract ( minute from dateValidFrom ) / 10 ) * 10 , 'minute'),
       round(avg(speed), 2) avsp
from   trafficdata
group  by trunc(dateValidFrom, 'hh24') + numtodsinterval( floor ( extract ( minute from dateValidFrom ) / 10 ) * 10 , 'minute')
order  by 1;

TRUNC(DATEVALIDFROM,       AVSP
-------------------- ----------
02-DEC-2015 12:40:00      26.72
02-DEC-2015 23:40:00       1.86
03-DEC-2015 00:00:00      40.11
03-DEC-2015 10:50:00      31.69
03-DEC-2015 12:20:00      49.71

Rating

  (10 ratings)

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

Comments

I'm past the error message, but...

Emily, July 14, 2017 - 4:38 pm UTC

Sorry about the mismatch between my example output and CSV file.

I used your query and now the output looks like this:

TRUNC(DAT| AVSP
---------|------------------------
03-DEC-05| .00
03-DEC-05| .00
03-DEC-05| .00
03-DEC-05| .00

I really don't understand why the 'hh24' mask would return DD-MON-YY. Its clearly not what the query asks for.

I changed the query to:
to_char(trunc(dateValidFrom, 'hh24') + numtodsinterval( floor ( extract ( minute from dateValidFrom ) / 10 ) * 10 , 'minute'))

and it returned:
TO_CHAR(TRUNC(DATE| AVSP
------------------|------------------------
02-DEC-15 | .00
03-DEC-05 | .00
03-DEC-15 | .00
| .00


Chris Saxon
July 17, 2017 - 9:33 am UTC

What do you get if you run:

select * from trafficdata

Query results

Emily, July 17, 2017 - 2:21 pm UTC

Here are the first and last lines of my output:

ID SPEED TRAVELTIME STATUS DATEVALIDFROM LINKID OWNER TRANSCOMID BOROUGH LINKNAME DATEVALIDTO
---------------------------------------------------------------------------
825371698 0 808204086 825242156 2005-12-03 00:02:07.0 909194804 325 1598249294 DOT_LIC 4616325
959523891 0 741814835 808594480 2015-12-03 00:02:07.0 909194804 324 1598249294 DOT_LIC 4616324
741619249 0 808528949 741354553 2015-12-03 21:02:41.0 909456436 497 1598249294 DOT_LIC 4456497
741684785 0 825568313 84175671
Chris Saxon
July 17, 2017 - 3:00 pm UTC

And what happens when you run:

alter session set nls_timestamp_format = 'DD-MON-YYYY HH24.MI.SS';
alter session set nls_date_format = 'DD-MON-YYYY HH24.MI.SS';


?

My timestamp

Emily, July 17, 2017 - 3:08 pm UTC

I ran the following commands before executing select * from traffic data:

alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF1';
alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF1';

select current_timestamp from dual;

CURRENT_TIMESTAMP

2017-07-17 11:00:38.2

select dateValidFrom from trafficdata where rownum <= 10;

DATEVALIDFROM

2005-12-03 00:02:07.0
2015-12-03 00:02:07.0
2015-12-03 00:02:07.0
2015-12-03 00:02:07.0
2015-12-03 12:26:11.0
2015-12-03 00:01:28.0
2015-12-03 00:02:07.0
2015-12-02 12:47:07.0
2015-12-03 00:01:28.0
2015-12-03 00:02:06.0



Chris Saxon
July 17, 2017 - 4:45 pm UTC

And what exactly is the problem with the output you're getting from the average speed query? Does running those alter sessions make any difference?

Unfortunately, no

A reader, July 17, 2017 - 4:55 pm UTC

The problem is, the date value is missing the time information and the averaged value is all zeroes. Altering the timestamp of my session did not help. This is a sample of the output I am getting:

TRUNC(DAT| AVSP
---------|------------------------
03-DEC-05| .00
03-DEC-05| .00
03-DEC-05| .00
03-DEC-05| .00
03-DEC-05| .00
03-DEC-05| .00
03-DEC-05| .00
03-DEC-05| .00
03-DEC-05| .00
03-DEC-05| .00
03-DEC-05| .00

I am expecting output similar to what is below. The data is displayed in 10 min intervals and the speed value is average for each 10 minute interval.

2015-12-03 00.07.00.0 | 44.00 |
2015-12-03 00.17.00.0 | 43.88 |
2015-12-03 00.27.00.0 | 43.29 |
2015-12-03 00.37.00.0 | 35.10 |
2015-12-03 00.47.00.0 | 45.82 |
2015-12-03 00.57.00.0 | 46.61 |
2015-12-03 01.07.00.0 | 46.42 |

Chris Saxon
July 18, 2017 - 9:56 am UTC

Assuming those dates do have times, this is an issue with formatting and/or implicit conversions.

Test each individual function and see what output you get. Check all your NLS date/time settings. See what happens when you to_char the results with different format masks.

And of course, you're expecting dates in 2015, but have values from 2005 in your data...

Got the right date format...

Emily, August 01, 2017 - 3:55 pm UTC

This is what worked for me:

alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF1';
alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF1';
alter session set NLS_DATE_FORMAT='YYYY-MM-DD';
alter session set NLS_TIME_FORMAT='HH24:MI:SS.FF1';

select to_timestamp(dateValidFrom, 'YYYY-MM-DD hh24:mi:ss.ff1') + numtodsinterval( floor ( extract ( minute from dateValidFrom ) / 10 ) * 10 , 'minute') dvf, round(avg(speed), 2) avsp
from trafficdata
group by to_timestamp(dateValidFrom, 'YYYY-MM-DD hh24:mi:ss.ff1') + numtodsinterval( floor ( extract ( minute from dateValidFrom ) / 10 ) * 10 , 'minute')
order by 1;

I noticed, however, that my output is grouped by 5 and 15 minute intervals. Also, my AVSP is .00 for every row. I've experimented with that a bit but have more to do obviously.
Chris Saxon
August 01, 2017 - 4:03 pm UTC

If you supply your data (in the form of insert statements) we can take a look.

For a quick way to get these, run the following in SQL Developer using the script method (F5):

select /*csv*/* from trafficdata;

Also noticed

Emily, August 01, 2017 - 4:09 pm UTC

I also noticed that non-existent 2005 years appear when I run this query. All the years are 2015 if I just run

SELECT dateValidFrom from trafficdata;
Chris Saxon
August 02, 2017 - 10:19 am UTC

So can you send us the inserts for your data?

The short answer is no

A reader, August 03, 2017 - 3:46 pm UTC

The answer I am getting from the people here is that I can't connect to the Oracle box using SQL Developer. I can send you the SQL Loader control file and CSV I used to populate my table. I can't paste the CSV here because I'm sure it will exceed the space limitations. Is there a way to send you files?
Chris Saxon
August 04, 2017 - 3:57 pm UTC

We only need a sample of the data that shows the problem.

And why can't you connect using SQL Dev?

It's the way we set up our tables

Emily, August 04, 2017 - 8:54 pm UTC

The answer I got is, it's the way we set up our tables. I access the database on a linux box by typing sqlplus username/password. The box in question is not a dedicated Oracle server.

SQL Developer exists on the linux box. I used a tool called Xming to open SQL Developer but when I try to make a connection to the database, everything I try results in a "Network Adapter could not make connection" message.

As Requested...

Emily, August 04, 2017 - 8:58 pm UTC

Here are my CSV data and ctl file for sqlldr:

LOAD DATA
infile 'traffData.csv'
badfile 'nyBad.txt'
discardfile 'nyDiscard.txt'

INSERT
INTO TABLE TRAFFICDATA
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(
id integer,
speed float,
traveltime integer,
status integer,
dateValidFrom timestamp "YYYY-MM-DD HH24:MI:SS.FF1",
linkId integer,
owner char(25),
transcomID integer,
borough char(25),
linkName char(25)
)

2,21.75,67,0,2015-12-3 00:02:07.0,4616325,NYC_DOT_LIC,4616325,Manhattan,11th ave s ganservoort - west st @ spring st
3,19.26,327,0,2015-12-3 00:02:07.0,4616324,NYC_DOT_LIC,4616324,Manhattan,12th ave @ 45th - 11 ave ganservoort st
4,13.05,250,0,2015-12-3 00:02:07.0,4616338,NYC_DOT_LIC,4616338,Manhattan,12th Ave N 40th - 57th St
106,19.26,119,0,2015-12-3 00:02:07.0,4616323,NYC_DOT_LIC,4616323,Manhattan,12th Ave S 57th St - 45th St
107,49.71,217,0,2015-12-3 12:26:11.0,4616279,NYC_DOT_LIC,4616279,Staten Island,278 E BRUNSWICK AVENUE - SIE E SOUTH AVENUE
119,34.18,191,0,2015-12-3 00:01:28.0,4456502,MTA Bridges & Tunnels,4456502,Manhattan,BBT E Manhattan Portal - Toll Plaza
122,18.02,294,0,2015-12-3 00:02:07.0,4616344,NYC_DOT_LIC,4616344,Manhattan,BBT Manhattan Portal inbound - West St N Watts St
123,26.72,54,0,2015-12-2 12:47:07.0,4616345,NYC_DOT_LIC,4616345,Manhattan,BBT Manhattan Portal inbound - West St S Battery Place
124,37.28,175,0,2015-12-3 00:01:28.0,4456501,MTA Bridges & Tunnels,4456501,Manhattan,BBT W Toll Plaza - Manhattan Portal
129,52.20,84,0,2015-12-3 00:02:06.0,4616246,NYC_DOT_LIC,4616246,Bronx,BE N STRATFORD AVENUE - CASTLE HILL AVE
137,57.17,75,0,2015-12-3 00:02:07.0,4616260,NYC_DOT_LIC,4616260,Bronx,BE S CASTLE HILL AVENUE - STRATFORD AVENUE
140,32.93,92,0,2015-12-3 00:01:27.0,4456479,MTA Bridges & Tunnels,4456479,Queens,BE S TBB EXIT RAMP - MANHATTAN LIFT SPAN
141,44.12,163,0,2015-12-3 00:01:27.0,4456478,MTA Bridges & Tunnels,4456478,Queens,BE S TBB EXIT RAMP - QUEENS ANCHORAGE
145,31.69,80,0,2015-12-3 00:02:07.0,4616342,NYC_DOT_LIC,4616342,Manhattan,BKN Bridge Manhattan Side - FDR N Catherine Slip
149,14.29,484,0,2015-12-3 00:02:07.0,4616339,NYC_DOT_LIC,4616339,Manhattan,BQE N Atlantic Ave - BKN Bridge Manhattan Side
150,13.05,721,0,2015-12-3 00:02:07.0,4616340,NYC_DOT_LIC,4616340,Manhattan,BQE N Atlantic Ave - MAN Bridge Manhattan Side
153,47.22,153,0,2015-12-3 00:02:06.0,4616235,NYC_DOT_LIC,4616235,Brooklyn,BQE N LEONARD STREET - 46TH STREET
154,47.22,87,0,2015-12-3 00:02:06.0,4616229,NYC_DOT_LIC,4616229,Brooklyn,BQE S - GOW S ALTANTIC AVENUE - 9TH STREET
155,42.87,164,0,2015-12-3 00:02:06.0,4616232,NYC_DOT_LIC,4616232,Brooklyn,BQE S 46TH STREET - LEONARD STREET
157,47.22,325,0,2015-12-3 00:02:07.0,4616271,NYC_DOT_LIC,4616271,Brooklyn,BQE S LEONARD STREET - ATLANTIC AVENUE
159,52.82,105,0,2015-12-3 00:02:07.0,4616252,NYC_DOT_LIC,4616252,Bronx,BRP N WATSON AVENUE - FORDHAM ROAD
160,57.17,97,0,2015-12-3 00:02:07.0,4616266,NYC_DOT_LIC,4616266,Bronx,BRP S FORDHAM ROAD - WATSON AVENUE
164,37.28,124,0,2015-12-3 00:01:28.0,4456497,NYC_DOT_LIC,4456497,Queens,BWB N Queens Anchorage - Toll Plaza
165,50.95,49,0,2015-12-3 00:02:07.0,4616305,NYC_DOT_LIC,4616305,Bronx,BWB N Toll Plaza - HRP N Lafayatte Ave
167,52.82,119,0,2015-12-3 00:02:07.0,4616312,NYC_DOT_LIC,4616312,Queens,BWB S Queens Anchorage - WSE S Exit 14 (Linden Pl)
168,47.22,98,0,2015-12-3 00:01:28.0,4456496,NYC_DOT_LIC,4456496,Bronx,BWB S Toll Plaza - Queens Anchorage
169,57.17,161,0,2015-12-3 00:02:07.0,4616355,NYC_DOT_LIC,4616355,Queens,Belt Pkwy E 182nd St - Laurelton Pkwy N @ SSP
170,55.30,68,0,2015-12-3 00:02:07.0,4616356,NYC_DOT_LIC,4616356,Queens,Belt Pkwy W 182nd St - JFK Expressway
171,8.08,1459,0,2015-12-3 00:02:07.0,4616357,NYC_DOT_LIC,4616357,Queens,Belt Pkwy W JFK Expressway - VWE N Jamaica Ave
172,55.30,78,0,2015-12-3 00:02:07.0,4616310,NYC_DOT_LIC,4616310,Bronx,CBE AMSTERDAM AVE (L/LVL) - MORRIS AVE
177,49.71,86,0,2015-12-3 00:02:07.0,4616309,NYC_DOT_LIC,4616309,Bronx,CBE E AMSTERDAM AVE(U/LVL) - MORRIS AVE
178,54.06,154,0,2015-12-3 00:02:07.0,4616297,NYC_DOT_LIC,4616297,Bronx,CBE E CASTLE HILL AVE - BE N WATERBURY AVE
184,59.03,43,0,2015-12-3 00:02:07.0,4616253,NYC_DOT_LIC,4616253,Bronx,CBE E TAYLOR AVENUE - CASTLE HILL AVENUE
185,59.03,43,0,2015-12-3 00:02:07.0,4616267,NYC_DOT_LIC,4616267,Bronx,CBE W CASTLE HILL AVENUE - TAYLOR AVENUE
186,37.28,222,0,2015-12-3 00:01:42.0,4620315,PA-GWBridge,4620315,Bronx,CBE W L/LE V AMSTERDAM AVE - I 95 S LOC LNS
190,3.73,932,0,2015-12-3 00:00:42.0,4620332,PA-GWBridge,4620332,Bronx,CBE W MORRIS AVE - GWB W AMSTERDAM AVE (L/LVL)
191,1.86,1550,0,2015-12-3 00:00:42.0,4620331,PA-GWBridge,4620331,Bronx,CBE W MORRIS AVE - GWB W AMSTERDAM AVE (U/LVL)
195,31.69,257,0,2015-12-3 00:01:42.0,4620310,PA-GWBridge,4620310,Bronx,CBE W U/LEV AMSTERDAM AVE - I95 S EXP LNS
199,59.03,185,0,2015-12-3 00:02:07.0,4616351,NYC_DOT_LIC,4616351,Queens,CIP N Hempstead Tpk - LIE
204,6.84,1500,0,2015-12-3 00:02:07.0,4616320,NYC_DOT_LIC,4616320,Queens,CIP N TNB - Whitestone Expwy S Exit 14 (Linden Pl)
205,57.79,277,0,2015-12-3 00:01:56.0,4362247,NYC-DOT-Region 10,4362247,Queens,CIP NB GCP - TNB
207,59.03,129,0,2015-12-3 00:02:07.0,4616352,NYC_DOT_LIC,4616352,Queens,CIP S Hempstead Tpk - Laurelton Pkwy @ SSP
208,59.03,185,0,2015-12-3 00:02:07.0,4616350,NYC_DOT_LIC,4616350,Queens,CIP S LIE - Hempstead Tpk
211,57.79,284,0,2015-12-3 00:01:56.0,4362250,NYC-DOT-Region 10,4362250,Queens,CVE NB GCP - WILLETS PT BLVD
212,52.20,210,0,2015-12-3 00:01:56.0,4362244,NYC-DOT-Region 10,4362244,Queens,CVE NB LIE - WILLETS PT BLVD
213,39.15,60,0,2015-12-3 00:01:27.0,4456450,MTA Bridges & Tunnels,4456450,Manhattan,FDR N - TBB E 116TH STREET - MANHATTAN TRUSS
215,47.22,146,0,2015-12-3 00:02:07.0,4616329,NYC_DOT_LIC,4616329,Manhattan,FDR N 25th - 63rd St
217,49.71,202,0,2015-12-3 00:02:07.0,4616328,NYC_DOT_LIC,4616328,Manhattan,FDR N Catherine Slip - 25th St
221,52.20,196,0,2015-12-3 00:02:07.0,4616333,NYC_DOT_LIC,4616333,Manhattan,FDR S 25th St - Catherine Slip
222,49.09,139,0,2015-12-3 00:02:07.0,4616332,NYC_DOT_LIC,4616332,Manhattan,FDR S 63rd - 25th St
223,31.07,84,0,2015-12-3 00:02:07.0,4616341,NYC_DOT_LIC,4616341,Manhattan,FDR S Catherine Slip - BKN Bridge Manhattan Side
224,8.70,184,0,2015-12-3 00:02:07.0,4616334,NYC_DOT_LIC,4616334,Manhattan,FDR S Catherine Slip - Whitehall St
225,31.69,111,0,2015-12-3 10:56:05.0,4456495,NYC_DOT_LIC,4456495,Manhattan,FDR S Whitehall St - BBT Manhattan Portal outbound
257,50.95,249,0,2015-12-3 00:02:07.0,4616318,NYC_DOT_LIC,4616318,Brooklyn,GOW N 7TH AVENUE - 9TH STREET
258,55.30,78,0,2015-12-3 00:02:06.0,4616220,NYC_DOT_LIC,4616220,Brooklyn,GOW N 92ND STREET - 7TH AVENUE
259,42.87,94,0,2015-12-3 00:02:06.0,4616223,NYC_DOT_LIC,4616223,Brooklyn,GOW N 9TH STREET - ATLANTIC AVENUE
261,8.08,481,0,2015-12-3 00:02:06.0,4616226,NYC_DOT_LIC,4616226,Brooklyn,GOW S 7TH AVENUE - 92ND STREET
262,49.09,259,0,2015-12-3 00:02:07.0,4616319,NYC_DOT_LIC,4616319,Brooklyn,GOW S 9TH STREET - 7TH AVENUE
263,1.86,800,0,2015-12-3 00:01:28.0,4763657,Verrazano-Narrows-Bridge,4763657,Brooklyn,GOW S VNB W 92ND STREET - BKLYN GANTRY LOWER LEVEL
264,1.86,827,0,2015-12-2 23:44:27.0,4763655,Verrazano-Narrows-Bridge,4763655,Brooklyn,GOW S VNB W 92ND STREET - BKLYN GANTRY UPPER LEVEL
265,57.79,127,0,2015-12-3 00:01:42.0,4620298,PA-GWBridge,4620298,Manhattan,GWB E LOWER LEVEL PLAZA - CBE E LOWER LEVEL AMSTERDAM AVE
295,62.14,66,0,2015-12-3 00:02:07.0,4616250,NYC_DOT_LIC,4616250,Bronx,HRP N LAFAYETTE AVENUE - E TREMONT AVENUE
298,45.98,55,0,2015-12-3 00:01:28.0,4456498,NYC_DOT_LIC,4456498,Bronx,HRP S Lafayette Ave - BWB S Toll Plaza
311,55.30,90,0,2015-12-3 00:01:57.0,4362342,NYC_DOT_LIC,4362342,Queens,LIE E 84TH ST - 108TH ST
313,52.82,286,0,2015-12-3 00:02:07.0,4616365,NYC_DOT_LIC,4616365,Queens,LIE E QMT TOLL PLAZA - 84TH ST
315,3.73,1173,0,2015-12-3 00:02:07.0,4616364,NYC_DOT_LIC,4616364,Queens,LIE W 108TH ST - 84TH ST
316,21.13,720,0,2015-12-3 00:01:28.0,4456516,NYC_DOT_LIC,4456516,Queens,LIE W 84TH ST - QMT TOLL PLAZA
318,52.20,75,0,2015-12-3 00:01:56.0,4362249,NYC-DOT-Region 10,4362249,Queens,LIE WB LITTLE NECK PKWY - NB CIP
319,55.92,171,0,2015-12-3 00:01:56.0,4362251,NYC-DOT-Region 10,4362251,Queens,LIE WB LITTLE NECK PKWY - NB CVE
324,36.04,155,0,2015-12-2 23:13:43.0,4329473,PA -Lincoln Tunnel,4329473,Manhattan,LINCOLN TUNNEL E CENTER TUBE NJ - NY
325,34.80,158,0,2015-12-3 00:01:42.0,4329472,PA -Lincoln Tunnel,4329472,Manhattan,LINCOLN TUNNEL E SOUTH TUBE - NJ - NY
329,36.04,153,0,2015-12-2 23:19:42.0,4329508,PA -Lincoln Tunnel,4329508,Manhattan,LINCOLN TUNNEL W CENTER TUBE NY - NJ
330,21.13,234,0,2015-12-3 00:01:42.0,4329507,PA -Lincoln Tunnel,4329507,Manhattan,LINCOLN TUNNEL W NORTH TUBE NY - NJ
331,55.92,140,0,2015-12-3 00:02:07.0,4616353,NYC_DOT_LIC,4616353,Queens,Laurelton Pkwy N @ SSP - CIP N Hempstead Tpk
332,57.17,161,0,2015-12-3 00:02:07.0,4616354,NYC_DOT_LIC,4616354,Queens,Laurelton Pkwy S @ SSP - Belt Pkwy W 182nd St
339,37.90,234,0,2015-12-3 15:23:00.0,4575278,NYC Thruway Tarrytown,4575278,Bronx,MDE N VAN CORTLAND PARK - NYST N EXIT 1 (MP.48)
344,34.18,130,0,2015-12-3 00:00:42.0,4620330,NYC_DOT_LIC,4620330,Bronx,MDE S HARLEM RIVER PARK - GWB W AMSTERDAM AVENUE LOWER LEVEL
345,9.94,422,0,2015-12-3 00:01:42.0,4620314,NYC_DOT_LIC,4620314,Bronx,MDE S HARLEM RIVER PARK - GWB W AMSTERDAM AVENUE UPPER LEVEL
347,41.01,174,0,2015-12-3 00:01:27.0,4456477,MTA Bridges & Tunnels,4456477,Queens,MDE S TBB EXIT RAMP - QUEENS ANCHORAGE
349,8.08,381,0,2015-12-3 09:44:05.0,4329499,NYC_DOT_LIC,4329499,Staten Island,MLK N WALKER STREET - NJ ROUTE 169
350,49.71,166,0,2015-12-3 00:02:06.0,4616196,NYC_DOT_LIC,4616196,Staten Island,MLK S - SIE E WALKER STREET - WOOLEY AVENUE
351,47.85,111,0,2015-12-3 00:02:06.0,4616210,NYC_DOT_LIC,4616210,Staten Island,MLK S - SIE W WALKER STREET - RICHMOND AVENUE
364,11.81,322,0,2015-12-3 00:01:28.0,4456511,NYC_DOT_LIC,4456511,Manhattan,QMT E Manhattan Side - Toll Plaza
365,1.86,1491,0,2015-12-2 23:54:28.0,4456510,NYC_DOT_LIC,4456510,Queens,QMT W Toll Plaza - Manhattan Side
369,37.28,143,0,2015-12-2 21:01:05.0,4616282,NYC_DOT_LIC,4616282,Staten Island,ROUTE 169 S - MLK WALKER STREET
375,64.00,104,0,2015-12-3 00:02:06.0,4616193,NYC_DOT_LIC,4616193,Staten Island,SIE E BRADLEY AVENUE - CLOVE ROAD
376,52.82,103,0,2015-12-3 05:19:03.0,4616192,NYC_DOT_LIC,4616192,Staten Island,SIE E CLOVE ROAD - FINGERBOARD ROAD
377,60.89,64,0,2015-12-3 00:02:06.0,4616195,NYC_DOT_LIC,4616195,Staten Island,SIE E RICHMOND AVENUE - WOOLEY AVENUE
378,55.30,49,0,2015-12-3 00:02:06.0,4616197,NYC_DOT_LIC,4616197,Staten Island,SIE E SOUTH AVENUE - RICHMOND AVENUE
379,55.30,53,0,2015-12-3 00:01:28.0,4763650,NYC_DOT_LIC,4763650,Staten Island,SIE E VNB E FINGERBOARD ROAD - SI GANTRY LOWER LEVEL
380,55.30,44,0,2015-12-3 00:01:28.0,4763648,NYC_DOT_LIC,4763648,Staten Island,SIE E VNB E FINGERBOARD ROAD - SI GANTRY UPPER LEVEL
381,57.17,28,0,2015-12-3 00:02:06.0,4616194,NYC_DOT_LIC,4616194,Staten Island,SIE E WOOLEY AVENUE - BRADLEY AVENUE
382,39.15,182,0,2015-12-2 22:35:05.0,4616216,NYC_DOT_LIC,4616216,Staten Island,SIE E-MLK N RICHMOND AVENUE - WALKER STREET
383,39.15,191,0,2015-12-2 20:57:06.0,4616217,NYC_DOT_LIC,4616217,Staten Island,SIE W - MLK N WOOLEY AVENUE - WLAKER STREET
384,62.76,110,0,2015-12-3 00:02:06.0,4616199,NYC_DOT_LIC,4616199,Staten Island,SIE W - WSE S SOUTH AVENUE - SOUTH AVENUE
385,52.82,27,0,2015-12-3 00:02:06.0,4616208,NYC_DOT_LIC,4616208,Staten Island,SIE W BRADLEY AVENUE - WOOLEY AVENUE
387,45.98,93,0,2015-12-3 00:02:06.0,4616206,NYC_DOT_LIC,4616206,Staten Island,SIE W FINGERBOARD ROAD - CLOVE ROAD
388,62.14,46,0,2015-12-3 00:02:06.0,4616211,NYC_DOT_LIC,4616211,Staten Island,SIE W RICHMOND AVENUE - SOUTH AVENUE
389,11.18,1003,0,2015-12-3 10:03:36.0,4329480,NYC_DOT_LIC,4329480,Staten Island,SIE W SOUTH AVENUE - 278 W BRUNSWICK AVENUE
390,64.00,61,0,2015-12-3 00:02:06.0,4616209,NYC_DOT_LIC,4616209,Staten Island,SIE W WOOLEY AVENUE - RICHMOND AVENUE
394,44.74,160,0,2015-12-3 00:02:06.0,4616225,NYC_DOT_LIC,4616225,Queens,TBB N QUEENS ANCHORAGE - BE N
395,42.87,188,0,2015-12-3 00:01:27.0,4456476,MTA Bridges & Tunnels,4456476,Queens,TBB N QUEENS ANCHORAGE - MANHATTAN LIFT SPAN


Chris Saxon
August 07, 2017 - 3:23 am UTC

How did you create that control file and CSV? Because when I use it I end up with a whole bunch of rows dated 15 (not 2015):

select count(*) from trafficdata
where  extract(year from datevalidfrom) = 15;

COUNT(*)  
40

using analytics

Rajeshwaran Jeyabal, August 07, 2017 - 1:12 am UTC

The above data and control file still doesn't work perfect.

....
I want dateValidFrom grouped by 10 minute intervals and the average speed of each 10 minute interval.
....


In the below example pretend the column X as "dateValidFrom" and the column Y as "speed"

demo@ORA11G> set feedback off
demo@ORA11G> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
demo@ORA11G> drop table t;
demo@ORA11G> create table t ( x timestamp, y int );
demo@ORA11G> declare
  2          l_date timestamp := to_timestamp( '12:22:03', 'hh24:mi:ss' );
  3  begin
  4          for i in 1 .. 10
  5          loop
  6               insert into t values ( l_date, dbms_random.value( 0, 100 ) );
  7               l_date := l_date + 1/24/60/60;
  8          end loop;
  9          l_date := l_date + 5/24/60;
 10          for i in 1 .. 3
 11          loop
 12               insert into t values ( l_date, dbms_random.value( 0, 100 ) );
 13               l_date := l_date + 1/24/60/60;
 14          end loop;
 15          l_date := l_date + 15/24/60;
 16          for i in 1 .. 12
 17          loop
 18               insert into t values ( l_date, dbms_random.value( 0, 100 ) );
 19               l_date := l_date + 1/24/60/60;
 20          end loop;
 21  end;
 22  /
demo@ORA11G> set feedback on
demo@ORA11G>
demo@ORA11G> select * from t;

X                                                                                    Y
--------------------------------------------------------------------------- ----------
01-AUG-17 12.22.03.000000 PM                                                        41
01-AUG-17 12.22.04.000000 PM                                                        84
01-AUG-17 12.22.05.000000 PM                                                        53
01-AUG-17 12.22.06.000000 PM                                                        65
01-AUG-17 12.22.07.000000 PM                                                         1
01-AUG-17 12.22.08.000000 PM                                                        51
01-AUG-17 12.22.09.000000 PM                                                         8
01-AUG-17 12.22.10.000000 PM                                                        32
01-AUG-17 12.22.11.000000 PM                                                         7
01-AUG-17 12.22.12.000000 PM                                                        40
01-AUG-17 12.27.13.000000 PM                                                        50
01-AUG-17 12.27.14.000000 PM                                                         0
01-AUG-17 12.27.15.000000 PM                                                       100
01-AUG-17 12.42.16.000000 PM                                                        71
01-AUG-17 12.42.17.000000 PM                                                        62
01-AUG-17 12.42.18.000000 PM                                                        78
01-AUG-17 12.42.19.000000 PM                                                        46
01-AUG-17 12.42.20.000000 PM                                                         5
01-AUG-17 12.42.21.000000 PM                                                        11
01-AUG-17 12.42.22.000000 PM                                                        55
01-AUG-17 12.42.23.000000 PM                                                        87
01-AUG-17 12.42.24.000000 PM                                                        73
01-AUG-17 12.42.25.000000 PM                                                        40
01-AUG-17 12.42.26.000000 PM                                                        86
01-AUG-17 12.42.27.000000 PM                                                        93

25 rows selected.

demo@ORA11G>


using Analytics - the above data set would be grouped with '10' minute interval like this.

demo@ORA11G> select grp2, min(x), max(x),avg(y),count(*)
  2  from (
  3  select x,y, last_value(grp ignore nulls) over( order by x ) grp2
  4  from (
  5  select x, y,
  6        case when lag(x) over(order by x) is null or
  7              x - lag(x) over(order by x) > numtodsinterval(10,'minute')
  8            then x end grp
  9  from t
 10       )
 11       )
 12  group by grp2
 13  order by grp2 ;

GRP2                           MIN(X)                         MAX(X)                             AVG(Y)   COUNT(*)
------------------------------ ------------------------------ ------------------------------ ---------- ----------
01-AUG-17 12.22.03.000000 PM   01-AUG-17 12.22.03.000000 PM   01-AUG-17 12.27.15.000000 PM   40.9230769         13
01-AUG-17 12.42.16.000000 PM   01-AUG-17 12.42.16.000000 PM   01-AUG-17 12.42.27.000000 PM   58.9166667         12

2 rows selected.

demo@ORA11G>


So take this and apply to your logic and see how it goes.
Incase of any discrepancy, post an example with small data that describes your problem clear and concise.