We had a custom session that added a range of dates to the job calendar. This worked under older versions of Baan, but since we upgraded to Baan IVc4, it does not handle the times very well

This is because the table (ttaad507) was changed to use UTC. I've been reading through many threads here and on the WIKI about time conversion, but I still cannot get the functions that covnert dates to times to work.

The user inputs a range of dates, and the time (in its own field using tctmhs). I need to covnert this integer to its equivalent in UTC, but I can't get this to work.

Here is what I've tried:

This version adds the hours offset from GMT to the time integer. This actually worked until it got to the end of daylight savings time, then the results were off by an hour.


declaration:
table tttaad507 | MRP Calendar

extern domain tcdate date.f
extern domain tcdate date.t
extern domain tcdate sel.date
extern domain tctmhs run.tm

choice.cont.process:
on.choice:
sel.date = date.f
while sel.date <= date.t
add.to.calendar()
sel.date = sel.date + 1
endwhile

functions:

function add.to.calendar()
{
long time.offset = 400 | offset from GMT

db.retry.point()
select ttaad507.*
from ttaad507 for update
where ttaad507._index1 = {:calc, :sel.date, :run.tm}
order by ttaad507._index1
selectempty
ttaad507.calc = calc
ttaad507.date = sel.date
ttaad507.time = run.time + time.offset db.insert(tttaad507, db.retry)
commit.transaction()
selectdo
endselect
}

This version tries to use the local.to.utc() function, but the results seem random.


| ... other code as above ... |
function add.to.calendar()
{
long utc.days
long utc.time
long loc.time
long ret
loc.time = run.tm * 3600 | Convert entered time to seconds

ret = local.to.utc(utc.days, utc.time, sel.date, loc.time)
db.retry.point()
select ttaad507.*
from ttaad507 for update
where ttaad507._index1 = {:calc, :sel.date, :run.tm}
order by ttaad507._index1
selectempty
ttaad507.calc = calc
ttaad507.date = sel.date
ttaad507.time = utc.time db.insert(tttaad507, db.retry)
commit.transaction()
selectdo
endselect
}

Thanks,
RD

Hi there,

I just had a look at ttaad507 in our 4c4 system.
The two fields used are date (type date) and time (type int).
So date is the number of days since 01.01.0001 and can be filled using the function date.to.num

long DATE.TO.NUM( long yearno, long monthno, long month_dayno ) This function returns the number of days since 01-01-0001 until the given yearno, monthno and month_dayno.

Example:
long no_of_days
no_of_days = date.to.num( 1991, 04, 20 ) | Returns 726942

The field time most probably contains the number of seconds since 00:00 and can be filled easily by multiplying the hours with 3600, adding minutes * 60 and adding remaining seconds.
In some tables another method is used (for instance ttaad500, job data) to store time by multiplying hours with 1000 and adding the minutes.
As ttaad507 is empty in our system, I cannot tell for sure which method is used, but if you look at one entry in your system you can easily tell which one you have to use.

hth

Norbert

I'm not sure this would work.

See thread:
http://www.baanboard.com/baanboard/showthread.php?t=5314&highlight=tttime

Also see on Infor's web site, Solution ID 119154 and 157136, which describe the introduction of UTC time to the job tables.

If I go to the standard Baan session for entering these dates (ttaad5107m000) and check the results in GTM, here is what I see:

Date: 6/21/2007
Enter Time: 00:10 (i.e. 10 minutes after midnight)
GTM shows: 0410

Date: 12/31/2007
Enter Time: 00:10
GTM shows 0510.

Infor solution 157136 explains that UTC does not have daylight savings time, so this makes sense. I just need to find a way to mimic to conversion Baan is doing ttaad5107m000.

Hi there,

seems to me that the second method is used (hours * 100 +minutes) if you are 4 or 5 hours away from GMT depending on summertime.
As I mentioned, in ttaad500 this is how time is stored on our system.
So a job that is scheduled for 18:22 has 1822 in the field time in ttaad500 on our system. Strangely enough, I cannot see any entry here with one hour difference regardless whether the month lays within summer or wintertime.
I found entries in ttaad507 meanwhile (was looking in company 000 this morning) and they match the time displayed in the sessions as described above: hh*100 + mm.
As far as I understood previous posts, from Baan V on another format is used to store time, but on 4c4 I always found it pretty much straightforward.

good luck

Norbert

I realize the way I converted to seconds above was wrong. But even with this correction, it does not return the correct time.

This is the correct code:

if run.tm > 100 then | After 1AM
loc.time = ((run.tm / 100) * 3600) + ((run.tm\100)*60)
else | Before 1 AM
loc.time = ((run.tm\100)*60)
endif
local.to.utc(utc.days, utc.time, sel.date, loc.time)

What service pack of Baan are you using? I think it is in some of the more recent ones they have converted to UTC.

But I figured out where I was going wrong. I did not realize that local.to.utc returns the UTC time in seconds since midnight. Here is the code that works:


|Convert local time to UTC time
long utc.days
long utc.time
long loc.time
long cal.time

| convert entered time to seconds since midnight
if run.tm > 100 then | if after 1 AM
loc.time = ((run.tm / 100) * 3600) + ((run.tm\100)*60)
else | if before 1 AM
loc.time = ((run.tm\100)*60)
endif
local.to.utc(utc.days, utc.time, sel.date, loc.time)
| utc time is in seconds since midnight. Convert back to time.
cal.time = ((utc.time / 3600)*100) + ((utc.time \ 3600) / 60)

There is a mistake in your solution. If run.tm = 100 it returns the wrong time. You don't need the if-command the then-path will be right for all values.

You're right. Thanks.