Substracting timestamps (was : "Substract 2 timestamp is incorrect (help please)!") 
Author Message
 Substracting timestamps (was : "Substract 2 timestamp is incorrect (help please)!")

My post refers to an old problem submitted by my now gone colleague in this newsgroup. I'm trying to fix this problem and don't get the same results here under OS/2 as obtained below under Windows NT. According to the DB2/2 SQL reference manual, the difference between 2 timestamps is a timestamp duration, not a number of seconds (which I would prefer) and this is exactly what I get. A timestamp duration has a yyyymmddhhmmss,zzzzzz format, so it is a time string, not a number of seconds.

For instance, the duration between timestamps
1999-11-17 00:00:00 and
2000-04-01 00:00:00 is
00000413000000,000000 that is 4 months (December, January, February and March) and  13 days (the 13 days in November). This is correct but as I need to convert this into minutes for my application purposes, I must take into account the fact that these 4 months actually have 31, 31, 29 and 31 days. So the number of minutes is actually: 60*24*(31+31+29+31+13).

How can I get a second or minute difference between 2 timestamps under DB2/2?

--
Luc Le Blanc
Hydro-Quebec
Montreal (Canada)

Quote:


>  > The result is invalid when the difference is larger than 1 month. Are you idea? Is
>  > it a bug? If Yes, how sumit a APAR to IBM.

>  Here is log of my testing on Windows NT:

>  D:\SQLLIB\BIN>db2 connect to sample

>    Database Connection Information

>  Database server        = DB2/NT 6.1.0
>  SQL authorization ID  = DB2ADMIN
>  Local database alias  = SAMPLE

>  D:\SQLLIB\BIN>db2 create table test (end timestamp, start timestamp) DB20000I  The SQL command completed successfully.
>  D:\SQLLIB\BIN>db2 insert into test values(current timestamp,'2000-01-01 00:00:00') DB20000I  The SQL command completed successfully.
>  D:\SQLLIB\BIN>db2 create view vtest(diff) as (select end - start from test) DB20000I  The SQL command completed successfully.
>  D:\SQLLIB\BIN>db2 select * from test

>  END                        START
>  -------------------------- --------------------------
>  2000-03-28-13.07.34.498000 2000-01-01-00.00.00.000000

>    1 record(s) selected.

>  D:\SQLLIB\BIN>db2 select * from vtest

>  DIFF
>  ----------------------
>        227130734.498000

>    1 record(s) selected.

>  Difference between START and END is more than one month - are you saying that the answer of 227,130,734.498 seconds is incorrect (Difference between midnight,
>  January 1, 2000 and 13:07:34.498 March 28 expressed as number of seconds)?

>  > The result is invalid when the difference is larger than 1 month. Are you idea? Is
>  > it a bug? If Yes, how sumit a APAR to IBM.

>  I don't see the bug yet - but I will be more than happy to assist you further. As for an APAR - if you have service agreement with IBM - just call 1-800-237-5511 (have your
>  customer number ready). Again - I don't see a bug yet - but I did not count number of seconds between those dates.

>  Best regards,

>  Jan M. Nelken

>  -------------------------------------------------------------------------
>  Office e-mail: jnelken at ca period ibm period com
>  Home  e-mail: nelkenj at attglobal period net

>  IBM Certified Solutions Expert DB2 UDB V6.1 for UNIX, Windows and OS/2     - Database Administration & Application Development
>  IBM Certified Advanced Technical Expert DB2 UDB V6.1 for UNIX, Windows and OS/2     - Distributed Relational Database Architecture (DRDA)



Mon, 28 Apr 2003 03:00:00 GMT
 Substracting timestamps (was : "Substract 2 timestamp is incorrect (help please)!")

Hi Luc,
Does the JULIAN_DAY() function help? To get the number of hours, try something like:

    select case
        when time(ts1) >= time(ts2)
        then (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2)
        else (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2)
    from t;

I didn't run it or test it!

Hope this helps,
David.

Quote:

