Time Problems

security Supervisor (4.x - 6.x)
1

Ok, besides the fact that nobody has enough of it, I have another time problem with the repository. I can’t get the dates to come out right, and I need some help telling me exactly what to do to correct my math - or telling me that yes, in fact, the times are wrong. I’ve posted before with little result concerning the date, and I still can’t get our repository to come out correctly. But I also can’t get their examples to work. Take this, for instance, straight from the documentation:

If 820897350 is stored in the repository, it corresponds to (in UTC format, do not forget): 12/19/96 at 04:02:30 PM.

If I break this down, I can go at it like this:

From December 15, 1970, there have been 820897350 seconds until the above date. That means there have been:

820897350 / (60 seconds) minutes, or
820897350 / (60 seconds * 60 minutes ) hours, or
820897350 / (60 seconds * 60 minutes * 24 hours) days, which gives us

9501.127 days (rounded).

Suppose we assume 365 days a year - so we need (26 years * 365 days) days, or 9490 days. Now we can add on the extra four days from the 15th of a month to the 19, giving us 9494, != 9501.

Next, lets assume thirty days in a month, meaning between December 15, 1970 and December 19, 1996, there has been (12 months * 26 years) months, or 312 months, plus four days and some change. That’s (312 * 30) + 4… days, or 9364 days. Not correct.

Lets find out exactly how many days are between these two - There’s 9501 days, but 16 hours and some change - about 820944150 seconds.

When I use db2’s timestampdiff forumula, I come up incorrectly, too:

values timestampdiff( 2, char( timestamp( ‘1996-12-19-16.02.30’ ) - timestamp( ‘1970-12-15-00.00.00’) ) )

gives me 820339350. How can I get the actual correct date out? I’m getting similar incorrect numbers inside my repository, too. Any help on how you get the correct time, or even if you get the correct time or the wrong time, would be appreciated.

smokybfgs (BOB member since 2004-06-18)

2

If I was using Oracle, I would do something like this:

To_Date(‘12/15/1970’,‘mm/dd/yyyy’) + (820897350 / (60 seconds * 60 minutes * 24 hours))

Can you do something like this in DB2?

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

3

I can add dates in db2 (doesn’t look as clean as Oracle’s), but my question is - when you do this in Oracle, do you get back the date they say you should get back? Because I’m coming up with a different number…

smokybfgs (BOB member since 2004-06-18)

4

This is what I get in Oracle 9i:

 Select 
To_Char (To_Date('12/15/1970','mm/dd/yyyy') + (820897350 / (60 * 60 * 24 )), 'Mon-dd-yyyy HH24:mi:ss') 
from DUAL;

--------------------
Dec-19-1996 03:02:30

Andreas :de: (BOB member since 2002-06-20)

5

The problem is that the repository stores this as the number of seconds since December 15th, 1970. That means you need to determine how many seconds there are. If you can determine the number of days between your date and midnight of this day, you can multiply it by the 86400 seconds in a day to figure out the number you need to add.

The same goes for the timestamp. If you have 1.5 days, then it will be the 86400 for 1 day plus the 43200 for the half day.

The other thing to realize is that the timestamp is in UTC format, so you will have to take into consideration your local system times and add/subtract the appropriate number of hours, but you should be pretty close.

Finally… Don’t forget that we have something called: leap years. :smiley:

1972, 1976, 180, 1984, 1988, 1992 and 1996 all get an extra day and that will bring your 9494 up to 9501.

digpen :us: (BOB member since 2002-08-15)