Rich.Uchytil.com

Progress | Date-Time DataType

Creating a Date-Time DataType. This is the article I wrote for Progressions.

Progress has a Date datatype and Time datatype, but not a Date-Time datatype. You can create one as a decimal. The idea is to add the two together into a decimal field to create the Date-Time datatype. This is really nice because you don't have to do any complex or weird logic to determine if you crossed the 24 hour mark. It would really be useful for calculating the difference between several days.

We have several processes that run at night and track the Last Run Date and Last Run Time, and if it ran successfully. Each morning a report runs displaying processes that didn't complete, the Last Run Date, and the Last Run Time. So I will use these as the examples.

int(today) = an integer number representing today. You can put this into a variable and use the date() function to display it:

v-int-date = int(today). disp date(v-int-date). 11/05/02

time / 86400 gives you a fraction representing part of today. 09:58:52 = 36932. 36932 / 86400 is .4158796296. You can put this into a variable and re-display it:

v-dec-time = time / 86400. disp string(int(v-dec-time * 86400),"HH:MM:SS"). 09:58:52

SO, you can put these two together into one decimal variable:

v-date-time = int(today) + time / 86400. v-date-time = 2452581.1054050926.

Ya, ok, so what, right?

Where this really becomes cool is when you are doing date-time calculations across the 24 hour mark, and especially multiple days. If we were to set the last-run-date to int(today) + time / 86400 (the date and time the process was last scheduled to be run, not today's date and time), then we can easily calculate exactly how many days and HH:MM:SS it has been since this process last run. For example:

Last Run Date = 11/01/02
Last Run Time = 02:31:47
Today = 11/05/02, 09:58:52

last-run-date = int(11/01/02) + 02:31:47 / 86400, or 2452581 + 9107 / 86400, or 2452581.1054050926. v-now = int(today) + time / 86400, or 2452585 + 36932 / 86400, or 2542585.4158796296.

To get the number of days and HH:MM:SS since the last run, we subtract these:

v-diff = v-now - last-run-date.

Next we parse v-diff to get the number of days and HH:MM:SS since this process last ran:

trunc(v-diff,0) - Number of days since last run string(int((v-diff - trunc(v-diff,0)) * 86400),"HH:MM:SS") - HH:MM:SS since last run

So we can see that it has been 4 days, 7 hours, 43 minutes and 45 seconds since this process last ran.

I would write functions as .p/.i's so these could be re-used. By making then functions in a .i, you can call them within the display statement. With .p's, you would have to have an output variable then use that variable in the display statement.

function days-diff returns int (input f-date-time as dec). return int(trunc(f-date-time,0)). end. function time-diff returns int (input f-date-time as dec). return int((f-date-time - trunc(f-date-time,0)) * 86400). end. disp date(int(trunc(last-run-date,0))) column-label "Last!Rundate" string(time-diff(last-run-date),"HH:MM:SS") column-label "Last!Runtime" days-diff(v-diff) column-label "Days!Since" string(time-diff(v-diff),"HH:MM:SS") column-label "Time!Since"). Results: Last Last Days Time Rundate Runtime Since Since -------- -------- ----- -------- 11/01/02 02:31:47 4 07:43:45

Anyhow, it's a neat idea and would make doing date and time difference calculations easier. You would need to make sure it's documented well so others know what's going on and why.