> My post refers to an old problem submitted by my now gone colleague in this newsgroup. I'm trying to fix this problem and don't get the same results here under OS/2 as obtained below under Windows NT. According to the DB2/2 SQL reference manual, the difference between 2 timestamps is a timestamp duration, not a number of seconds (which I would prefer) and this is exactly what I get. A timestamp duration has a yyyymmddhhmmss,zzzzzz format, so it is a time string, not a number of seconds.

> For instance, the duration between timestamps
> 1999-11-17 00:00:00 and
> 2000-04-01 00:00:00 is
> 00000413000000,000000 that is 4 months (December, January, February and March) and  13 days (the 13 days in November). This is correct but as I need to convert this into minutes for my application purposes, I must take into account the fact that these 4 months actually have 31, 31, 29 and 31 days. So the number of minutes is actually: 60*24*(31+31+29+31+13).

> How can I get a second or minute difference between 2 timestamps under DB2/2?

> --
> Luc Le Blanc
> Hydro-Quebec
> Montreal (Canada)



> >  > The result is invalid when the difference is larger than 1 month. Are you idea? Is
> >  > it a bug? If Yes, how sumit a APAR to IBM.

> >  Here is log of my testing on Windows NT:

> >  D:\SQLLIB\BIN>db2 connect to sample

> >    Database Connection Information

> >  Database server        = DB2/NT 6.1.0
> >  SQL authorization ID  = DB2ADMIN
> >  Local database alias  = SAMPLE

> >  D:\SQLLIB\BIN>db2 create table test (end timestamp, start timestamp) DB20000I  The SQL command completed successfully.
> >  D:\SQLLIB\BIN>db2 insert into test values(current timestamp,'2000-01-01 00:00:00') DB20000I  The SQL command completed successfully.
> >  D:\SQLLIB\BIN>db2 create view vtest(diff) as (select end - start from test) DB20000I  The SQL command completed successfully.
> >  D:\SQLLIB\BIN>db2 select * from test

> >  END                        START
> >  -------------------------- --------------------------
> >  2000-03-28-13.07.34.498000 2000-01-01-00.00.00.000000

> >    1 record(s) selected.

> >  D:\SQLLIB\BIN>db2 select * from vtest

> >  DIFF
> >  ----------------------
> >        227130734.498000

> >    1 record(s) selected.

> >  Difference between START and END is more than one month - are you saying that the answer of 227,130,734.498 seconds is incorrect (Difference between midnight,
> >  January 1, 2000 and 13:07:34.498 March 28 expressed as number of seconds)?

> >  > The result is invalid when the difference is larger than 1 month. Are you idea? Is
> >  > it a bug? If Yes, how sumit a APAR to IBM.

> >  I don't see the bug yet - but I will be more than happy to assist you further. As for an APAR - if you have service agreement with IBM - just call 1-800-237-5511 (have your
> >  customer number ready). Again - I don't see a bug yet - but I did not count number of seconds between those dates.

> >  Best regards,

> >  Jan M. Nelken

> >  -------------------------------------------------------------------------
> >  Office e-mail: jnelken at ca period ibm period com
> >  Home  e-mail: nelkenj at attglobal period net

> >  IBM Certified Solutions Expert DB2 UDB V6.1 for UNIX, Windows and OS/2     - Database Administration & Application Development
> >  IBM Certified Advanced Technical Expert DB2 UDB V6.1 for UNIX, Windows and OS/2     - Distributed Relational Database Architecture (DRDA)



Mon, 28 Apr 2003 03:00:00 GMT
 Substracting timestamps (was : "Substract 2 timestamp is incorrect (help please)!")
I guess it should work but it will truncate hours and minutes by returning a whole number of days. My conclusion is that I must perform myself the conversion between timestamp duration to minutes, taking into acount the real number of days in months, depending on which months are covered. I'll add this in the program that performs the select request (instead of the SQL request itself).

--
Luc Le Blanc
Hydro-Quebec
Montreal (Canada)

David Sharpe a crit :

