I am using application.getServerTimeStamp() to get a timestamp for a mysql datetime field. However, at times servoy will store a bogus date and time in my database. This happens randomly and I cannot reproduce,but know that it is happening. When I run application.getServerTimeStamp I get {Thu Oct 28 14:59:10 CDT 2010}. My server time is accurate and I have never had problems with the server time. I believe the problem is when servoy converts {Thu Oct 28 14:59:10 CDT 2010} to {2010-10-28 14:59:10} at save time. Any and all help would be appreciated.
Is this really random, or does it happen only for some of your clients? (for example only clients from a certain timezone, only clients of type SC or WC…)
Is the stored date completely unrelated to what it should be or is it only off by a number of hours (in case you can find out)?
All the clients are on the local network, time synced with domain controller, central time zone and are running the smart client (about 50 users). Furthermore, users cannot change the time on their local system due to permissions. I cannot tie the bogus date and times to a specific client machine. What’s weird is I cannot replicate the error, it happens randomly throughout the network. When bogus dates and times are found I will reissue the getServerTime and the correct value will be stored. I am using the application.getServerTime() to pull the current date and time in a button on my form. I also tried writing my own time function with a mysql query, “Select Now();”. The query generated the right date and time to pass to the datetime form field. However, I still got bogus data at times when I saved the data from servoy to the mysql database. My guess is that it has to do something with the conversion of servoy datetime values to mysql datetime values. Is it possible the jdbc could be suspect, running version 5.1.7. I even made a function that retrieves the server time twice and verifies against each other before writing to the db. If the problem is in the saving to the db then my issue is not generating the correct time but storing in the mysql datetime field. Is there a way to format the application.getServerTime() before passing to the mysql datetime field. As of right now I just pass the result of the function into my servoy datetime field which is then saved to the mysql database.
//below converts to string and will errror out when passed to a datetime field in servoy
//this is the format mysql stores DateTime values in .
var mysqlDateTimeFormat = utils.dateFormat(application.getServerTimeStamp(),‘yyyy-MM-dd HH:mm:ss’);
//I need a resolution for this, thanks in advance for all your help.
Well you can store what application.getServerTimeStamp() gives you by using a form/foundset field or you could use an query to store the value but you should get the same result…
So mysqlDateTimeFormat string would contain expected Date string, but if you add the same value (as returned by application.getServerTimeStamp()) to the DB via field it’s bogus?
lhale:
var mysqlDateTimeFormat = utils.dateFormat(application.getServerTimeStamp(),‘yyyy-MM-dd HH:mm:ss’);
//gets the time and date of fab
var date = application.getServerTimeStamp();
//fabdate is a dataprovider from db on the form (servoy generates timestamp as [Fri Oct 29 09:47:09 CDT 2010])
fabdate = date;
//I do some other code and then call my save function which returns true or false
//saves by calling databaseManager.SaveData()
globals.close = globals.Validation();
So nothing special… I am curious to see exactly the bogus date vs expected date when this happens. Please post them when you see this happen again.
It should work considering that you have no converters, validators, special format in form fields linked to that dataprovider that might affect that value and the value is not altered before the save in your code…
Example of bogus time. Employee works (3:30pm-12:00am) however the fab time is at 2:46:01 AM 10/28/10 when it should of been before midnight on 10/27. We know for sure that this was completed before their shift change at 12:00AM. The same employee has another issue a few seconds later 2:46:16 AM 10/28/10. This should be impossible seeing that the employee was already off work. We also have timestamps that give a time in the afternoon when it is still morning. The kicker is I have verified suspected machines and they all give me the correct server time. It seems that when things go wrong the date may be one day ahead and the time off by several hours. There should be no timezone issues with our clients and network time is correct.
Is there a way to format the timestamps to mysql format to eliminate possible problems in dates?
NOTE***
I display the fields on the form in the following format in the servoy properties : h:mm:ss a MM/dd/yy
This is only the display and should not affect the value stored to the DB.
Hmm, timezones keep coming to mind because of difference in hours, but if the same client / user generally has correct times and this only happens from time to time then it must be something else…
Maybe compare the date you get with “Select Now()” with the application.getServerTime() and if it’s off by more then a few sec/minutes log both dates, maybe getTimestamp() as well. You could also compare it with employee work interval to see if it’s wrong - to debug it further.
I don’t have any idea at this time on what (inside Servoy) could cause a random wrong storing/getting of servertime. This problem needs to be isolated further - to get it reproducible somehow.
I don’t think application.getServerTimeStamp or Select NOW () is generating the wrong time.I believe when servoy passes the datetime to the back end db it is randomly changing it.
I don’t want to use raw sql to set this date. By this I mean running a backend sql query and inserting with sql . That would require a flush of the entire table which has many records. Even if I compare the times and they match, servoy would still have to pass the correct values to mysql.
function GetDateTime()
{
//mysql query to get datetime from server
var query = 'SELECT NOW();';
var result = databaseManager.getDataSetByQuery('database', query, null,1);
var date= result.getValue(1,1);
//if datetimes don't match up to the minute keep looping until they do
while(utils.dateFormat(application.getServerTimeStamp(),'yyyy-MM-dd HH:mm')!= utils.dateFormat(date,'yyyy-MM-dd HH:mm'))
{
result = databaseManager.getDataSetByQuery('database', query, null,1);
date = result.getValue(1,1);
}
return date;//return good date
}
This will ensure that a good date is generated;however, problems could occurr when servoy saves this datetime to database.
You could add some more code to check that the date is stored correctly in the DB. After storing and saving it, load it back using a query (databaseManager.getDataSetByQuery(…)) that searches by some other column and check that it’s the same date. Also use date2.getTime() - date1.getTime() < 60000 to check that two dates are less then 1 min apart - to avoid situation when the date you get by query is 1 sec apart from server time but in different minute. This should help isolate the problem further if you log the event with info to the server when either of these happen… (either to a log table or other methods)
Maybe you should also limit the number of while loops to avoid hanging the app. indefinitely (for example after 1000 loops that fail to bring times close to each other choose one of the two dates).
Thanks for the help. I added your suggestion for the .getTime(). However, I might have to think a little bit on how to verify dates in db then update them if they are incorrect. I would like to do this in a global method.
Below is updated GetDateTime function:
//mysql query to get date from server
var query = 'SELECT NOW();';
var result = databaseManager.getDataSetByQuery('database', query, null,1);
var date = result.getValue(1,1);
var i = 0;
while((application.getServerTimeStamp().getTime() - date.getTime() >= 60000) && i < 100)
{
result = databaseManager.getDataSetByQuery('database', query, null,1);
date = result.getValue(1,1);
i++;
}
return date;
Any ideas on how to code a global function that will do a save to the database for that specific field and if it’s bogus rollback and generate a new one. The reason I need this is because at random bogus times are being written to my database when I save the result of any datetime field. I do not want to code a unique function for each place I call the application.timestamp() command.This would very timely. Below is what I have to verify timestamp generation;
//mysql query to get datetime from server
var query = 'SELECT NOW()';
var result = databaseManager.getDataSetByQuery('database', query, null,1);
var date = result.getValue(1,1);
var i = 0;
//compare generated timestamps to the minute
//if not valid continue looping for valid datetime up to 100 times
while((application.getServerTimeStamp().getTime() - date.getTime() >= 60000) && i < 100)
{
result = databaseManager.getDataSetByQuery('database', query, null,1);
date = result.getValue(1,1);
i++;
}
return date;
This will generate a valid datetime in servoy. Now I need a global function to make sure the same date is written to the db and if not rollback. I need this in several areas of the database. As a result, a generic function is needed to address the incorrect dates. The kicker is I cannot replicate bogues dates being written. I am only able to find them when I query or users report.
A user reported bogus datetimes this morning. She completed her work at around 8:00AM 11/2 but servoy is showing 6:00PM 11/2. This is a 10 hr diff. I have ten records with bogus dates. Some records have even changed their value without user intervention. For example, a record reported a time of 6:00 pm then changed to 12:00pm today. Why would a clients datetimes be correct all the time except at random moments. This is very frustrating. I need some help on this one…
please look at the thing i asked.
What is your value of the servoy.use.client.timezone?
Is that true or not?
If that is false and your clients and server are in different timezone’s you will get constantly different things because times are recalculated for every client to see the time in there timezone
but what you want is to always see the same textual time that you only get if the property is true.
I already verified the timezone was set to true. However, this morning my app server decided to change to utc time and push all my times off by 10hrs. Just to make sure, I reset the timezone to true and restarted the server. It looks like the server is set to CDT time now.Could something be intermittently changing my times to utc? How do you change the time from Central Dailylight to UTC??? We are running on a local LAN. The users cannot even change their clock. I sync the app server every hour to verify the time is correct. In command line my server is displaying the right date every time (Tue Nov 2 10:17:43 CDT 2010).
that is a well know bug… that is already solved please upgrade to the latest version of servoy for that, also the latest 4.1.x should also already have some fix for this, dont know if it has the complete fix yet
It happens when you restart the server and a new client connects to it and the server generates pack files for your client so that the download is as small as possible.
Problem is that the latest webstart clients download jars concurrently so multiply at one time, The pack code of java sets the timezone quickly to utc… (and back) but that is not thread safe
This is all fixed in the latest versions of servoy.
(or you just have to make sure when you start the app server, connect with a clean client once (so that it does a full download))
then make sure that the the timezone is set correctly, if not do it once more.
