mySQL Timestamp conversion in XESame

navid

Hi ,

I am trying to convert data from MySQL data base to XES format using XESame tool. I have problem by timeStamp. the data type of this column in MySQL is DATETIME. The timestamp in the table is appeared like 05/01/2000 01:14:59. Inside XESame at first I wrote :
navid_table.timestamp[{dd/MM/yyyy hh:mm:ss}]  as TraceID and I received the following error:
2012-05-11 11:13:59: (DEBUG) We are about to run the following query to detect the traceID type: SELECT navid_table.timestamp[{dd-MM-yyyy hh:mm:ss}] FROM navid_table   WHERE navid_table.timestamp[{dd-MM-yyyy hh:mm:ss}] LIKE '123456ABCDEFG'
2012-05-11 11:13:59: (DEBUG) We are about to run the following query to extract the items for Event: cellID:
SELECT navid_table.timestamp AS 'traceID', '0' AS 'orderAttribute' FROM navid_table   WHERE (navid_table.timestamp IN (2000-01-05 01:14:59.0))
2012-05-11 11:13:59: (ERROR) There is an error in the query (turn on 'debug' mode to see it) to fetch the information for Event: cellID
2012-05-11 11:13:59: (ERROR) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '01:14:59.0))' at line 1
then I changed the sign / to -  and I added SSS as ms at the end of the timestamp :   navid_table.timestamp[{dd-MM-yyyy hh:mm:ss.SSS}]
I received again this error:  
2012-05-11 11:26:21: (DEBUG) We are about to run the following query to detect the traceID type: SELECT navid_table.timestamp[{dd-MM-yyyy hh:mm:ss.S}] FROM navid_table   WHERE navid_table.timestamp[{dd-MM-yyyy hh:mm:ss.S}] LIKE '123456ABCDEFG'
2012-05-11 11:26:21: (DEBUG) We are about to run the following query to extract the items for Event: cellID:
SELECT navid_table.timestamp AS 'traceID', '0' AS 'orderAttribute' FROM navid_table   WHERE (navid_table.timestamp IN (2000-01-05 01:14:59.0))
2012-05-11 11:26:21: (ERROR) There is an error in the query (turn on 'debug' mode to see it) to fetch the information for Event: cellID
2012-05-11 11:26:21: (ERROR) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '01:14:59.0))' at line 1
I appreciate any help in advance

JBuijs

I Navid,

I'm afraid that the solution is quite simple: add a space between the timestamp column and the [{.... timestamp format. XESame needs this to detect the table column and the timestamp code to correctly create the SQL query for the database.

navid

Thanks for quick reply
It seems the problem is not because of that (not so simple ). I tried the followings:
1- navid_table.timestamp [{yyyy-MM-dd hh:mm:ss.S}]
2- navid_table.timestamp [{yyyy-MM-dd hh:mm:ss.SSS}]
3- navid_table.timestamp [{dd-MM-yyyy hh:mm:ss.SSS}]
4-navid_table.timestamp [{dd-MM-yyyy hh:mm:ss.S}]
5-navid_table.timestamp [{dd-MM-yyyy hh:mm:ss}]
in all of them I had the same error.
2012-05-11 11:26:21: (ERROR) There is an
error in the query (turn on 'debug' mode to see it) to fetch the
information for Event: cellID
2012-05-11 11:26:21: (ERROR) You have
an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '01:14:59.0))' at line 1
 it seems that the problem is on 01:14:59.0 side.

JBuijs

Hi Navid,

Could you turn debug mode on and paste the query here?

navid

this is the query :
navid_table.timestamp [{yyyy-MM-dd hh:mm:ss.S}] ,the timestamp column in MySQL is like : 05/01/2000 01:14:59
I am trying a simple query on database just to go forward step by step
and this is the console result:
2012-05-11 12:19:40: (PROGRESS) Starting step 1 of 5: initialization.
2012-05-11 12:19:40: (PROGRESS) Starting step 2 of 5: Extracting log info.
2012-05-11 12:19:40: (DEBUG) We are about to run the following query to extract the items for Log:
SELECT 'PalletNumber' AS 'concept_name' FROM navid_table  
2012-05-11 12:19:40: (PROGRESS) Starting step 3 of 5: Extracting traces.
2012-05-11 12:19:40: (DEBUG) We are about to run the following query to extract the items for Trace:
SELECT DISTINCT navid_table.timestamp AS 'traceID', 'palletID' AS 'concept_name' FROM navid_table   WHERE navid_table.value='start'
2012-05-11 12:19:40: (PROGRESS) Starting step 4 of 5: Extracting Event: cellID.
2012-05-11 12:19:40: (DEBUG) We are about to run the following query to detect the traceID type: SELECT navid_table.timestamp [{yyyy-MM-dd hh:mm:ss.S}] FROM navid_table   WHERE navid_table.timestamp [{yyyy-MM-dd hh:mm:ss.S}] LIKE '123456ABCDEFG'
2012-05-11 12:19:40: (DEBUG) We are about to run the following query to extract the items for Event: cellID:
SELECT navid_table.timestamp  AS 'traceID', '0' AS 'orderAttribute' FROM navid_table   WHERE (navid_table.timestamp  IN (2000-01-05 01:14:59.0))
2012-05-11 12:19:40: (ERROR) There is an error in the query (turn on 'debug' mode to see it) to fetch the information for Event: cellID
2012-05-11 12:19:40: (ERROR) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '01:14:59.0))' at line 1
2012-05-11 12:19:40: (WARNING) Cancelling execution! Just after running the query.
2012-05-11 12:19:40: (NOTICE) Execution safely terminated

JBuijs

Hi Navid,

the WHERE clause in your event query is not correct. It should work if you remove this.
Please check the SQL manual for how to select rows of a certain date and time.

navid

Joos
I found the correct format , I have put it for others who might find the same problem:
timestamp 'yyyy-MM-dd HHMMSS.uuuuuu'
Thanks