Quote:
> Hi Luc,
> Does the JULIAN_DAY() function help? To get the number of hours, try something like:

>     select case
>         when time(ts1) >= time(ts2)
>         then (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2)
>         else (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2)
>     from t;

> I didn't run it or test it!

> Hope this helps,
> David.


> > My post refers to an old problem submitted by my now gone colleague in this newsgroup. I'm trying to fix this problem and don't get the same results here under OS/2 as obtained below under Windows NT. According to the DB2/2 SQL reference manual, the difference between 2 timestamps is a timestamp duration, not a number of seconds (which I would prefer) and this is exactly what I get. A timestamp duration has a yyyymmddhhmmss,zzzzzz format, so it is a time string, not a number of seconds.

> > For instance, the duration between timestamps
> > 1999-11-17 00:00:00 and
> > 2000-04-01 00:00:00 is
> > 00000413000000,000000 that is 4 months (December, January, February and March) and  13 days (the 13 days in November). This is correct but as I need to convert this into minutes for my application purposes, I must take into account the fact that these 4 months actually have 31, 31, 29 and 31 days. So the number of minutes is actually: 60*24*(31+31+29+31+13).

> > How can I get a second or minute difference between 2 timestamps under DB2/2?

> > --
> > Luc Le Blanc
> > Hydro-Quebec
> > Montreal (Canada)



> > >  > The result is invalid when the difference is larger than 1 month. Are you idea? Is
> > >  > it a bug? If Yes, how sumit a APAR to IBM.

> > >  Here is log of my testing on Windows NT:

> > >  D:\SQLLIB\BIN>db2 connect to sample

> > >    Database Connection Information

> > >  Database server        = DB2/NT 6.1.0
> > >  SQL authorization ID  = DB2ADMIN
> > >  Local database alias  = SAMPLE

> > >  D:\SQLLIB\BIN>db2 create table test (end timestamp, start timestamp) DB20000I  The SQL command completed successfully.
> > >  D:\SQLLIB\BIN>db2 insert into test values(current timestamp,'2000-01-01 00:00:00') DB20000I  The SQL command completed successfully.
> > >  D:\SQLLIB\BIN>db2 create view vtest(diff) as (select end - start from test) DB20000I  The SQL command completed successfully.
> > >  D:\SQLLIB\BIN>db2 select * from test

> > >  END                        START
> > >  -------------------------- --------------------------
> > >  2000-03-28-13.07.34.498000 2000-01-01-00.00.00.000000

> > >    1 record(s) selected.

> > >  D:\SQLLIB\BIN>db2 select * from vtest

> > >  DIFF
> > >  ----------------------
> > >        227130734.498000

> > >    1 record(s) selected.

> > >  Difference between START and END is more than one month - are you saying that the answer of 227,130,734.498 seconds is incorrect (Difference between midnight,
> > >  January 1, 2000 and 13:07:34.498 March 28 expressed as number of seconds)?

> > >  > The result is invalid when the difference is larger than 1 month. Are you idea? Is
> > >  > it a bug? If Yes, how sumit a APAR to IBM.

> > >  I don't see the bug yet - but I will be more than happy to assist you further. As for an APAR - if you have service agreement with IBM - just call 1-800-237-5511 (have your
> > >  customer number ready). Again - I don't see a bug yet - but I did not count number of seconds between those dates.

> > >  Best regards,

> > >  Jan M. Nelken

> > >  -------------------------------------------------------------------------
> > >  Office e-mail: jnelken at ca period ibm period com
> > >  Home  e-mail: nelkenj at attglobal period net

> > >  IBM Certified Solutions Expert DB2 UDB V6.1 for UNIX, Windows and OS/2     - Database Administration & Application Development
> > >  IBM Certified Advanced Technical Expert DB2 UDB V6.1 for UNIX, Windows and OS/2     - Distributed Relational Database Architecture (DRDA)



Fri, 02 May 2003 03:00:00 GMT
 Substracting timestamps (was : "Substract 2 timestamp is incorrect (help please)!")
