• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Rob Spoor
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Junilu Lacar
  • Tim Cooke
Saloon Keepers:
  • Tim Holloway
  • Piet Souris
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Frits Walraven
  • Himai Minh

connecting to an Apache Derby database to extract a record number based on a particular key value

 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi again

I wish at  a certain point to take the Date and Time values, which represent the key in an Apache database table, use that key to find the appropriate record in the table and come away with it's record number

How do I code that please ?

Bob M

Table is called Prices
1st column is Date
2nd Column is Time
these two fields make a unique key
 
Rob Spoor
Marshal
Posts: 22409
121
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What do you mean with "record number"? Records don't have numbers, they have keys (primary, unique, etc.) and columns. Any number that you see before a record (or row) in tools like SQL Server Management Studio or DBeaver are not part of the record. They are simply an incremental counter added by the UI to each row. It's like retrieving the records into an ArrayList; these values are the list indexes +1. Those numbers are dependent on the ordering.

If you have a column called number, simply do a query to retrieve the column, using the date+time columns in your WHERE clause. It doesn't matter if that columns is an autoincrement primary key (which means the date+time columns are a secondary key) or any other column.
 
Tim Holloway
Saloon Keeper
Posts: 24212
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There is exactly one SQL DBMS that I know of where the term "record number" was valid and that was FoxPro. FoxPro was a descendent of DBase, which kept data tables in a very simple format and the "record number" was a direct indication of the order in which the row had been added to the table.

FoxPro was also single-user. Which is why it never made it as a webapp server database.

More sophisticated databases use more complex schemes of storing and locating records, plus if multiple clients are adding records at the same time, the "record number" would be impossible to reliably calculate.

At the extreme end, Oracle has used a "record number" that was actually a triplet of values and not even a single number. PostgreSQL has made me suspecct that it randomly re-organizes records just for giggles.

So the long and short of it is that any "record number" is more likely to be an internal record ID which may or may not be a simple numeric value, may or may not be visible to application code, and will almost certainly not be the same thing if you migrate from one database to another like Google did recently.

The only reliable sequencing for records is when you do a full ORDER BY retrieval where the complete set of ordering fields is unique - anything from a primary key to some unique combination of secondary keys and/or basic fields. If anything is left to flop around loose, it likely will, sooner or later.

SQL is essentially a parallel language, so even within an ordered SELECT you probably won't have a sequential selection order value option (again, this depends on what database you are using). So about the closest rhing you can get therefore to a true "record number" is to cursor your way through an ordered SELECTion.
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK

At the moment I use an array of Prices - say a hundred
when I seek a particular Price (backwards) I iterate thru the Prices starting with the current Price (current Date and Time) until I find my record with the Date and Time I am seeking

I take the array index of the current Price record and subtract the array index of the past Price record to give me a Time Difference (in minutes) as each record is collected each minute

However, whatever array size I care to use, sooner or later, I will be seeking a past record which is further back that the array size............

So I am trying to get rid of temporary arrays and simply use the table of Price records to seek and retrieve my past Price record Date and Time

Then I need to work out a Time Difference

Bob M
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
We may be heading to a situation where I have two dates and time and you may think it is easy to subtract one from the other to get a Time Difference

BUT weekends and holidays - no trading - so it gets tricky to get a correct Time Difference

e.g. 2159 Friday and 2205 Sunday would yield 6 for a Time Difference

Bob M
 
Tim Holloway
Saloon Keeper
Posts: 24212
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There is something fuzzy about the process you are describing, but what it sounds like would almost certainly require a stored procedure and a custom function definition.

The stored procedure would be to get the difference between successive record timestamps. although you might be able to avoid that using some really kinky SQL. Then you'd need a custom date-differential function in order to handle the specific gaps of your particular sparse calendar.

The other option is to simply do the calculations in Java code as you walk through the ResultSet for a query that starts from the desired date and returns records in descending timestamp sequence.
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Tim

your second option is perfect...........................
so code to :-

