timestamp/interval trouble

[oleaka]

Hi,

I'm having some trouble understanding timestamps/intervals.

select bigint(startup_time) from ima_dbms_server_parameters
gives: 1227268649
as I understand it, this is the startup time as the number of seconds since 1970.

And
select _date(1227268649) || ' ' || _time(1227268649)
confirms this by giving the correct startup time: 21-nov-08 11:57

Using this date as the argument for interval
select bigint(interval('sec', timestamp('2008-08-21 11:57:00.0')))
gives: 63389255004
This (I think) is the startup time as the number of seconds since the year 0.

select bigint(interval('sec', timestamp('1970-01-01 00:00:00.0')))
gives 62169911586
This should then be the number of seconds between year 0 and 1970.

Then I would expect that taking the startup time as seconds since year 0 and substracting the number of seconds between year 0 and 1970
should yield the original value for startup_time 1227268649.
However it appears to be of by 7925231 seconds (a little over 91 days).

63389255004 - 62169911586 = 1219343418

1227268649 - 1219343418 = 7925231

What is wrong with my reasoning here?

Thanks
Ole

[Bodo]

91 days seems to be the correct difference between the two selects:

select _date(1227268649) || ' ' || _time(1227268649)
confirms this by giving the correct startup time: 21-nov-08 11:57

Using this date as the argument for interval
select bigint(interval('sec', timestamp('2008-08-21 11:57:00.0')))

The first date is 21-nov-08 , the second one 21-aug-08,
so the 3 months sum up to 91 days.

[oleaka]

Haha

[oleaka]

Ok, I still can't get the numbers to add up, so I'm going to give this one more shot

The number of seconds from year 0 to 2008-11-21 11:57:00 is
select bigint(interval('sec', timestamp('2008-11-21 11:57:00.0')))
=> 63397144242

The number of seconds from year 0 to 1970-01-01 00:00:00 is
select bigint(interval('sec', timestamp('1970-01-01 00:00:00.0')))
=> 62169911586

The number of seconds from 1970 to 2008-11-21 11:57:00 is 1227268649
select _date(1227268649) || ' ' || _time(1227268649)
=> 21-nov-08 11:57

Then, taking the number of seconds between year 0 and 2008-11-21, and substracting the number of seconds between year 0 and 1970
should yield the original value: 1227268649.

But
63397144242 - 62169911586 = 1227232656
1227268649 -1227232656 = 35993

35993 seconds => approx 10 hours off.

Why?

Thanks again
Ole

[Bodo]

I believe this difference has something to do with using timestamps instead of intervals.

Here is what I did (all with II_TIMEZONE_NAME=GMT,
as the startup_time is in GMT):

select bigint(startup_time) from ima_dbms_server_parameters
Result: 1227173375

select _date(1227173375) + '_' + _time(1227173375)
Result: 20-nov-08_09:29

select bigint(interval('sec', date('01-jan-1970 00:00:00')-
'01-jan-0001 00:00:00'-'1 year'))
Result: 62104212647
These are the seconds since 01-Jan-0000
(I couldn't use 01-jan-0000, because this is an invalid date,
so I used year 0001 and subtracted a year)

select bigint(interval('sec', date('20-Nov-2008 09:29:00')-
'01-jan-0001 00:00:00'-'1 year'))

Result: 63331385987

Now the calculation:
63331385987 - 62104212647 = 1227173340

Startup time value was 1227173375.

The difference of 35 seconds is due to the limited granularity of the _time() function, which doesn't return seconds (format "hh:mm").