Hi Luc,
You can add in the hours, minutes, seconds, and microseconds by nesting the case expressions. At each unit's level (days, hours, minutes, ...), you need a case when the subtraction of that unit will cause a positive number and a case when it will be a negative number ... similar to the two cases I have for hours.

Here is the select for hours:
     select case
         when time(ts1) >= time(ts2)
         then (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2)
         else (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2)
     from t;

Here is the select for minutes:
    select case
         when hour(ts1) >= hour(ts2)
            then
            case when minute(ts1) >= minute(ts2)
                 then (60 * (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2))
                    + minute(ts1) - minute(ts2);
                 else (60 * (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2))
                    + 60 - minute(ts1) + minute(ts2);
            end;
        else
            case when minute(ts1) >= minute(ts2)
                 then (60 * (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2))
                    + minute(ts1) - minute(ts2);
                 else (60 * (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2))
                    + 60 - minute(ts1) + minute(ts2);
            end;
        end;
     from t

If you don't do it in the sql, then you have to have the same logic in your application.

Hope this helps,
David.

Quote:

> I guess it should work but it will truncate hours and minutes by returning a whole number of days. My conclusion is that I must perform myself the conversion between timestamp duration to minutes, taking into acount the real number of days in months, depending on which months are covered. I'll add this in the program that performs the select request (instead of the SQL request itself).

> --
> Luc Le Blanc
> Hydro-Quebec
> Montreal (Canada)

> David Sharpe a crit :

> > Hi Luc,
> > Does the JULIAN_DAY() function help? To get the number of hours, try something like:

> >     select case
> >         when time(ts1) >= time(ts2)
> >         then (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2)
> >         else (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2)
> >     from t;

> > I didn't run it or test it!

> > Hope this helps,
> > David.


> > > My post refers to an old problem submitted by my now gone colleague in this newsgroup. I'm trying to fix this problem and don't get the same results here under OS/2 as obtained below under Windows NT. According to the DB2/2 SQL reference manual, the difference between 2 timestamps is a timestamp duration, not a number of seconds (which I would prefer) and this is exactly what I get. A timestamp duration has a yyyymmddhhmmss,zzzzzz format, so it is a time string, not a number of seconds.

> > > For instance, the duration between timestamps
> > > 1999-11-17 00:00:00 and
> > > 2000-04-01 00:00:00 is
> > > 00000413000000,000000 that is 4 months (December, January, February and March) and  13 days (the 13 days in November). This is correct but as I need to convert this into minutes for my application purposes, I must take into account the fact that these 4 months actually have 31, 31, 29 and 31 days. So the number of minutes is actually: 60*24*(31+31+29+31+13).

> > > How can I get a second or minute difference between 2 timestamps under DB2/2?

> > > --
> > > Luc Le Blanc
> > > Hydro-Quebec
> > > Montreal (Canada)



> > > >  > The result is invalid when the difference is larger than 1 month. Are you idea? Is
> > > >  > it a bug? If Yes, how sumit a APAR to IBM.

> > > >  Here is log of my testing on Windows NT:

> > > >  D:\SQLLIB\BIN>db2 connect to sample

> > > >    Database Connection Information

> > > >  Database server        = DB2/NT 6.1.0
> > > >  SQL authorization ID  = DB2ADMIN
> > > >  Local database alias  = SAMPLE

> > > >  D:\SQLLIB\BIN>db2 create table test (end timestamp, start timestamp) DB20000I  The SQL command completed successfully.
> > > >  D:\SQLLIB\BIN>db2 insert into test values(current timestamp,'2000-01-01 00:00:00') DB20000I  The SQL command completed successfully.
> > > >  D:\SQLLIB\BIN>db2 create view vtest(diff) as (select end - start from test) DB20000I  The SQL command completed successfully.
> > > >  D:\SQLLIB\BIN>db2 select * from test

> > > >  END                        START
> > > >  -------------------------- --------------------------
> > > >  2000-03-28-13.07.34.498000 2000-01-01-00.00.00.000000

> > > >    1 record(s) selected.