Table has 4 fields date, time, number (Incremental) and price - key =Date+Time

get latest (current) record after sort DECR

while key does not equal key sought
             step back one record

when key equals sought key, stop and grab Date, Time and number

By subtracting this number from the total number of records in the table will give me my Time Difference

Bob M
 
Carey Brown
Saloon Keeper
Posts: 8457
71
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bob Matthews wrote:We may be heading to a situation where I have two dates and time and you may think it is easy to subtract one from the other to get a Time Difference BUT weekends and holidays - no trading - so it gets tricky to get a correct Time Difference

I think a custom tradingTimeDifference() method would be doable. Certainly calculating weekdays vs weekends shouldn't be too difficult and other non-trading days can be handled with a lookup table. Though a bit tricky I think, once debugged, that it would be more efficient and less error prone than your other proposal. What would happen, for instance, if something failed to record for a few minutes? That would throw off hundreds if not thousands of difference computations.

What about trading days that close early?

e.g. 2159 Friday and 2205 Sunday would yield 6 for a Time Difference

Sorry, don't quite get this.
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Carey

I think Tim has the solution to my problem.................

At Dukascopy, the weekend typically goes from 2200 Friday to 2200 Sunday
thus my example of a 6 minute Time Difference

Trading stops at weekends and on public holidays but that's about it

Bob M
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Tim

Looking for something like this...............
Unsure of the code for the 'while' loop though

Table PRICE : DATE and TIME [key] and PRICE


// grab DATE, TIME, PRICE from current record
// by subtracting counter from total number of records in PRICE table I will have my Time Difference
// together with DATE and TIME of that record
 
Tim Holloway
Saloon Keeper
Posts: 24212
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well you have double-spaced and not indented and it makes the code very hard to read. Also, I do recommend you avoid "SELECT *". It has been known to bite people (including me).
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Tim

Looking for something like this...............
Unsure of the code for the 'while' loop though

Table PRICE : DATE and TIME [key] and PRICE


I have used 'SELECT' always and so am unsure of alternative approach
 
Campbell Ritchie
Marshal
Posts: 73766
332
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please don't edit messages after they have been replied to; that makes it difficult to know what exactly was replied to.
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Apologies
 
Campbell Ritchie
Marshal
Posts: 73766
332
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Apologies accepted
 
Tim Holloway
Saloon Keeper
Posts: 24212
167
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK. I still don't understand exactly what you are looking to do, but I'm going to act like you're trying to find the amount of trading time since a security had a given price.

Some real-world caveats, however:

1. There exists a thing called "after-hours trading". Securities are often sold on multiple exchanges and even off-exchange. You'll see a lot of this for big-name companies like Tesla. So you can't really assume as a general rule how long the gap is in trading time units, since your trading time units don't allow for trades done "in the cracks" between trading times.

2. Securities prices don't neatly step from one value to another. They may skip up and down over wide ranges. So before testing for a given last-trade time for a security be sure you know the exact price in question (and don't store it in a floaring-point value!!!)

Given that, let's count back trade entries for your given price to count how many "clock units" have passed and what amount of time that is. Something like this:


The process of converting "record_count" to a time differential within the constraints of your custom trading calendar I leave as an exercise for you. In a real-world app, I'd probably also capture the record's primary key in case I wanted to look at it in more detail.
 
Bob Matthews
Master Rancher
Posts: 594
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Tim

Thank you for your reply..............

My situation is really straight forward
I record a record each minute while trading is open (i.e. excluding weekends and holidays)

Each record has a date, time and price
That's it!

I keep a record of when an 'event' begins i.e. I keep a date, time and price

Further down the track I have a current date, time and price - when that same 'event' ends

At this stage I wish to calculate a time difference NOT based on differences between dates and times BUT between the number of records I have from event begin to event end

Your code has given me something to work with - thanks

Bob M

p.s. my prices are stored in a derby table as decimal (8,5)
 
What kind of corn soldier are you? And don't say "kernel" - that's only for this tiny ad:
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic