Computing a date knowing just the time of day.

[ellreit]

I've been racking my brain on this for a while and can't find an easy solution. My program reads in a text file which includes a “time of day” field, without a corresponding date (e.g. one record may have 2:12:35 AM, and another record may have 11:36:58 PM). The times are in no specific order. Knowing the date and time that the program started (e.g. the program may start at 1:31:14 AM on 5/20/05), I need to compute the date of those records in the text file. Using the examples, the 2:12:35 AM record would be 5/20/05 and the 11:36:58 record would be 5/19/05. I think the logic should somehow relate the input time to midnight as well as the time the program started to accurately compute the date. The program may start at any time of the day, and the input records may have any time of the day. Another example, suppose the program starts at midnight (00:00:00 AM) on 5/20/05 and a text record comes in with 11:59:59 AM. Do I consider the date to be 5/19/05 or 5/20/05? Similarly, if the text record has 12:00:00 PM, is the date 5/20/05 or 5/19/05?

Thanks for any help.

Elliot

 

[MacCaillean]

Hi Elliot

Assuming your program runs daily and that it picks up only text files which have already been produced, can you not simply assume that any time which is less than the program start time happened today, whilst any time that is greater than the program start time happened yesterday?

As for your midnight example, I think Access sees midnight (00:00) as the start of the day, so 23:59 and 00:00 are in different days.

Regards - Mac

 

[ellreit]

Thanks for the reply Mac. I did consider waht you are saying, but suppose the program starts at 23:30:00 (11:30 PM). Any record with a time between 23:30:01 PM and 23:59:59 is greater than 23:30:00 but still is on the same day as when the program starts. Even records with a time of 23:29:00 is less than the start time but is still onthe same day. Any record after midnight (00:00:00 and after) will be less than the start time but I have to consider as the next day, not the previous day. Even a record coming in with a time of 11:29:59 I have to consider as is the same day as the start time of the pragram although it is less than the start time. I think I need to consider some threshold before and after the start time to consider any record as being on the same, previous, or next day but am having difficulty setting the parameters for coding.

Thanks again.

 

[MacCaillean]

Hi Elliot

Sticking with the assumptions I made in my first post, if your program runs at 23:30:00 it won't pick up a text file created at 23:59:00 until the next day, by which time that file will have been created yesterday, and my suggestion works.

You are right that you need to consider a threshold, but you are implicitly doing that in this scenario.

Maybe my assumptions are wrong?

Regards - Mac

 

[SkipVought]

Just to piggy back on Mac's replies, consider that any time GREATER than or EQUAL to the program start time is DAY 0 and any time LESS than the program start time is DAY 1.

Add that to you time values and everything will work out.

dim NewTOD as Date
if [ProgStart] <= [TOD] then
   NewTOD = [TOD]
else
   NewTOD = [TOD] + 1
end if

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Eupher]

If the time the program runs is variable, I don't think you can know with certainty what day you are dealing with. What happens if more than 24 hours elapse between program runs? For instance, if on 5/19 the program runs at 09:00:00, we assume any time value less than (or equal to) 09:00:00 is 5/19 and any time value greater than 09:00:00 is 5/18. Fine so far. But suppose the following day the program runs at 23:00:00? Is 22:15:00 on 5/19 or 5/20? What if you now have two 22:15:00's?

Ken S.

 

[SkipVought]

Ken,

You've got a good point.

Maybe you have to consider the ORDER of the input data.

Don't ADD 1 until you encounter a [TOD] < [PrevTOD]

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Eupher]

The times are in no specific order.

The program may start at any time of the day, and the input records may have any time of the day.

Given those parameters, I maintain it is impossible to know the date with certainty. I see two solutions: 1) require that the date is included in the source data (best choice); or 2) require that the program be run at exactly the same time every day (not crazy about that option - too many things can happen to mess it up). Sequencing the times might work, but doesn't account for possible duplicate times if the period exceeds 24 hours.

Ken S.

 

[Oolongtea]

I agree with Eupher that you need an estimated runtime so that you know if 10PM and 11PM are plus 1 hour or minus 23 hours. Without the expected runtime there is no way to know for sure and this is still not going to be 100% accurate.

Really the only (and proper) way to be sure is really to go in and make the timestamping mechanism log the date as part of the timestamp.

 

[MacCaillean]

The previous points are all well made.