> > > >  D:\SQLLIB\BIN>db2 select * from vtest

> > > >  DIFF
> > > >  ----------------------
> > > >        227130734.498000

> > > >    1 record(s) selected.

> > > >  Difference between START and END is more than one month - are you saying that the answer of 227,130,734.498 seconds is incorrect (Difference between midnight,
> > > >  January 1, 2000 and 13:07:34.498 March 28 expressed as number of seconds)?

> > > >  > The result is invalid when the difference is larger than 1 month. Are you idea? Is
> > > >  > it a bug? If Yes, how sumit a APAR to IBM.

> > > >  I don't see the bug yet - but I will be more than happy to assist you further. As for an APAR - if you have service agreement with IBM - just call 1-800-237-5511 (have your
> > > >  customer number ready). Again - I don't see a bug yet - but I did not count number of seconds between those dates.

> > > >  Best regards,

> > > >  Jan M. Nelken

> > > >  -------------------------------------------------------------------------
> > > >  Office e-mail: jnelken at ca period ibm period com
> > > >  Home  e-mail: nelkenj at attglobal period net

> > > >  IBM Certified Solutions Expert DB2 UDB V6.1 for UNIX, Windows and OS/2     - Database Administration & Application Development
> > > >  IBM Certified Advanced Technical Expert DB2 UDB V6.1 for UNIX, Windows and OS/2     - Distributed Relational Database Architecture (DRDA)



Fri, 02 May 2003 03:00:00 GMT
 Substracting timestamps (was : "Substract 2 timestamp is incorrect (help please)!")
From the IBM intranet, Serge made the suggestion to wrap the beastly SQL into
a function, which you can then invoke easily in your SQL. Here is his suggestion:

create function delta_minutes(ts1 timestamp, ts2 timestamp)
  returns integer deterministic no external action contains sql
return
case
         when hour(ts1) >= hour(ts2)
            then
            case when minute(ts1) >= minute(ts2)
                 then (60 * (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2))
                    + minute(ts1) - minute(ts2);
                 else (60 * (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2))
                    + 60 - minute(ts1) + minute(ts2);
            end;
        else
            case when minute(ts1) >= minute(ts2)
                 then (60 * (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2))
                    + minute(ts1) - minute(ts2);
                 else (60 * (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2))
                    + 60 - minute(ts1) + minute(ts2);
            end;
        end;

Quote:

> Hi Luc,
> You can add in the hours, minutes, seconds, and microseconds by nesting the case expressions. At each unit's level (days, hours, minutes, ...), you need a case when the subtraction of that unit will cause a positive number and a case when it will be a negative number ... similar to the two cases I have for hours.

> Here is the select for hours:
>      select case
>          when time(ts1) >= time(ts2)
>          then (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2)
>          else (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2)
>      from t;

> Here is the select for minutes:
>     select case
>          when hour(ts1) >= hour(ts2)
>             then
>             case when minute(ts1) >= minute(ts2)
>                  then (60 * (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2))
>                     + minute(ts1) - minute(ts2);
>                  else (60 * (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2))
>                     + 60 - minute(ts1) + minute(ts2);
>             end;
>         else
>             case when minute(ts1) >= minute(ts2)
>                  then (60 * (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2))
>                     + minute(ts1) - minute(ts2);
>                  else (60 * (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2))
>                     + 60 - minute(ts1) + minute(ts2);
>             end;
>         end;
>      from t

> If you don't do it in the sql, then you have to have the same logic in your application.

> Hope this helps,
> David.


> > I guess it should work but it will truncate hours and minutes by returning a whole number of days. My conclusion is that I must perform myself the conversion between timestamp duration to minutes, taking into acount the real number of days in months, depending on which months are covered. I'll add this in the program that performs the select request (instead of the SQL request itself).

> > --
> > Luc Le Blanc
> > Hydro-Quebec
> > Montreal (Canada)

> > David Sharpe a crit :

> > > Hi Luc,
> > > Does the JULIAN_DAY() function help? To get the number of hours, try something like:

