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 Wed, 02 January 2013 05:29
 marcopo Messages: 24Registered: 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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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?

[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: 57221Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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?

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"
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: 24Registered: 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
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
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
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
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
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
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
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
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
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
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
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: 1711Registered: January 2010 Senior Member
marcopo wrote on Wed, 02 January 2013 06:29
Anyone an idea?

Just for fun:

```with t1 as (
select  test2.*,
row_number() over(order by trade_date) rn
from  test2
),
t2 as (
select  a.*,
(
from  t1 b
where connect_by_isleaf = 1
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
)
)
from  t1 a
),
t3 as (
select  t2.*,
(end_trade_date - cast(trunc(end_trade_date) + 7 / 24 as timestamp)) +
(trade_date - cast(trunc(trade_date) + 7 / 24 as timestamp)) diff
from  t2
)
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: 24Registered: 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
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
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
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
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
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
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
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
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
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
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
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 #573879 is a reply to message #573725] Thu, 03 January 2013 15:14
 marcopo Messages: 24Registered: December 2012 Junior Member
No one an idea?

Best Regards
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: 1711Registered: January 2010 Senior Member
marcopo wrote on Wed, 02 January 2013 17:21
The 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)) +
(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: 24Registered: 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 #573991 is a reply to message #573931] Fri, 04 January 2013 18:24
 Solomon Yakobson Messages: 1711Registered: January 2010 Senior Member

SY.
Re: Calculating the difference of timestamps in seconds [message #574018 is a reply to message #573991] Sat, 05 January 2013 07:00
 marcopo Messages: 24Registered: 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.*,
(
from  t1 b
where connect_by_isleaf = 1
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
)
)
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)) +
(trade_date - cast(trunc(trade_date) + 7 / 24 as timestamp)) diff
from  t2
)
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: 1711Registered: 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.*,
(
from  t1 b
where connect_by_isleaf = 1
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
)
)
from  t1 a
),
t3 as (
select  t2.*,
(end_trade_date - cast(trunc(end_trade_date) + 7 / 24 as timestamp)) +
(trade_date - cast(trunc(trade_date) + 7 / 24 as timestamp)) diff
from  t2
)
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                      (
10                         from  t1 b
11                         where connect_by_isleaf = 1
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                                    )
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             )
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.*,
(
from  t1 b
where connect_by_isleaf = 1
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
)
)
from  t1 a
),
t3 as (
select  t2.*,
(end_trade_date - cast(trunc(end_trade_date) + 7 / 24 as timestamp)) +
(trade_date - cast(trunc(trade_date) + 7 / 24 as timestamp)) diff
from  t2
)
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 #574020 is a reply to message #574019] Sat, 05 January 2013 08:47
 marcopo Messages: 24Registered: December 2012 Junior Member
Thanks so much! But one last thing, please. I tryed now to update the empty row "permanently" with the calculated values. But without success. Can you please add the code for the update statement? Sorry I'm not so experienced.
Re: Calculating the difference of timestamps in seconds [message #574025 is a reply to message #574020] Sat, 05 January 2013 10:15
 Solomon Yakobson Messages: 1711Registered: January 2010 Senior Member
And what are the rules for NULL trend?

SY.
Re: Calculating the difference of timestamps in seconds [message #574027 is a reply to message #574025] Sat, 05 January 2013 10:45
 marcopo Messages: 24Registered: December 2012 Junior Member
I will delete the rows with NULL trend. So you don't have to be aware of the rules.

[Updated on: Sun, 06 January 2013 03:59]

Report message to a moderator

Re: Calculating the difference of timestamps in seconds [message #574057 is a reply to message #574027] Sun, 06 January 2013 06:43
 marcopo Messages: 24Registered: December 2012 Junior Member
I tryed it in this way but I get an error.

```
UPDATE test2 w set permanently =(select my_permanently from(

with t1 as (
select  test2.*,
row_number() over(order by trade_date) rn
from  test2
),
t2 as (
select  a.*,
(
from  t1 b
where connect_by_isleaf = 1
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
)
)
from  t1 a
),
t3 as (
select  t2.*,
(end_trade_date - cast(trunc(end_trade_date) + 7 / 24 as timestamp)) +
(trade_date - cast(trunc(trade_date) + 7 / 24 as timestamp)) diff
from  t2
)
select
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)
)

SQL-Fehler: ORA-01427:
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:
*Action:

```
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: 1711Registered: January 2010 Senior Member
marcopo wrote on Sun, 06 January 2013 07:43
I 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.*,
(
from  t1 b
where connect_by_isleaf = 1
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
)
)
from  t1 a
),
t3 as (
select  t2.*,
(end_trade_date - cast(trunc(end_trade_date) + 7 / 24 as timestamp)) +
(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                               (
14                                  from  t1 b
15                                  where connect_by_isleaf = 1
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                                             )
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.
Re: Calculating the difference of timestamps in seconds [message #574065 is a reply to message #574058] Sun, 06 January 2013 09:31
 marcopo Messages: 24Registered: December 2012 Junior Member
Hi,

thanks so much for your help.

 Previous Topic: Please Make Query Next Topic: difference between oracle procedure & package
Goto Forum:

Current Time: Fri Mar 07 03:00:16 CST 2014

Total time taken to generate the page: 0.16250 seconds