The solution in MySQL is similar to that of DB2 based solution. Create a function in MySQL database and call that function in your SQL. But here is how to do it.
- Bring up MySQL Administrator client and connect to the database in question.
- Goto Catalogs in your left pane and select the appropriate database schema.
- Goto Stored Procedure tab and click the "Create Stored Proc".
- In the name field, give a Name for the function (e.g. TDW_TO_NORMAL_TS) and click "Create FUNCTION" button.
- MySQL will create a skeleton function like below.
CREATE FUNCTION `ncpolldata`.`TDW_TO_NORMAL_TS` () RETURNS INT
BEGIN
END - Replace the "CREATE FUNCTION" to look like below.
CREATE FUNCTION `ncpolldata`.`TDW_TO_NORMAL_TS` (tdw_time bigint) RETURNS DATETIME DETERMINISTIC - Between the "BEGIN" and "END" blocks, paste the following code.
BEGIN
Declare normal_time datetime;
Declare tdw_trunc bigint;
Set tdw_trunc = substr(tdw_time,2,12);
Set normal_time = DATE_FORMAT(tdw_trunc, '%y%m%d%H%i%s');
return(normal_time);
END - That's it. Click on the "Execute SQL" button to save the newly created function.
- Call the function in your SQL Statements like below.
SELECT TDW_TO_NORMAL_TS(poll_time) from KNP_POLL_DATA_COLLECTION LIMIT 100