> > >     select case
> > >         when time(ts1) >= time(ts2)
> > >         then (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2)
> > >         else (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2)
> > >     from t;

> > > I didn't run it or test it!

> > > Hope this helps,
> > > David.


> > > > My post refers to an old problem submitted by my now gone colleague in this newsgroup. I'm trying to fix this problem and don't get the same results here under OS/2 as obtained below under Windows NT. According to the DB2/2 SQL reference manual, the difference between 2 timestamps is a timestamp duration, not a number of seconds (which I would prefer) and this is exactly what I get. A timestamp duration has a yyyymmddhhmmss,zzzzzz format, so it is a time string, not a number of seconds.

> > > > For instance, the duration between timestamps
> > > > 1999-11-17 00:00:00 and
> > > > 2000-04-01 00:00:00 is
> > > > 00000413000000,000000 that is 4 months (December, January, February and March) and  13 days (the 13 days in November). This is correct but as I need to convert this into minutes for my application purposes, I must take into account the fact that these 4 months actually have 31, 31, 29 and 31 days. So the number of minutes is actually: 60*24*(31+31+29+31+13).

> > > > How can I get a second or minute difference between 2 timestamps under DB2/2?

> > > > --
> > > > Luc Le Blanc
> > > > Hydro-Quebec
> > > > Montreal (Canada)



> > > > >  > The result is invalid when the difference is larger than 1 month. Are you idea? Is
> > > > >  > it a bug? If Yes, how sumit a APAR to IBM.

> > > > >  Here is log of my testing on Windows NT:

> > > > >  D:\SQLLIB\BIN>db2 connect to sample

> > > > >    Database Connection Information

> > > > >  Database server        = DB2/NT 6.1.0
> > > > >  SQL authorization ID  = DB2ADMIN
> > > > >  Local database alias  = SAMPLE

> > > > >  D:\SQLLIB\BIN>db2 create table test (end timestamp, start timestamp) DB20000I  The SQL command completed successfully.
> > > > >  D:\SQLLIB\BIN>db2 insert into test values(current timestamp,'2000-01-01 00:00:00') DB20000I  The SQL command completed successfully.
> > > > >  D:\SQLLIB\BIN>db2 create view vtest(diff) as (select end - start from test) DB20000I  The SQL command completed successfully.
> > > > >  D:\SQLLIB\BIN>db2 select * from test

> > > > >  END                        START
> > > > >  -------------------------- --------------------------
> > > > >  2000-03-28-13.07.34.498000 2000-01-01-00.00.00.000000

> > > > >    1 record(s) selected.

> > > > >  D:\SQLLIB\BIN>db2 select * from vtest

> > > > >  DIFF
> > > > >  ----------------------
> > > > >        227130734.498000

> > > > >    1 record(s) selected.

> > > > >  Difference between START and END is more than one month - are you saying that the answer of 227,130,734.498 seconds is incorrect (Difference between midnight,
> > > > >  January 1, 2000 and 13:07:34.498 March 28 expressed as number of seconds)?

> > > > >  > The result is invalid when the difference is larger than 1 month. Are you idea? Is
> > > > >  > it a bug? If Yes, how sumit a APAR to IBM.

> > > > >  I don't see the bug yet - but I will be more than happy to assist you further. As for an APAR - if you have service agreement with IBM - just call 1-800-237-5511 (have your
> > > > >  customer number ready). Again - I don't see a bug yet - but I did not count number of seconds between those dates.

> > > > >  Best regards,

> > > > >  Jan M. Nelken

> > > > >  -------------------------------------------------------------------------
> > > > >  Office e-mail: jnelken at ca period ibm period com
> > > > >  Home  e-mail: nelkenj at attglobal period net

> > > > >  IBM Certified Solutions Expert DB2 UDB V6.1 for UNIX, Windows and OS/2     - Database Administration & Application Development
> > > > >  IBM Certified Advanced Technical Expert DB2 UDB V6.1 for UNIX, Windows and OS/2     - Distributed Relational Database Architecture (DRDA)



