Repository Time Question

I am trying to get db2 to give me the same time format as what is in the repository. I can use TIMESTAMPDIFF(…) to get the difference between a selected date and 12/15/70 at midnight (0:0:0). However, I have found examples - one such example from the repository is that 820897350 should equal 12/19/96 at 4:02:30 pm (16:02:30). However, when I put this date in comparison to 12/15/70, I get 820339350 (db2 uses assumptions like 365 days/year, 30 days/month, etc.). When I calculate it actually, I get 820944150. Obviously both of these are different than what is in the repository. Does anybody know how I can get the time that the repository says it is? What formula/function can I use? Or have you guys checked your repository and found the dates to be correct, and this is just an example error?

Thanks,

Smoky


smokybfgs (BOB member since 2004-06-18)

Ok, so I’m almost positive that Business Objects’ date is not the same as, say, db2’s date. db2 makes some assumptions like 30 days in a month, etc., and perhaps BO doesn’t, but BO is wrong even when compared to the actual number of days that have gone by (not just guessing 30 days to a month). Does anybody know exactly HOW they make their dates?


smokybfgs (BOB member since 2004-06-18)

Don’t forget that there are also time zone differences, and daylight savings time differences. So a date in the summer may be off by (for the central time zone in the US) 5 hours, while the dates in the fall or winter are off by 6 hours.


Dave Rathbun :us: (BOB member since 2002-06-06)

Yeah, I thought about the time zone differences, too. But the numbers I’m getting are like 6 days off or more - a little bit more than a timezone will do :slight_smile: Has anybody gotten their database to reproduce the times like Business Objects? Perhaps I’m doing it wrong - could somebody tell me how they get theirs to work (if its accurate for them, at least)?


smokybfgs (BOB member since 2004-06-18)

Has anybody checked to see if their times are correct, or are other people coming up with funny numbers, too?


smokybfgs (BOB member since 2004-06-18)

Mine look fine in 6.1a when the zone differences are taken into account.


Cindy Clayton :us: (BOB member since 2002-06-11)

Can you tell me how you got the correct date and time? Which database are you using? here’s what I’m doing:

values timestampdiff(2, char( current timestamp - timestamp(‘1970-12-15-00.00.00’) ) )

This is in db2, but gives me an incorrect number.


smokybfgs (BOB member since 2004-06-18)