Converting second timestamp data records into minute records

I have a table that has trading transactions and contains a timestamp field and a ASK price each time the price changes (tick data).
The timestamp has the format:1/1/2009 10:42:00.000000 PM
The records are sequential but do not have a consistent gap. some are the same time, some are 2 seconds apart, some 10 seconds apart.
I wish to load  the beginning ask price and the ending ask price for each minute into another table (MIN_Table)

Then I will want to load the beginning and ending ask price for each hour into a third table (Hour_table)

How do I do this in SQL?
I know how to do an Append from one table to another, but How do I have it go through all the records and take the beginning and ending prices for the specific Timestamp time frame?

This is really twisting my head.
GNOVAKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
First of all I will add a column that has DATE type.  Date and time should always be saved using the DATE type - it is easy to convert it to a specific format later on, but storing it as other than DATA makes it inefficient to make queries on the column.  To populate that column:

update tablename set new_date_column=to_date(existing_column, 'MM/DD/YYYY HH:MI:SS.FF6 AM'); --see http://www.techonthenet.com/oracle/functions/to_date.php

Now that you have your DATE column, it should be easier to do what you want:

select date_hour, sum(starting_hour_ask_price), sum(ending_hour_ask_price)
from
( select to_char(a.new_date_column,'MM-DD-YYYY HH24:00') as date_hour,
     case when new_date_column=a.start_hour then a.ask_price_column else 0 end case as starting_hour_ask_price,
     case when new_date_column=a.end_hour then a.ask_price_column else 0 end case as ending_hour_ask_price
   from table_name a,
     (select to_char(new_date_column,'MM-DD-YYYY HH24:00'), min(new_date_column) start_hour, max(new_date_column) end_hour
     from table_name group by to_char(new_date_column,'MM-DD-YYYY HH24:00')) b
   where a.new_date_column=b.start_hour
   or a.new_date_column=b.end_hour
)
group by date_hour;

Note: untested
0
johanntagleCommented:
Sorry, I forgot you also wanted it in minutes:  Then just change the to_char calls in the previous SQL to to_char(a.new_date_column,'MM-DD-YYYY HH24:MI')

Then to load data to separate tables, then just prepend the SQL's with a

INSERT INTO MIN_TABLE (col1, col2, col3)
0
Shaju KumbalathDeputy General Manager - ITCommented:
   select to_char(time_stamp_column,'dd/mm/yyyy hh24:mi'),max(price) KEEP (DENSE_RANK first ORDER BY time_stamp_column),
max(price) KEEP (DENSE_RANK last ORDER BY time_stamp_column)
   from your_table  
    group by to_char(time_stamp_column,'dd/mm/yyyy hh24:mi');


    select to_char(time_stamp_column,'dd/mm/yyyy hh24:hh24'),max(price) KEEP (DENSE_RANK first ORDER BY time_stamp_column),
max(price) KEEP (DENSE_RANK last ORDER BY time_stamp_column)
   from your_table  
    group by to_char(time_stamp_column,'dd/mm/yyyy hh24:hh24');
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark GeerlingsDatabase AdministratorCommented:
You don't need to add a "date" column to the table if it currently has an actual "timestamp" column.  Older Oracle database versions only supported four data types: CHAR, VARACHAR2, NUMBER and DATE (and "DATE" columns only stored values to the nearest whole second).  But, Oracle added a TIMESTAMP datatype in the Oracle10 database.  This stores date/time values with much greater precision.  By default, these are to the millionth of a second (6 decimal digits) but these can optionally be down to the billionth of a second (9 decimal digits).

You indicated that your timestamp column "has the format:1/1/2009 10:42:00.000000 PM".  That may not be the complete truth.  If this really is a TIMESTAMP datatype, that may be the way your system is currently configured to display TIMESTAMP values, but Oracle offers *MANY* different ways to display DATE and/or TIMESTAMP values.  The actual values in both cases are stored internally as a proprietary type of number value, but Oracle presents them to us in characters strings that can easily be adjusted.
0
johanntagleCommented:
Markgeer is correct if the column is really of type timestamp - I interpreted the Gnovak's words as having a timestamp column that is of varchar2 type.
0
GNOVAKAuthor Commented:
sorry for the confusion. The dataype for the column is TIMESTAMP. I copied a sample via TOAD.
I'll give the shajukg query a try as well as the other. Curious to see if there is a speed difference as well.
As an aside, someone mentioned that "Partitioning" may be an answer. I have also heard that partitioning is not a good practice in some cases.
I have never utilized partitioning.
Any comments on that?  
0
Mark GeerlingsDatabase AdministratorCommented:
Partitioning is an excellent option in some cases.  It works especially well with historical data (like transactions) that get written once, and never updated or deleted and that include a date or timestamp column that records the time each record was created.

Tables with data like this can be partitioned on the column that includes this date/time value.  I usually like to create a separate partition for each month, but this could be done by day, week, 2-month period, quarter, year, or any other time interval that makes sense for the application.  After a particular time period is closed, this partition can then be compressed and the index(es) for the partition rebuilt.  This often yields a 20-50% reduction in space required for historical data, and that can translate into almost that same speed increase for queries of this data, since the system has fewer physical blocks of data to read, fit into cache buffers, etc.

