MySQL DateTime - 'do da ma'

[stanl]

[the quote is from the movie "The Whole Nine Yards" - "Do the Math", but along with the Big Chill it is one of my favorites]

O.K. - Assume 2 call centers, in two time zones. Dialer uses retry counts and next retry timestamps.  ANI's (phone numbers) are loaded and separated by 'project types'. Lists are presented to agents by project.

The kicker is that the last call is saved as EST in one database, while the retries and next time are in another database and next time is stored as GMT.

I know there are registry keys and COM stuff to aid in this, but there is already significant overhead with ODBC drivers between MySQL, Access and SQL Server.

MySQL stores datetimes as numeric with n decimals

So....

2 simple lists (for EST Time) and calculate current day and next day

Code (WINBATCH) Select Expand


t1=TimeYmdHms()
t1=TimeDiffDays(t1,"1899:12:30:00:00:00")
t2=t1+1

hrs="8AM,9AM,10AM,11AM,Noon,1PM,2PM,3PM,4PM,5PM,6PM,7PM,8PM,9PM,10PM,11PM"
thrs="0.334,0.375,0.417,0.459,0.500,0.542,0.584,0.626,0.667,0.709,0.751,0.792,0.834,0.876,0.917,0.959"
hr1=0.0417


Being only concerned with a rounded hour, with hr1 being a 1 hour time frame

Script presents a dropdown of hours, representing an 'As of this hour...'  so assuming 2pm is selected, t1 is added to the thrs corresponding to 2pm to get the EST hour.  This permits a query into the calling tables to see which projects are active as of that hour.

The ANI lists are in separate tables, not a master table, so for each project returned in the above query want to see the retries available in the next n*hr1 hours within the current day.

To aid in the calculation of the GMT times, ini entry for the bias which can be adjusted for Daylight Savings:

Code (WINBATCH) Select Expand


bias = IniReadPvt("Main","bias","0.2083",cINI)


At which point t2 becomes t1+bias

So let's make this more clear:  today March 1 is

42064.000  (carried to 3 decimals)

and 42064.417 would be 10am (which would be t1)

and then adding the bias to the GMT time > t1+2*hr1 hours and less than t2 (next day)

would give a count of the ANI's that can be retried starting in 2 hours [EST].

Doesn't quite seem to be working as planned.... Right now more of a math test. 

[JTaylor]

So is the need a good formula for retrieving the retries from the selected time through the rest of the day..with the retry time being in GMT but the selected time in EST?

Jim

[stanl]

So is the need a good formula for retrieving the retries from the selected time through the rest of the day..with the retry time being in GMT but the selected time in EST?

Jim

I think the calculation(s) are correct (at 3 decimal places), the issue is more the SQL using them. I already know the retries will be incorrect, sort of a Heisenberg effect.  The goal is not to calculate the retries left, but the retries left in the next n hours. 

If t1=EST hour (i.e 2pm) and t2=t1+(nhours*0.0417) ;where nhours could be 1..4

Then [in pseudo-code] "SELECT Count(ANIs) FROM

WHERE retry_time BETWEEN (t1+bias) AND (t2+bias);" should work but I'm getting variations from running the code and looking at specific tables.  I am setting decmals(5) at the beginning of the script but I may need to add something to either the calculations or SQL to get the formulas to execute correctly in the script.

[JTaylor]

If your retry_time is a datetime datatype why not work with real dates and times rather than doing the calculations?   Also, assuming your server is in EST then something like the following should work...if not, then you will need to replace some of the code to use the bias variable.  Otherwise the bias is computed here.   The begin and end datetimes could be computed using the selected start hour and adding the number of hours for which you want a count.

SELECT COUNT(ANI)
FROM table
where timestampadd(HOUR,Extract(HOUR FROM timediff(localtimestamp(),utc_timestamp())),retry_time) between '%begin_datetime%' and '%end_datetime%'

Sorry if I have misunderstood something and this wastes your time.

Jim

[stanl]

If your retry_time is a datetime datatype why not work with real dates and times rather than doing the calculations?   Also, assuming your server is in EST then something like the following should work...if not, then you will need to replace some of the code to use the bias variable.  Otherwise the bias is computed here.   The begin and end datetimes could be computed using the selected start hour and adding the number of hours for which you want a count.

SELECT COUNT(ANI)
FROM table
where timestampadd(HOUR,Extract(HOUR FROM timediff(localtimestamp(),utc_timestamp())),retry_time) between '%begin_datetime%' and '%end_datetime%'

Sorry if I have misunderstood something and this wastes your time.

Jim

Not at all. Appreciate it.  If functions like timestampadd() are native MySQL, my issue is with ADO conversion, similar to an earlier thread I posted where you suggested using a .csv export as an intermediate. Also,  I am including the times as part of the SQL - putting everything in Excel as a table and letting Excel do the datetime formatting.

But on the other hand, you may be correct. If I use MySQL syntax I still get a recordset and Excel can still do the formatting.

The real issue is load balancing.  If 100 agents are dialing project_1 at 2pm and only 400 anis can be redialed in the next 1-2 hours, projects_2 and 3 need to be made available.

It is a lot more complicated than the simple math problem presented, but I felt you would weigh in as you have the MySQL experience.

[JTaylor]

Not sure what all formatting you need to do but MySQL can handle most anything you would want to do on the date and time formatting as well. 

I'm sure you have looked at this but for easy reference:

    http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Jim

[stanl]

Not sure what all formatting you need to do but MySQL can handle most anything you would want to do on the date and time formatting as well. 

I'm sure you have looked at this but for easy reference:

    http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Jim

I actually wanted to avoid the more arcane MySQL syntax in favor of ADO. As it turns out... dumb mw, I was adding the bias not subtracting it. Math works fine.

[JTaylor]

That would make a wee bit of difference  :)

Jim