Fri, 02 May 2003 03:00:00 GMT
 Substracting timestamps (was : "Substract 2 timestamp is incorrect (help please)!")
Thanks David & Serge for this elaborate solution. I hope to find the time to implement
it before Xmas. I gave it a quick shot but it does not work right away in a REXX script
under DB2/2. Some of the function header keywords must be adapted I think.

--
Luc Le Blanc
Hydro-Quebec
Montreal (Canada)

David Sharpe a crit :

Quote:
> From the IBM intranet, Serge made the suggestion to wrap the beastly SQL into
> a function, which you can then invoke easily in your SQL. Here is his suggestion:

> create function delta_minutes(ts1 timestamp, ts2 timestamp)
>   returns integer deterministic no external action contains sql
> return
> case
>          when hour(ts1) >= hour(ts2)
>             then
>             case when minute(ts1) >= minute(ts2)
>                  then (60 * (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2))
>                     + minute(ts1) - minute(ts2);
>                  else (60 * (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2))
>                     + 60 - minute(ts1) + minute(ts2);
>             end;
>         else
>             case when minute(ts1) >= minute(ts2)
>                  then (60 * (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2))
>                     + minute(ts1) - minute(ts2);
>                  else (60 * (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2))
>                     + 60 - minute(ts1) + minute(ts2);
>             end;
>         end;


> > Hi Luc,
> > You can add in the hours, minutes, seconds, and microseconds by nesting the case expressions. At each unit's level (days, hours, minutes, ...), you need a case when the subtraction of that unit will cause a positive number and a case when it will be a negative number ... similar to the two cases I have for hours.

> > Here is the select for hours:
> >      select case
> >          when time(ts1) >= time(ts2)
> >          then (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2)
> >          else (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2)
> >      from t;

> > Here is the select for minutes:
> >     select case
> >          when hour(ts1) >= hour(ts2)
> >             then
> >             case when minute(ts1) >= minute(ts2)
> >                  then (60 * (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2))
> >                     + minute(ts1) - minute(ts2);
> >                  else (60 * (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2))
> >                     + 60 - minute(ts1) + minute(ts2);
> >             end;
> >         else
> >             case when minute(ts1) >= minute(ts2)
> >                  then (60 * (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2))
> >                     + minute(ts1) - minute(ts2);
> >                  else (60 * (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2))
> >                     + 60 - minute(ts1) + minute(ts2);
> >             end;
> >         end;
> >      from t

> > If you don't do it in the sql, then you have to have the same logic in your application.

> > Hope this helps,
> > David.


> > > I guess it should work but it will truncate hours and minutes by returning a whole number of days. My conclusion is that I must perform myself the conversion between timestamp duration to minutes, taking into acount the real number of days in months, depending on which months are covered. I'll add this in the program that performs the select request (instead of the SQL request itself).

> > > --
> > > Luc Le Blanc
> > > Hydro-Quebec
> > > Montreal (Canada)

> > > David Sharpe a crit :

> > > > Hi Luc,
> > > > Does the JULIAN_DAY() function help? To get the number of hours, try something like:

> > > >     select case
> > > >         when time(ts1) >= time(ts2)
> > > >         then (24 * (julian_day(ts1) - julian_day(ts2))) + hour(ts1) - hour(ts2)
> > > >         else (24 * (julian_day(ts1) - julian_day(ts2) - 1)) + 24 - hour(ts1) + hour(ts2)
> > > >     from t;

> > > > I didn't run it or test it!

> > > > Hope this helps,
> > > > David.


> > > > > My post refers to an old problem submitted by my now gone colleague in this newsgroup. I'm trying to fix this problem and don't get the same results here under OS/2 as obtained below under Windows NT. According to the DB2/2 SQL reference manual, the difference between 2 timestamps is a timestamp duration, not a number of seconds (which I would prefer) and this is exactly what I get. A timestamp duration has a yyyymmddhhmmss,zzzzzz format, so it is a time string, not a number of seconds.