Indexes on tables like this should be "locally-partitioned" (that is, partitioned to match the way the table is partitioned) so that when you do the compression of each historical period after it closes, you only need to rebuild the part of each index that corresponds to the partition that was just compressed.  You don't want to have to rebuild entire index(es), because that will make the table unusable by the application.
0
GNOVAKAuthor Commented:
The query works great for obtain the min/max  but how do I obtain the first value and the last value?
0
johanntagleCommented:
Did you try my query?  Just ignore the part about converting to date type.
0
Shaju KumbalathDeputy General Manager - ITCommented:
there is no impact MAX function in the query posted by me as it sorts all the records based timestamp and takes the dense rank. if there are multiple rows in the first rank then only group function makes impact.
0
GNOVAKAuthor Commented:
The Data looks like this for 1 minute:
ID      TICKTIME                     ASK
1      1/1/09 10:42 PM      0.7063                          <<<open
2      1/1/09 10:42 PM      0.70625
3      1/1/09 10:42 PM      0.70635
4      1/1/09 10:42 PM      0.70635
5      1/1/09 10:42 PM      0.70635
6      1/1/09 10:42 PM      0.70635
7      1/1/09 10:42 PM      0.7063
8      1/1/09 10:42 PM      0.7063
9      1/1/09 10:42 PM      0.70635                          <<<close

I'm looking to generate the following for the minute 10:42
TICKTIME               HIGH        LOW         OPEN         CLOSE
1/1/09 22:42     0.70635  0.70625     0.7063      0.70635

I run the query:
select to_char(ticktime,'dd/mm/yyyy hh24:mi'),max(ask) KEEP (DENSE_RANK first ORDER BY ticktime),
max(ask) KEEP (DENSE_RANK last ORDER BY ticktime)
   from Ticktemp
    group by to_char(ticktime,'dd/mm/yyyy hh24:mi');

I get:
TO_CHAR(...)               MAX(ASK)KEEP(DENSE_RANKFIRSTORDERBYTICKTIME)      MAX(ASK)KEEP(DENSE_RANKLASTORDERBYTICKTIME)
01/01/2009  22:42     0.70635                                                                                 0.70635    


suggestions?
0
Mark GeerlingsDatabase AdministratorCommented:
Getting the High and Low for each minute is easy.  You don't need analytic functions for those.  This relatively simple query can do that:

select to_char(ticktime,'dd/mm/yyyy hh24:mi'),max(ask) "HIGH", min(ask) "LOW"
   from Ticktemp
group by to_char(ticktime,'dd/mm/yyyy hh24:mi');

If you use SQL*Plus to test this, you will need to set your NUMFORMAT appropriately so you see the results with the decimal precision you want, for example:
set NUMFORMAT 9,99999

or:
column HIGH format 9,99999;
column LOW format 9,99999;

If you also need to support higher values, use a format more like this: "999.99999" or this: "9,999.99999".

I have to admit that I'm not an expert with Oracle's analytical functions.  I found a good example on-line though today at: http://orafaq.com/node/55, that I hope to spend some time with.  I'll try to figure out how to use both a group operator and analytics in the same statement.
0
Shaju KumbalathDeputy General Manager - ITCommented:
are u storing "seconds" values also ?
if it is zero then we can not determine which one is the first record
0
Shaju KumbalathDeputy General Manager - ITCommented:
SELECT   TO_CHAR (ticktime, 'dd/mm/yyyy hh24:mi'),
           Min(ASK) Min,
           Max(ASK) Max,
           MAX (ask) KEEP (DENSE_RANK FIRST ORDER BY ticktime) Open,
           MAX (ask) KEEP (DENSE_RANK LAST ORDER BY ticktime)  Close
    FROM   Ticktemp
GROUP BY   TO_CHAR (ticktime, 'dd/mm/yyyy hh24:mi');


If ur storing seconds and hundredth of second as zero in the data u will not able to dettermine which is the first record
0
Shaju KumbalathDeputy General Manager - ITCommented:
or if u r identifieng the first and last transaction using ID then change " ORDER BY " clause from ticktime to ID
0
Shaju KumbalathDeputy General Manager - ITCommented:
ie

  SELECT   TO_CHAR (ticktime, 'dd/mm/yyyy hh24:mi'),
           Min(ASK) Min,
           Max(ASK) Max,
           MAX (ask) KEEP (DENSE_RANK FIRST ORDER BY id) Open,
           MAX (ask) KEEP (DENSE_RANK LAST ORDER BY id)  Close
    FROM   Ticktemp
GROUP BY   TO_CHAR (ticktime, 'dd/mm/yyyy hh24:mi');
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GNOVAKAuthor Commented:
Yes - exactly !
I assign the ID based on the order recieved as the data is aquired in order. The seconds do not appear to be accurate.
So I'll use ID and that should work out great.
Thanks so much for the info - I have to check on what KEEP(DENSE_RANK..) does. Not familiar with that.

0
GNOVAKAuthor Commented:
Thanks everyone for helping out. The thread and the solutions were great.
0
GNOVAKAuthor Commented:
How can I do the above for say 15 Min, or 4 hours?
0
GNOVAKAuthor Commented:
The time frames I'm looking for are: 5 min, 15 min, 30 min, 1 hour, 4 hour, 1 day , 1 week.
Are these possible using this type of query?
0
GNOVAKAuthor Commented:
Just found out after doing some research that the above DOESNT work,
It appears that it gives me every minute of every 1st day of the month if I use: GROUP BY   TO_CHAR (ticktime, 'dd/mm/yyyy hh24:mi');

I'm going to try the first query tomorrow.
0
GNOVAKAuthor Commented:
CORRECTION - IT DOES WORK
But still trying to find the 5, 15, 30 min & 4hour, day 1 week solutions
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.