?
Solved

Converting second timestamp data records into minute records

Posted on 2011-10-17
22
Medium Priority
?
1,056 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:GNOVAK
  • 9
  • 6
  • 4
  • +1
22 Comments
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 500 total points
ID: 36983618
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
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 500 total points
ID: 36983635
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
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 36985234
   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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 36985652
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
 
LVL 24

Expert Comment

by:johanntagle
ID: 36985838
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
 

Author Comment

by:GNOVAK
ID: 36988801
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 36989111
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
 

Author Comment

by:GNOVAK
ID: 36992248
The query works great for obtain the min/max  but how do I obtain the first value and the last value?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36993153
Did you try my query?  Just ignore the part about converting to date type.
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 37000019
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
 

Author Comment

by:GNOVAK
ID: 37028801
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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 400 total points
ID: 37030756
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
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 37036713
are u storing "seconds" values also ?
if it is zero then we can not determine which one is the first record
0
 
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 1100 total points
ID: 37036737
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
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 37036744
or if u r identifieng the first and last transaction using ID then change " ORDER BY " clause from ticktime to ID
0
 
LVL 15

Accepted Solution

by:
Shaju Kumbalath earned 1100 total points
ID: 37036748
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
 

Author Comment

by:GNOVAK
ID: 37037644
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
 

Author Closing Comment

by:GNOVAK
ID: 37037660
Thanks everyone for helping out. The thread and the solutions were great.
0
 

Author Comment

by:GNOVAK
ID: 37067118
How can I do the above for say 15 Min, or 4 hours?
0
 

Author Comment

by:GNOVAK
ID: 37067172
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
 

Author Comment

by:GNOVAK
ID: 37067206
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
 

Author Comment

by:GNOVAK
ID: 37069191
CORRECTION - IT DOES WORK
But still trying to find the 5, 15, 30 min & 4hour, day 1 week solutions
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question