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 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: 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.*,
                    (
                     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 Go to previous messageGo to next message
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 #573879 is a reply to message #573725] Thu, 03 January 2013 15:14 Go to previous messageGo to next message
marcopo
Messages: 24
Registered: 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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: 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)) +
                    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 Go to previous messageGo to next message
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 #573991 is a reply to message #573931] Fri, 04 January 2013 18:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: January 2010
Senior Member
Post your query.

SY.
Re: Calculating the difference of timestamps in seconds [message #574018 is a reply to message #573991] Sat, 05 January 2013 07:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
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 #574020 is a reply to message #574019] Sat, 05 January 2013 08:47 Go to previous messageGo to next message
marcopo
Messages: 24
Registered: 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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: 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 Go to previous messageGo to next message
marcopo
Messages: 24
Registered: 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 Go to previous messageGo to next message
marcopo
Messages: 24
Registered: 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.*,
                    (
                     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
        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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: 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.*,
                             (
                              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.
Re: Calculating the difference of timestamps in seconds [message #574065 is a reply to message #574058] Sun, 06 January 2013 09:31 Go to previous message
marcopo
Messages: 24
Registered: 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: Mon Sep 22 21:54:24 CDT 2014

Total time taken to generate the page: 0.05578 seconds