KACE Product Support

I have now found this out, teh query required to run is "SELECT @@version;" and the database version is listed as "5.1.54" in case anyone requires this in future.

yes
select version() also works.

To be clear this command gives the version of mysql that is running. The database doesn't technically have a version itself. The database runs on MySQL and it is used by our software. Both MySQL and our software have versions, but again the database itself does not. A 32-bit kbox will have a different version of Mysql running than a 64-bit kbox. Different versions of our product do use a slightly different database structure, but there is no database version to check. For example, a database on a 32-bit 5.3 kbox will be different from a database on a 64-bit 5.3 kbox.

In dave1kelsey's question I think the motivation is to know the database engine version so that they can use appropriate functions and optimizations in their reports. For example, a common one is that the function TIMESTAMPDIFF doesn't exist in kboxes that are running MySQL 4.x. Another common one is that cross-product join syntax requires parentheses in MySQL 5.x.

Lastly, it's a bit confusing that the MySQL versions have closely matched our version numbers for the last few versions so watch out tor that as well.

GillySpy,

You are spot on, our MD's require a report that shows how long it took to took for jobs to be dealt with (time from opened to stalled, closed etc), our SQL are having issues creating this using the TIMESTAMPDIFF function.

Is the above a common request and if so do you know what query we can run to add this report in?

TIMESTAMPDIFF is convenient but I never use it for the compatibility reasons. I'd like to because it is easier to work with because it is more versatile. I use DATE_SUB / DATE_ADD or UNIX_TIMESTAMP arithmetic.

E.g. if you are evaluating it in a where clause then use DATE_SUB / DATE_ADD
TIMESTAMPDIFF(DAY,time1,time2) =X is practically equivalent to
time2=DATE_SUB(time1, INTERVAL X DAY)

e.g. If you are displaying the result of TIMESTAMPDIFF then use UNIX_TIMESTAMP
TIMESTAMPDIFF(DAY,time1,time2 is equivalent to
FLOOR((UNIX_TIMESTAMP(time2)-UNIX_TIMESTAMP(time1))/60/60/24)

So you can see how TIMESTAMPDIFF is convenient, especially in the second example.

thanks for the information