Home » SQL & PL/SQL » SQL & PL/SQL » Calculating the difference of timestamps in seconds (Oracle 11g, 11.2.0.1.0, Windows Server 2008)
| Calculating the difference of timestamps in seconds [message #573681] |
Wed, 02 January 2013 05:29  |
 |
marcopo
Messages: 24
Registered: December 2012
|
Junior Member |
|
|
Hello Community,
I have to create the following table. The fields Trend_Date, Price and Trend are already given. I have to calculate the field permanently and to insert the value in this permanent table.
Fields:
The field price belong to the value of a product during the trade.
The field trade_date belongs to the moment of the trade.
The field trend belongs to the future behavior of the the price. Here, the price of the present moment is compared to the following price (possible characteristics: 'UP', 'DOWN', 'STABLE').
The field permanently belongs to the time (in seconds) how long the value of the field Trend_Date (depending on the price) is still true.
For example:
Row 1: The trend in row 1 is 'UP' and it has a price of '11'. Until row 3 this remains true (the price is greater or equal to 11). In this case, the difference between row 1 and row 3 are 9801 (rounded) seconds.
Row 2: The trend in row 2 is 'DOWN' and it has a price of '12'. This remains true till to the end (the price is never greater than 12) In this case, the difference between row 2 and row 11 are 97346 (rounded) seconds. To calculate the 97346 seconds the field has to consider that between row 2 and row 11 are two days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (2*46800) 93600.
-> 190945-93600 = 97346s
Row 6: The trend in row 6 is 'UP' and it has a price of '5'. This remains true till to the end (the price is never smaller than 5) In this case, the difference between row 6 and row 11 are 65729 (rounded) seconds. To calculate the 65729 seconds the field has to consider that between row 65729 and row 11 are one days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (1*46800) 46800.
-> 112528-46800 = 65729s
Row 9: The trend in row 9 is 'STABLE' and it has a price of '8'. Until row 10 this remains true (the price is equal to 8 ). In this case, the difference between row 9 and row 10 is 14418 (rounded) seconds.
Row 11: Is empty because there are no values to compare.
Example Table
TRADE_DATE --PRICE --TREND --permanently
02.01.13 11:21:42,720000000 --11 --UP --9801
02.01.13 12:44:03,236000000 --12 --DOWN --97346
02.01.13 14:05:03,845000000 --11 --DOWN --92485
02.01.13 16:21:04,345000000 --8 --DOWN --23648
02.01.13 17:25:43,470000000 --7 --DOWN --14717
03.01.13 10:31:00,376000000 --5 --UP --65729
03.01.13 11:55:12,798000000 --8 --UP --41350
03.01.13 16:32:47,642000000 --10 --DOWN --44201
04.01.13 08:24:05,486000000 --8 --STABLE --14418
04.01.13 12:24:23,189000000 --8 --DOWN --19326
04.01.13 17:46:29,123000000 --7 --NULL --0
CREATE TABLE "INFODB"."TEST2" ("TRADE_DATE" TIMESTAMP (6),"PRICE" NUMBER(5,2),
"Permanently" NUMBER,"TREND" VARCHAR2(20 BYTE)) TABLESPACE "USERS" ;
INSERT INTO "INFODB"."test2"
(trade_date,
price,
"permanently",
trend)
VALUES (To_timestamp('02.01.13 11:21:42,720000000',
'DD.MM.RR HH24:MI:SS,FF'),
'11',
'9801',
'UP');
INSERT INTO "INFODB"."test2"
(trade_date,
price,
"permanently",
trend)
VALUES (To_timestamp('02.01.13 12:44:03,236000000',
'DD.MM.RR HH24:MI:SS,FF'),
'12',
'97346',
'DOWN');
INSERT INTO "INFODB"."test2"
(trade_date,
price,
"permanently",
trend)
VALUES (To_timestamp('02.01.13 14:05:03,845000000',
'DD.MM.RR HH24:MI:SS,FF'),
'11',
'92485',
'DOWN');
INSERT INTO "INFODB"."test2"
(trade_date,
price,
"permanently",
trend)
VALUES (To_timestamp('02.01.13 16:21:04,345000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8',
'23648',
'DOWN');
INSERT INTO "INFODB"."test2"
(trade_date,
price,
"permanently",
trend)
VALUES (To_timestamp('02.01.13 17:25:43,470000000',
'DD.MM.RR HH24:MI:SS,FF'),
'7',
'14717',
'DOWN');
INSERT INTO "INFODB"."test2"
(trade_date,
price,
"permanently",
trend)
VALUES (To_timestamp('03.01.13 10:31:00,376000000',
'DD.MM.RR HH24:MI:SS,FF'),
'5',
'65729',
'UP');
INSERT INTO "INFODB"."test2"
(trade_date,
price,
"permanently",
trend)
VALUES (To_timestamp('03.01.13 11:55:12,798000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8',
'41350',
'UP');
INSERT INTO "INFODB"."test2"
(trade_date,
price,
"permanently",
trend)
VALUES (To_timestamp('03.01.13 16:32:47,642000000',
'DD.MM.RR HH24:MI:SS,FF'),
'10',
'44201',
'DOWN');
INSERT INTO "INFODB"."test2"
(trade_date,
price,
"permanently",
trend)
VALUES (To_timestamp('04.01.13 08:24:05,486000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8',
'14418',
'STABLE');
INSERT INTO "INFODB"."test2"
(trade_date,
price,
"permanently",
trend)
VALUES (To_timestamp('04.01.13 12:24:23,189000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8',
'19326',
'DOWN');
INSERT INTO "INFODB"."test2"
(trade_date,
price,
"permanently",
trend)
VALUES (To_timestamp('04.01.13 17:46:29,123000000',
'DD.MM.RR HH24:MI:SS,FF'),
'7',
'0',
'NULL');
Anyone an idea?
Thanks in advance
[Updated on: Wed, 02 January 2013 05:50] by Moderator Report message to a moderator |
|
|
|
| Re: Calculating the difference of timestamps in seconds [message #573686 is a reply to message #573681] |
Wed, 02 January 2013 05:57  |
 |
Michel Cadot
Messages: 54724
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I don't understand what is the input and what is the output.
The table in the test case is the input or the result one?
In addition:
1/ Do NOT use schema name in your test case, we have not the same schemas
2/ Do NOT use tablespace and storage parameters, we have not the same tablespaces
3/ Do NOT use mixed case names (remove the "), you have a high risk of error:
SQL> CREATE TABLE "TEST2" ("TRADE_DATE" TIMESTAMP (6),"PRICE" NUMBER(5,2),
2 "Permanently" NUMBER,"TREND" VARCHAR2(20 BYTE))
3 /
Table created.
SQL> INSERT INTO "test2"
2 (trade_date,
3 price,
4 "permanently",
5 trend)
6 VALUES (To_timestamp('02.01.13 11:21:42,720000000',
7 'DD.MM.RR HH24:MI:SS,FF'),
8 '11',
9 '9801',
10 'UP');
INSERT INTO "test2"
*
ERROR at line 1:
ORA-00942: table or view does not exist
Regards
Michel
|
|
|
|
| Re: Calculating the difference of timestamps in seconds [message #573696 is a reply to message #573686] |
Wed, 02 January 2013 06:49 |
|
marcopo
Messages: 24
Registered: December 2012
|
Junior Member |
|
|
Hello,
the fields trade_date, price, and trend are given. The input to calculate the difference of seconds is the difference between two dates of trade_date (depends on the price and trend). The output is the field permanently. I just added the results manually to show the expected results.
The field price belong to the value of a product during the trade.
The field trade_date belongs to the moment of the trade.
The field trend belongs to the future behavior of the the price. Here, the price of the present moment is compared to the following price (possible characteristics: 'UP', 'DOWN', 'STABLE').
The field permanently belongs to the time (in seconds) how long the value of the field Trend (depending on the price (not just the following) is still true.
CREATE TABLE TEST2 (TRADE_DATE TIMESTAMP (6), PRICE NUMBER(5,2), Permanently NUMBER, TREND VARCHAR2(20 BYTE));
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('02.01.13 11:21:42,720000000',
'DD.MM.RR HH24:MI:SS,FF'),
'11',
'9801',
'UP');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('02.01.13 12:44:03,236000000',
'DD.MM.RR HH24:MI:SS,FF'),
'12',
'31617',
'DOWN');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('02.01.13 14:05:03,845000000',
'DD.MM.RR HH24:MI:SS,FF'),
'11',
'26757',
'DOWN');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('02.01.13 16:21:04,345000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8',
'18596',
'DOWN');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('02.01.13 17:25:43,470000000',
'DD.MM.RR HH24:MI:SS,FF'),
'7',
'14717',
'DOWN');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('03.01.13 10:31:00,376000000',
'DD.MM.RR HH24:MI:SS,FF'),
'5',
'21707',
'UP');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('03.01.13 11:55:12,798000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8',
'41350',
'UP');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('03.01.13 16:32:47,642000000',
'DD.MM.RR HH24:MI:SS,FF'),
'10',
'44201',
'DOWN');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('04.01.13 08:24:05,486000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8',
'14418',
'STABLE');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('04.01.13 12:24:23,189000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8',
'19326',
'DOWN');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('04.01.13 17:46:29,123000000',
'DD.MM.RR HH24:MI:SS,FF'),
'7',
'0',
'NULL');
Best Regards
[Updated on: Wed, 02 January 2013 06:52] Report message to a moderator |
|
|
|
| Re: Calculating the difference of timestamps in seconds [message #573721 is a reply to message #573681] |
Wed, 02 January 2013 14:22 |
Solomon Yakobson
Messages: 1443
Registered: January 2010
|
Senior Member |
|
|
marcopo wrote on Wed, 02 January 2013 06:29Anyone an idea?
Just for fun:
with t1 as (
select test2.*,
row_number() over(order by trade_date) rn
from test2
),
t2 as (
select a.*,
(
select b.trade_date
from t1 b
where connect_by_isleaf = 1
start with b.rn = a.rn
connect by b.rn = prior b.rn + 1
and (
(
a.trend = 'UP'
and
prior b.price >= a.price
)
or (
a.trend = 'STABLE'
and
prior b.price = a.price
)
or (
a.trend = 'DOWN'
and
prior b.price <= a.price
)
)
) end_trade_date
from t1 a
),
t3 as (
select t2.*,
(end_trade_date - cast(trunc(end_trade_date) + 7 / 24 as timestamp)) +
numtodsinterval((trunc(end_trade_date) - trunc(trade_date)) * 11,'hour') -
(trade_date - cast(trunc(trade_date) + 7 / 24 as timestamp)) diff
from t2
)
select trade_date,
price,
permanently,
trend,
((date '1-1-1' + diff) - date '1-1-1') * 24 * 60 *60 my_permanently
from t3
order by rn
/
TRADE_DATE PRICE PERMANENTLY TREND MY_PERMANENTLY
------------------------------ ----- ----------- ------ --------------
02-JAN-2013 11.21.42.720000 AM 11 9801 UP 17961
02-JAN-2013 12.44.03.236000 PM 12 31617 DOWN 97345
02-JAN-2013 02.05.03.845000 PM 11 26757 DOWN 92485
02-JAN-2013 04.21.04.345000 PM 8 18596 DOWN 40303
02-JAN-2013 05.25.43.470000 PM 7 14717 DOWN 19769
03-JAN-2013 10.31.00.376000 AM 5 21707 UP 65728
03-JAN-2013 11.55.12.798000 AM 8 41350 UP 60676
03-JAN-2013 04.32.47.642000 PM 10 44201 DOWN 44021
04-JAN-2013 08.24.05.486000 AM 8 14418 STABLE 33743
04-JAN-2013 12.24.23.189000 PM 8 19326 DOWN 19325
04-JAN-2013 05.46.29.123000 PM 7 0 NULL 0
11 rows selected.
SQL>
SY.
|
|
|
|
| Re: Calculating the difference of timestamps in seconds [message #573725 is a reply to message #573721] |
Wed, 02 January 2013 16:21 |
|
marcopo
Messages: 24
Registered: December 2012
|
Junior Member |
|
|
Hi,
thanks for your replay. I checked the code. I did an error in the discription, sorry. The time between two days are not 7 hours. The time is 13 hours. The example table is still correct. I used (13*60*60) 46800 seconds. In addition, in the first row it looks like that the code takes one row to much.
I just add the example table with the intermediate steps.
Trade_Date-- Price-- Trend -- interim stage (interim stage- (days_between*46800)-- Result
02.01.2013 11:21:43-- 11-- UP -- 9801,125-- 9801,1
02.01.2013 12:44:03-- 12-- DOWN -- 190945,764-- 97345,764
02.01.2013 14:05:04-- 11-- DOWN -- 186085,155-- 92485,155
02.01.2013 16:21:04-- 8-- DOWN -- 70448,453-- 23648,453
02.01.2013 17:25:43-- 7-- DOWN -- 61516,906-- 14716,906
03.01.2013 10:31:00-- 5-- UP -- 112528,624-- 65728,624
03.01.2013 11:55:13-- 8-- UP -- 88150,391-- 41350,391
03.01.2013 16:32:48-- 10-- DOWN -- 90821,358-- 44021,358
04.01.2013 08:24:05-- 8-- STABLE -- 14417,703-- 14417,703
04.01.2013 12:24:23-- 8-- DOWN -- 19325,811-- 19325,811
04.01.2013 17:46:29-- 7- NULL -- 0 0
If I am using 13 hours I get this results:
TRADE_DATE PRICE PERMANENTLY TREND MY_PERMANENTLY
---------------------------- ---------- ----------- -------------------- --------------
02.01.13 11:21:42,720000000 11 9801 UP 17961
02.01.13 12:44:03,236000000 12 97346 DOWN 97345
02.01.13 14:05:03,845000000 11 92485 DOWN 92485
02.01.13 16:21:04,345000000 8 23648 DOWN 40303
02.01.13 17:25:43,470000000 7 14717 DOWN 19769
03.01.13 10:31:00,376000000 5 65729 UP 65728
03.01.13 11:55:12,798000000 8 41350 UP 60676
03.01.13 16:32:47,642000000 10 44021 DOWN 44021
04.01.13 08:24:05,486000000 8 14418 STABLE 33743
04.01.13 12:24:23,189000000 8 19326 DOWN 19325
04.01.13 17:46:29,123000000 7 0 NULL 0
Please use this values
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('02.01.13 11:21:42,720000000',
'DD.MM.RR HH24:MI:SS,FF'),
'11',
'9801',
'UP');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('02.01.13 12:44:03,236000000',
'DD.MM.RR HH24:MI:SS,FF'),
'12',
'97346',
'DOWN');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('02.01.13 14:05:03,845000000',
'DD.MM.RR HH24:MI:SS,FF'),
'11',
'92485',
'DOWN');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('02.01.13 16:21:04,345000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8',
'23648',
'DOWN');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('02.01.13 17:25:43,470000000',
'DD.MM.RR HH24:MI:SS,FF'),
'7',
'14717',
'DOWN');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('03.01.13 10:31:00,376000000',
'DD.MM.RR HH24:MI:SS,FF'),
'5',
'65729',
'UP');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('03.01.13 11:55:12,798000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8',
'41350',
'UP');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('03.01.13 16:32:47,642000000',
'DD.MM.RR HH24:MI:SS,FF'),
'10',
'44201',
'DOWN');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('04.01.13 08:24:05,486000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8',
'14418',
'STABLE');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('04.01.13 12:24:23,189000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8',
'19326',
'DOWN');
INSERT INTO test2
(trade_date,
price,
permanently,
trend)
VALUES (To_timestamp('04.01.13 17:46:29,123000000',
'DD.MM.RR HH24:MI:SS,FF'),
'7',
'0',
'NULL');
The errors belongs to the end_trade_date. If the end_trade_date is not the last row, than the code takes one row to much for the end_trade_date variable. But I am not able to see the error in the code.
Best Regards and thank you for replay
[Updated on: Thu, 03 January 2013 04:17] Report message to a moderator |
|
|
|
|
|
| Re: Calculating the difference of timestamps in seconds [message #573921 is a reply to message #573725] |
Fri, 04 January 2013 05:16 |
Solomon Yakobson
Messages: 1443
Registered: January 2010
|
Senior Member |
|
|
marcopo wrote on Wed, 02 January 2013 17:21The time between two days are not 7 hours. The time is 13 hours. The example table is still correct.
Not enough. You also need to provide start and end time of trading day like you did in original post (no trade between 18:00 and 07:00 o'clock). Anyway, t3 in my solution:
t3 as (
-- 7 / 24 represents trade day start hour (7a.m.)
-- 11 represents trade day length in hours (7 to 18)
select t2.*,
(end_trade_date - cast(trunc(end_trade_date) + 7 / 24 as timestamp)) +
numtodsinterval((trunc(end_trade_date) - trunc(trade_date)) * 11,'hour') -
(trade_date - cast(trunc(trade_date) + 7 / 24 as timestamp)) diff
from t2
)
So replace 7 and 11 with proper values and you'll be OK.
SY.
[Updated on: Fri, 04 January 2013 05:18] Report message to a moderator |
|
|
|
| Re: Calculating the difference of timestamps in seconds [message #573931 is a reply to message #573921] |
Fri, 04 January 2013 06:20 |
|
marcopo
Messages: 24
Registered: December 2012
|
Junior Member |
|
|
Ok now I got it.
But still some values are not correct...
TRADE_DATE PRICE PERMANENTLY TREND MY_PERMANENTLY
---------------------------- ---------- ----------- -------------------- --------------
02.01.13 11:21:42,720000000 11 9801 UP 17961
02.01.13 12:44:03,236000000 12 97346 DOWN 97345
02.01.13 14:05:03,845000000 11 92485 DOWN 92485
02.01.13 16:21:04,345000000 8 23648 DOWN 40303
02.01.13 17:25:43,470000000 7 14717 DOWN 19769
03.01.13 10:31:00,376000000 5 65729 UP 65728
03.01.13 11:55:12,798000000 8 41350 UP 60676
03.01.13 16:32:47,642000000 10 44021 DOWN 44021
04.01.13 08:24:05,486000000 8 14418 STABLE 33743
04.01.13 12:24:23,189000000 8 19326 DOWN 19325
04.01.13 17:46:29,123000000 7 0 0
|
|
|
|
|
|
| Re: Calculating the difference of timestamps in seconds [message #574018 is a reply to message #573991] |
Sat, 05 January 2013 07:00 |
|
marcopo
Messages: 24
Registered: December 2012
|
Junior Member |
|
|
Hi,
the same query like you. The values are the same (Tradeday starts at 7 and ends at 18 o'click). ROW 1,4,5, 7, and 8 are wrong.
I think the errors belongs to the end_trade_date. If the end_trade_date is not the last row, than the code takes one row to much for the end_trade_date variable. But I am not able to see the error in the code.
Best Regards and thank you for replay
with t1 as (
select test2.*,
row_number() over(order by trade_date) rn
from test2
),
t2 as (
select a.*,
(
select b.trade_date
from t1 b
where connect_by_isleaf = 1
start with b.rn = a.rn
connect by b.rn = prior b.rn + 1
and (
(
a.trend = 'UP'
and
prior b.price >= a.price
)
or (
a.trend = 'STABLE'
and
prior b.price = a.price
)
or (
a.trend = 'DOWN'
and
prior b.price <= a.price
)
)
) end_trade_date
from t1 a
),
t3 as (
-- 7 / 24 represents trade day start hour (7a.m.)
-- 11 represents trade day length in hours (7 to 18)
select t2.*,
(end_trade_date - cast(trunc(end_trade_date) + 7 / 24 as timestamp)) +
numtodsinterval((trunc(end_trade_date) - trunc(trade_date)) * 11,'hour') -
(trade_date - cast(trunc(trade_date) + 7 / 24 as timestamp)) diff
from t2
)
select trade_date,
price,
permanently,
trend,
((date '1-1-1' + diff) - date '1-1-1') * 24 * 60 *60 my_permanently
from t3
order by rn;
|
|
|
|
| Re: Calculating the difference of timestamps in seconds [message #574019 is a reply to message #574018] |
Sat, 05 January 2013 07:53 |
Solomon Yakobson
Messages: 1443
Registered: January 2010
|
Senior Member |
|
|
Oops,
Cut & paste error. I posted wrong code with right results. Sorry about that. You should remove prior everywhere except connect by b.rn = prior b.rn + 1:
with t1 as (
select test2.*,
row_number() over(order by trade_date) rn
from test2
),
t2 as (
select a.*,
(
select b.trade_date
from t1 b
where connect_by_isleaf = 1
start with b.rn = a.rn
connect by b.rn = prior b.rn + 1
and (
(
a.trend = 'UP'
and
b.price >= a.price
)
or (
a.trend = 'STABLE'
and
b.price = a.price
)
or (
a.trend = 'DOWN'
and
b.price <= a.price
)
)
) end_trade_date
from t1 a
),
t3 as (
select t2.*,
(end_trade_date - cast(trunc(end_trade_date) + 7 / 24 as timestamp)) +
numtodsinterval((trunc(end_trade_date) - trunc(trade_date)) * 11,'hour') -
(trade_date - cast(trunc(trade_date) + 7 / 24 as timestamp)) diff
from t2
)
select trade_date,
price,
permanently,
trend,
((date '1-1-1' + diff) - date '1-1-1') * 24 * 60 *60 my_permanently
from t3
order by rn
/
Now:
SQL> with t1 as (
2 select test2.*,
3 row_number() over(order by trade_date) rn
4 from test2
5 ),
6 t2 as (
7 select a.*,
8 (
9 select b.trade_date
10 from t1 b
11 where connect_by_isleaf = 1
12 start with b.rn = a.rn
13 connect by b.rn = prior b.rn + 1
14 and (
15 (
16 a.trend = 'UP'
17 and
18 b.price >= a.price
19 )
20 or (
21 a.trend = 'STABLE'
22 and
23 b.price = a.price
24 )
25 or (
26 a.trend = 'DOWN'
27 and
28 b.price <= a.price
29 )
30 )
31 ) end_trade_date
32 from t1 a
33 ),
34 t3 as (
35 select t2.*,
36 (end_trade_date - cast(trunc(end_trade_date) + 7 / 24 as timestamp)) +
37 numtodsinterval((trunc(end_trade_date) - trunc(trade_date)) * 11,'hour') -
38 (trade_date - cast(trunc(trade_date) + 7 / 24 as timestamp)) diff
39 from t2
40 )
41 select trade_date,
42 price,
43 permanently,
44 trend,
45 ((date '1-1-1' + diff) - date '1-1-1') * 24 * 60 *60 my_permanently
46 from t3
47 order by rn
48 /
TRADE_DATE PRICE PERMANENTLY TREND MY_PERMANENTLY
------------------------------ ----- ----------- ------ --------------
02-JAN-13 11.21.42.720000 AM 11 9801 UP 9801
02-JAN-13 12.44.03.236000 PM 12 97346 DOWN 97345
02-JAN-13 02.05.03.845000 PM 11 92485 DOWN 92485
02-JAN-13 04.21.04.345000 PM 8 23648 DOWN 23648
02-JAN-13 05.25.43.470000 PM 7 14717 DOWN 14716
03-JAN-13 10.31.00.376000 AM 5 65729 UP 65728
03-JAN-13 11.55.12.798000 AM 8 41350 UP 41350
03-JAN-13 04.32.47.642000 PM 10 44201 DOWN 44021
04-JAN-13 08.24.05.486000 AM 8 14418 STABLE 14417
04-JAN-13 12.24.23.189000 PM 8 19326 DOWN 19325
04-JAN-13 05.46.29.123000 PM 7 0 NULL 0
11 rows selected.
SQL>
Now the difference is in rounding. My code truncates fractional seconds from trade date difference. If you want to round it:
with t1 as (
select test2.*,
row_number() over(order by trade_date) rn
from test2
),
t2 as (
select a.*,
(
select b.trade_date
from t1 b
where connect_by_isleaf = 1
start with b.rn = a.rn
connect by b.rn = prior b.rn + 1
and (
(
a.trend = 'UP'
and
b.price >= a.price
)
or (
a.trend = 'STABLE'
and
b.price = a.price
)
or (
a.trend = 'DOWN'
and
b.price <= a.price
)
)
) end_trade_date
from t1 a
),
t3 as (
select t2.*,
(end_trade_date - cast(trunc(end_trade_date) + 7 / 24 as timestamp)) +
numtodsinterval((trunc(end_trade_date) - trunc(trade_date)) * 11,'hour') -
(trade_date - cast(trunc(trade_date) + 7 / 24 as timestamp)) diff
from t2
)
select trade_date,
price,
permanently,
trend,
round(
(
(
extract(day from diff) * 24 + extract(hour from diff)
) * 60 + extract(minute from diff)
) * 60 + extract(second from diff)
) my_permanently
from t3
order by rn
/
TRADE_DATE PRICE PERMANENTLY TREND MY_PERMANENTLY
------------------------------ ----- ----------- ------ --------------
02-JAN-13 11.21.42.720000 AM 11 9801 UP 9801
02-JAN-13 12.44.03.236000 PM 12 97346 DOWN 97346
02-JAN-13 02.05.03.845000 PM 11 92485 DOWN 92485
02-JAN-13 04.21.04.345000 PM 8 23648 DOWN 23648
02-JAN-13 05.25.43.470000 PM 7 14717 DOWN 14717
03-JAN-13 10.31.00.376000 AM 5 65729 UP 65729
03-JAN-13 11.55.12.798000 AM 8 41350 UP 41350
03-JAN-13 04.32.47.642000 PM 10 44201 DOWN 44021
04-JAN-13 08.24.05.486000 AM 8 14418 STABLE 14418
04-JAN-13 12.24.23.189000 PM 8 19326 DOWN 19326
04-JAN-13 05.46.29.123000 PM 7 0 NULL 0
11 rows selected.
SQL>
SY.
[Updated on: Sat, 05 January 2013 08:01] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Calculating the difference of timestamps in seconds [message #574058 is a reply to message #574057] |
Sun, 06 January 2013 07:07 |
Solomon Yakobson
Messages: 1443
Registered: January 2010
|
Senior Member |
|
|
marcopo wrote on Sun, 06 January 2013 07:43I tryed it in this way but I get an error.
Use:
merge
into test2 a
using (
with t1 as (
select test2.*,
rowid rid,
row_number() over(order by trade_date) rn
from test2
),
t2 as (
select a.*,
(
select b.trade_date
from t1 b
where connect_by_isleaf = 1
start with b.rn = a.rn
connect by b.rn = prior b.rn + 1
and (
(
a.trend = 'UP'
and
b.price >= a.price
)
or (
a.trend = 'STABLE'
and
b.price = a.price
)
or (
a.trend = 'DOWN'
and
b.price <= a.price
)
)
) end_trade_date
from t1 a
),
t3 as (
select t2.*,
(end_trade_date - cast(trunc(end_trade_date) + 7 / 24 as timestamp)) +
numtodsinterval((trunc(end_trade_date) - trunc(trade_date)) * 11,'hour') -
(trade_date - cast(trunc(trade_date) + 7 / 24 as timestamp)) diff
from t2
)
select rid,
round(
(
(
extract(day from diff) * 24 + extract(hour from diff)
) * 60 + extract(minute from diff)
) * 60 + extract(second from diff)
) my_permanently
from t3
) b
on (
a.rowid = b.rid
)
when matched
then update
set a.permanently = b.my_permanently
/
For example:
SQL> select *
2 from test2
3 /
TRADE_DATE PRICE PERMANENTLY TREND
------------------------------ ----- ----------- ------
02-JAN-13 11.21.42.720000 AM 11 UP
02-JAN-13 12.44.03.236000 PM 12 DOWN
02-JAN-13 02.05.03.845000 PM 11 DOWN
02-JAN-13 04.21.04.345000 PM 8 DOWN
02-JAN-13 05.25.43.470000 PM 7 DOWN
03-JAN-13 10.31.00.376000 AM 5 UP
03-JAN-13 11.55.12.798000 AM 8 UP
03-JAN-13 04.32.47.642000 PM 10 DOWN
04-JAN-13 08.24.05.486000 AM 8 STABLE
04-JAN-13 12.24.23.189000 PM 8 DOWN
04-JAN-13 05.46.29.123000 PM 7 NULL
11 rows selected.
SQL> merge
2 into test2 a
3 using (
4 with t1 as (
5 select test2.*,
6 rowid rid,
7 row_number() over(order by trade_date) rn
8 from test2
9 ),
10 t2 as (
11 select a.*,
12 (
13 select b.trade_date
14 from t1 b
15 where connect_by_isleaf = 1
16 start with b.rn = a.rn
17 connect by b.rn = prior b.rn + 1
18 and (
19 (
20 a.trend = 'UP'
21 and
22 b.price >= a.price
23 )
24 or (
25 a.trend = 'STABLE'
26 and
27 b.price = a.price
28 )
29 or (
30 a.trend = 'DOWN'
31 and
32 b.price <= a.price
33 )
34 )
35 ) end_trade_date
36 from t1 a
37 ),
38 t3 as (
39 select t2.*,
40 (end_trade_date - cast(trunc(end_trade_date) + 7 / 24 as timestamp)) +
41 numtodsinterval((trunc(end_trade_date) - trunc(trade_date)) * 11,'hour') -
42 (trade_date - cast(trunc(trade_date) + 7 / 24 as timestamp)) diff
43 from t2
44 )
45 select rid,
46 round(
47 (
48 (
49 extract(day from diff) * 24 + extract(hour from diff)
50 ) * 60 + extract(minute from diff)
51 ) * 60 + extract(second from diff)
52 ) my_permanently
53 from t3
54 ) b
55 on (
56 a.rowid = b.rid
57 )
58 when matched
59 then update
60 set a.permanently = b.my_permanently
61 /
11 rows merged.
SQL> select *
2 from test2
3 /
TRADE_DATE PRICE PERMANENTLY TREND
------------------------------ ----- ----------- ------
02-JAN-13 11.21.42.720000 AM 11 9801 UP
02-JAN-13 12.44.03.236000 PM 12 97346 DOWN
02-JAN-13 02.05.03.845000 PM 11 92485 DOWN
02-JAN-13 04.21.04.345000 PM 8 23648 DOWN
02-JAN-13 05.25.43.470000 PM 7 14717 DOWN
03-JAN-13 10.31.00.376000 AM 5 65729 UP
03-JAN-13 11.55.12.798000 AM 8 41350 UP
03-JAN-13 04.32.47.642000 PM 10 44021 DOWN
04-JAN-13 08.24.05.486000 AM 8 14418 STABLE
04-JAN-13 12.24.23.189000 PM 8 19326 DOWN
04-JAN-13 05.46.29.123000 PM 7 0 NULL
11 rows selected.
SQL>
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jun 20 08:33:13 CDT 2013
Total time taken to generate the page: 0.25276 seconds
|
] 
Members
Search
Help
Register
Login
Home
