Skip to Main Content

Breadcrumb

Warning

Before you submit your comment...

Remember - this is about feedback to the original question. AskTOM is about building a strong Oracle community, not stealing time away from others needing assistance by sneaking in a brand new question under the guise of "Comment".

If your comment requires a response that might include a query, please supply very simple CREATE TABLE and INSERT statements. Nice simple scripts so we can replicate your issue simply.

Remember to take a look at Legal Notices and Terms of Use before supplying a comment...

Don't forget, your comments are public. If you want to send a comment to just the AskTOM team, please use the feedback option

Comment

Highlight any SQL, PL/SQL, or fixed-width text and click the <code> button
 (will never be published or used to spam you)

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