Splitting times into 2 minute groups

[robjones23]

Hi,

I have another database with "Sent Date / Time" column which contains date / time in the format "yyyy-mm-dd hh:mm:ss.sss". I need to run a query on the "Sent Date / Time" field to output yyyy-mm-dd hh:mm but in groups of two minutes. for example:

Sent Date / Time 2 minute date / time
2005-01-03 00:00:01.117 2005-01-03 00:00
2005-01-03 00:00:04.432 2005-01-03 00:00
2005-01-03 00:01:08.456 2005-01-03 00:00
2005-01-03 00:01:19.458 2005-01-03 00:00
2005-01-03 00:01:28.117 2005-01-03 00:00
2005-01-03 00:02:02.597 2005-01-03 00:02
2005-01-03 00:02:02.597 2005-01-03 00:02
2005-01-03 00:03:34.597 2005-01-03 00:02
2005-01-03 00:03:54.597 2005-01-03 00:02
2005-01-03 00:04:02.597 2005-01-03 00:04

Should look like the above. Any ideas? previously when I needed to just have the hour, i used

Date/Hour: Left([Sent Date / Time],9)+Mid([Sent Date / Time],10,4)+":00"

This output "yyyy-mm-dd hh:00" but i can't use this formula as the minutes need to be in 2 minute gaps......

Any ideas??

Thanks,

Rob.

 

[The_Doc_Man]

Well, ... I can do this but it ain't pretty.

Basically, you need to convert the text time field to a date, then scale it to the "minutes" field you want, then truncate it, then re-float it, then convert it back to true time format, then regenerate it as a time string for logging.

OK, now more detail...

First, you must understand that a date field is a DOUBLE floating-point number that is the days and fractions of a day since a reference date/time, which is nominally midnight of 1-Jan-1900. Days of contemporary times now range in the tens of thousands, 'bout 38K or so. Doubles can store about 55 bits, so the fractional part can be pretty tiny.

Now, as to how that time gets INPUT into your system so precisely, it HAS to come from text, 'cause I can tell you Access won't like the fractions of seconds you listed in your sample. So I know that those AREN'T date fields originally.

So what you have to do is convert the time part up to the decimal point to a Date variable. Which means you would locate the decimal point, take the LEFT$ of everything up to the decimal point, and do a CDate$ on it.

Now, the date is a DOUBLE. You want to scale it, so multiply it by the number of 2-minute intervals in a day, which is 24 * 30, or 720. OK, this integer is on the order of 20 million+, still easily managed by a DOUBLE.

Now convert it to a LONG format to truncate it. Or do a FIX on it or something like that. Doesn't matter which one as long as you are consistent.

Now let's reverse the process. Float the LONG back to a double and divide the result by 720. This is now a "normalized" date. And once it is in that format, you can print it as a date.

Since you posted this as an entry for queries, I'm going to assume you wanted to do this in a query. To my way of thinking, your best bet would be to write a public function to do these steps using VBA because the nesting of so many of the contributing function steps will probably drive you bloody bonkers.

The functions I've mentioned above included INSTR (to find the decimal point), LEFT (to isolate the date and time from the fractions), CLNG (to truncate the scaled number), CDBL (to regenerate the number in the right format), and the required format routine.

In a query, this might be

FORMAT( "insert your preferred date format here",
720.0* CDbl( CLng( CDate( Left$([your date string],InStr( 1, ".", [your date string]) / 720.0) ) ) )

and that has to be all in one query cell. (Time for the expression builder or the zoom dialog box...). Also, you had better double-check my parentheses and the order of the args for the INSTR function, 'cause I always get that one confused. Not to mention the arguments for the FORMAT function.

 

[mresann]

Sometimes, when there are unusual programming situations, I bypass the "robust" methodology and go for the quick and dirty. In this case, the only information that is needed is the "minute" characters. In the sample line of code below,

2005-01-03 00:00:01.117

...the highlighted numbers indicate the minutes. They can easily be extracted by the Mid function as follows.

Mid([Sent Date / Time],15,2)

Now, it's just a matter of comparing that number to see if it falls in the realm of your two-minute intervals. Looking at the sequence below,

Extracted 2-minute

00 00
01 00
02 02
03 02
04 04
05 04

...etc. Now we see the pattern, we can code the algorithm. In this case (remember, we are quick and dirty, not elegant), we note that if the extracted minute is an even number, the displayed minute will be the same number. If the number is an odd number, the displayed minute will be one number less than the extracted number. Therefore,

Format((Int(CSng(Mid([Sent Date / Time],15,2))/ 2) * 2),"00")

...would give you the minute you needed. You can probably code the rest from here, corresponding the hour and date information from one field to the other.

 

[robjones23]

Thanks to you both for taking the time to help out. I decided to go quick and dirty as it's something which isn't a permanent reporting requirement - just something we're experimenting with.

The code Format((Int(CSng(Mid([Sent Date / Time],15,2))/ 2) * 2),"00") worked exactly as described and I now have a column with the minutes.

Many thanks!

Rob.