> > > > > For instance, the duration between timestamps
> > > > > 1999-11-17 00:00:00 and
> > > > > 2000-04-01 00:00:00 is
> > > > > 00000413000000,000000 that is 4 months (December, January, February and March) and  13 days (the 13 days in November). This is correct but as I need to convert this into minutes for my application purposes, I must take into account the fact that these 4 months actually have 31, 31, 29 and 31 days. So the number of minutes is actually: 60*24*(31+31+29+31+13).

> > > > > How can I get a second or minute difference between 2 timestamps under DB2/2?

> > > > > --
> > > > > Luc Le Blanc
> > > > > Hydro-Quebec
> > > > > Montreal (Canada)



> > > > > >  > The result is invalid when the difference is larger than 1 month. Are you idea? Is
> > > > > >  > it a bug? If Yes, how sumit a APAR to IBM.

> > > > > >  Here is log of my testing on Windows NT:

> > > > > >  D:\SQLLIB\BIN>db2 connect to sample

> > > > > >    Database Connection Information

> > > > > >  Database server        = DB2/NT 6.1.0
> > > > > >  SQL authorization ID  = DB2ADMIN
> > > > > >  Local database alias  = SAMPLE

> > > > > >  D:\SQLLIB\BIN>db2 create table test (end timestamp, start timestamp) DB20000I  The SQL command completed successfully.
> > > > > >  D:\SQLLIB\BIN>db2 insert into test values(current timestamp,'2000-01-01 00:00:00') DB20000I  The SQL command completed successfully.
> > > > > >  D:\SQLLIB\BIN>db2 create view vtest(diff) as (select end - start from test) DB20000I  The SQL command completed successfully.
> > > > > >  D:\SQLLIB\BIN>db2 select * from test

> > > > > >  END                        START
> > > > > >  -------------------------- --------------------------
> > > > > >  2000-03-28-13.07.34.498000 2000-01-01-00.00.00.000000

> > > > > >    1 record(s) selected.

> > > > > >  D:\SQLLIB\BIN>db2 select * from vtest

> > > > > >  DIFF
> > > > > >  ----------------------
> > > > > >        227130734.498000

> > > > > >    1 record(s) selected.

> > > > > >  Difference between START and END is more than one month - are you saying that the answer of 227,130,734.498 seconds is incorrect (Difference between midnight,
> > > > > >  January 1, 2000 and 13:07:34.498 March 28 expressed as number of seconds)?

> > > > > >  > The result is invalid when the difference is larger than 1 month. Are you idea? Is
> > > > > >  > it a bug? If Yes, how sumit a APAR to IBM.

> > > > > >  I don't see the bug yet - but I will be more than happy to assist you further. As for an APAR - if you have service agreement with IBM - just call 1-800-237-5511 (have your
> > > > > >  customer number ready). Again - I don't see a bug yet - but I did not count number of seconds between those dates.

> > > > > >  Best regards,

> > > > > >  Jan M. Nelken

> > > > > >  -------------------------------------------------------------------------
> > > > > >  Office e-mail: jnelken at ca period ibm period com
> > > > > >  Home  e-mail: nelkenj at attglobal period net

> > > > > >  IBM Certified Solutions Expert DB2 UDB V6.1 for UNIX, Windows and OS/2     - Database Administration & Application Development
> > > > > >  IBM Certified Advanced Technical Expert DB2 UDB V6.1 for UNIX, Windows and OS/2     - Distributed Relational Database Architecture (DRDA)



Mon, 05 May 2003 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Substract 2 timestamp is incorrect (help please)!

2. Substracting two timestamp values

3. substracting a day from timestamp

4. Convert "epoch" to timestamp

5. "Problem in timestamp , optimistic locking"

6. Linking datasets using "similar" timestamps

7. adVarBinary in "timestamp"

8. "Substract" query

9. substracting two different "DATE" values?

10. max of ("...","...","..")

11. Substracting from Same Field Same Table

12. Substracting numbers


 
Powered by phpBB® Forum Software