Lacking the ideal solution, however, a further assumption I should have made was that once a text file has been 'used' and its data imported, it is either removed from the target folder or it is renamed in such a way that it is ignored the next time the program runs.

Maybe such an action is outside the scope of Elliot's program's functions, but it would let the greater than / less than solution work.

Maybe you can clarify, Elliot?

Regards - Mac

 

[ellreit]

First, thank you all for replying. Hopefully I can clear up any questions. I guess I should have provided more detail sooner.

The program is designed to run 24X7, but may start at any time of the day or night after a power failure, network failure, or a restart after system maintenance. Once the program is running for a while, the issue of computing dates goes away as the TOD in the records coming in will be further away from the starting time of the program and at that point every record it reads will always be after the day the program starts. The problem comes in to play soon after the program starts.

Let's deal with the period of time when the program starts. There may be one or multiple text files that the program has to open up and read. Even after it finishes the file(s) it first encounters, there may be another one coming in a few minutes later. Yes, the program will move any file it processes out of the way so as not to process it again. It's not important to know the TOD of the file itself because each file will have many records within it and each record has a TOD field (only the time and no date). I'm interested only in the TOD field in each record. The TOD field represents the TOD (again, no date) that someone logs into a network and that statistical record is not written to the file until the person logs off. A file is created by the network every 60 minutes and contains a record of anyone who logs off the network in that 60-minute period, even though the user may have been logged on for hours and hours. There is a duration field as well indicating how long the user was logged on. I have not considered this, but perhaps I can make use of that piece of information in my logic. Again, the TOD field represents the TOD (in hh:mm:ss with no date) that the person initially logged on. So you can see that the records are in no specific order and the TOD field may be many hours before the time the person logged off.

Consider these scenarios:
Program Starts at 23:45:00 on Day 0 (the current day).
The text file has five records:
Record 1 has TOD = 22:00:00.
Record 2 has TOD = 23:50:00.
Record 3 has TOD = 00:00:00.
Record 4 has TOD = 00:15:00.
Record 5 has TOD = 11:00:00.

The desired result is that Records 1, 2 and 5 will be Day 0. Records 3 and 4 will be Day 1 (current day plus 1).

Now consider this scenario:
Program starts at 00:15:00 on Day 0.
The text file is the same as above.
In this scenario, the desired result is that Records 1, 2 and 5 will now be Day -1 (current day less 1). Records 3, 4 are now Day 0.

So I think it's more than just knowing if the TOD of a record is less than, equal to, or greater than the Starting time of the program.

Thanks again in advance.

Regards to all.

Elliot

 

[Eupher]

So I think it's more than just knowing if the TOD of a record is less than, equal to, or greater than the Starting time of the program.

I concur and stand by my earlier assessment: absent a datestamp, and with variable run times of the program, there is no way to know with a satisfactory degree of precision what day you are dealing with.

Ken S.

 

[MacCaillean]

You are right, Ken, but using the extra information, is there anything wrong with the following ... ?

[blue]f[/blue] = Date/time of the record file
[green]s[/green] = Session duration of individual record in a file
[red]c[/red] = f - s = Calculated date/time of log-in
r = Program run date/time

d1 = Difference in days between r and c
a = Actual log-in time

d2 = Actual difference in days between r and a; therefore

d2 = d1 + 1 [IF a > Timepart (c)]
or
d2 = d1 [IF a <= Timepart (c)]


Notes:
Using the date and timestamp of the log file and the duration of each session within the file, we can get a calculated log-in time and date.

We can compare our calculated log-in time/date against the program run time/date and calculate the difference in the number of days.

Because the log file is produced every hour, there is a potential error in our calculated log-in time of up to 60 minutes. So to cope with the scenario where the actual log-in time is in a different day from the calculated login-in time, we compare them. If the actual time is greater than the calculated time, we can say that they are in different days and minus 1 from the difference in days figure we previously calculated.

I have modelled this in Excel and it seems to work!

Regards - Mac

 

[Eupher]

Mac,

I think you are onto something there, I had not considered the datetimestamp of the log file itself. Yes, if you have a date on the log file and the duration of the session in the data, your formulation should work.

Ken S.

 

[ellreit]

To all:

I think you've pointed out the right idea to look into and I will ponder over your suggestions and experiment a bit.

Thanks once again for all your help.

Elliot