In Progress

Stored procedure PostgreSQL (pgSQL)

What I have; a system that get forex quotes from a server, and stores the

values in a sql database. Example of table in the database:

table AUDUSD

id, volume, bid, ask, date

1, 10, 0.896, 0.897, 2014-02-28 10:18:00

2, 20, 0.897, 0.896, 2014-02-28 10:18:30

3, 30, 0.898, 0.895, 2014-02-28 10:19:00

4, 40, 0.899, 0.894, 2014-02-28 10:19:30

5, 50, 0.898, 0.893, 2014-02-28 10:20:00

6, 60, 0.897, 0.892, 2014-02-28 10:20:30

7, 70, 0.896, 0.891, 2014-02-28 10:21:00

8, 80, 0.895, 0.892, 2014-02-28 10:21:30

9, 90, 0.894, 0.893, 2014-02-28 10:22:00

10, 80, 0.893, 0.894, 2014-02-28 10:22:30

11, 70, 0.892, 0.895, 2014-02-28 10:23:00

12, 60, 0.891, 0.897, 2014-02-28 10:23:30

13, 50, 0.891, 0.896, 2014-02-28 10:24:00

What I need:

i) Request the last value of a period X, where X could be a 30 seconds, 1 minute, 3 Minutes, 1 hour and 4 hours.

ii) Request group of values sorted by oldest value to newer value, of certain period X, where X could be a

30 seconds, 1minute, 3 Minutes, 1 hour and 4 hours.

* The answer should include the Open, High, Low and Close of each period. This is calculate using a temp

field "last", who is the average of the bid and ask fields.

See example to see how the values have to be calculated

Example:

- Select lastValue table AUDUSD in with Period 1M (1 minute)

The result has to be:

Open, High, Low, Close, volume, bid, ask, date

0.8935, 0.894, 0.8935, 0.894, 130 0.8915, 0.8956, 2014-02-28 10:23:00

How the values were calculated:

a) We have to check when the last minute was completed. In this case, the minute that was completed

was the one that finished before the last value displayed. So, the values to be used are the ones

with the id 11 and 12

10, 0.894, 80, 0.893, 0.894, 2014-02-28 10:22:30

11, 0.895, 70, 0.892, 0.895, 2014-02-28 10:23:00

12, 0.896, 60, 0.891, 0.897, 2014-02-28 10:23:30

13, 0.896, 50, 0.891, 0.896, 2014-02-28 10:24:00

b) So,

temp_lastValue = (bid+ask)/2

Open: First value of temp_lastValue

Hight: Highest value of temp_lastValue of group

Low: Lowest value of temp_lastValue of group

Close: Last value of temp_lastValue

Volume: Sum of the values in the group

bid: Average of the bid values of the group

ask: Average of the ask values of the group

date: First date of the group

I can write the logic in C++, if it needed

I need to clarify that I need the logic and the calculations done in the database, as a stored procedure. I've already have the logic implemented in Java, but it's too slow and a waste of resources.

Information about stored procedure in PostgreSQL: http://www.postgresql.org/docs/9.3/static/plpgsql.html and http://www.postgresqltutorial.com/introduction-to-postgresql-stored-procedures/


Questions:
a) How do you know if the last minute is completed?

Answer: To check the last minute completed (if 1 minute was requested), you must check the "date" field in the table:

The last value in the table, for the field "date" in the example is 2014-02-28 10:24:00, so, the current minute is "24", (more records can be coming with the time 2014-02-28 10:24:XX).

So, if "24" is the current minute, the previous "completed" minute is "23", so, in this case we should get all the fields that have the minute 23 in it. In the example, these are the records 11 and 12

b) How many rows does the second request return?

Answer: For the second request, I'll pass the amount of points needed, like: Select 30 values table AUDUSD with Period 1M (1 minute)

c) What version of PostgreSQL I'm using?

PostgreSQL 9.3.3

Thanks

Skills: PostgreSQL

See more: postgresql stored procedure, what is a temp, high volume database, postgresql c++, last minute bid, postgresql procedure, postgresql sql, database procedure, postgresql check check, postgresql table, database forex, low forex, calculate volume, forex server, write stored procedure sql, sql oldest date, sql calculated values, postgresql sorting stored procedure, low forex system, sql stored procedure table, sql table procedure, auto calculate display field using java, postgresql function stored, sql procedure, postgresql function stored procedure

About the Employer:
( 1 review ) Sydney, Australia

Project ID: #5501444

Awarded to:

krokysl

Hi, I am interested in working on your project. Forgive me if I'm wrong: doesn't column 'ask' in your result sample have to be 0.896 instead 0.8956? Open, High, Low, Close, volume, bid, ask, date 0.8935, 0.8 More

$230 AUD in 6 days
(1 Review)
2.8

10 freelancers are bidding on average $206 for this job

WebLYN

Hi Dave, I have seen your project requirement and surely tell you that we have an experience of this kind of project development. We are best in outsourcing business since 2004 and we have 250+ highly skilled develo More

$222 AUD in 10 days
(0 Reviews)
0.0
chinbochen

Hello, I'm a Java, J2EE application developer with over 10 years experience. I have worked with PostgreSQL in many projects. I'm very happy to get this project done for you. Contact me if you are interested. More

$242 AUD in 3 days
(0 Reviews)
0.0
elboukharimu

Aucune proposition n'a encore été fournie.

$200 AUD in 7 days
(0 Reviews)
0.0
neel4501

I have worked in PostGreSQL datbase. We also developed the tool that monitor the postgresql database in remote location for that we have used PL/SQL so i think i can write this logic.

$388 AUD in 4 days
(0 Reviews)
0.0
karouiachraf

Hi, I have more than 7 years experience in data analysis please contact me to have more details about your request. Best Regards, Achraf Karoui

$155 AUD in 3 days
(0 Reviews)
0.0
deena4ps

A proposal has not yet been provided

$100 AUD in 7 days
(0 Reviews)
0.0
silvioq

Hi, azcar, I'm Silvio from Argentina. I have a simple question about your project ... You say "We have to check when the last minute was completed." How you know if the last minute is completed? Completed mi More

$120 AUD in 3 days
(0 Reviews)
0.0
zkutch

Hello. More 20 years programming experience. Regards. ---------------------------------------------------------------------------------------------------------------------------------------------------

$180 AUD in 7 days
(0 Reviews)
0.0
scicvara

After reading your job description, I am confident that the experience that I have acquired during my professional career are a perfect match for the work that you have posted. I have many years database and Java e More

$222 AUD in 3 days
(0 Reviews)
0.0