SQL Server – Handling Unixtime Issues When Migrating from MySQL

datetimeMySQLsql server

I'm trying to convert this query to SQL Server.

It already has a lot of updates. Still, I don't know what to do from here ( i'm not the best when it comes to programming in MySQL )

 SELECT wo.WORKORDERID,
dpt.DEPTNAME "Department",
aau.USER_ID,
aau.FIRST_NAME "Requester", 
sadt.RATING "Rating Value", 
spre.COMMENTS "Comments",
    FROM_UNIXTIME((spre.CREATEDTIME/1000)) "criacao da pesquisa",
    #FROM_UNIXTIME((wo.RESPONDEDTIME/1000)) "Resposta da pesquisa",
            MONTH(FROM_UNIXTIME((spre.CREATEDTIME)/1000)) "Mes Pesquisa",
            YEAR(FROM_UNIXTIME((spre.CREATEDTIME)/1000)) "Ano Pesquisa",
#wo.RESPONDEDTIME "RespondedTime", 
aau1.FIRST_NAME "Technician"
    FROM SurveyPerRequest spr 
INNER JOIN SurveyPerRequestExt spre ON spr.SURVEYID=spre.SURVEYID 
LEFT JOIN SurveyDetails sdt ON spre.SURVEYID=sdt.SURVEYID 
LEFT JOIN SurveyAnswers sadt ON sdt.SURANSID=sadt.SURANSID 
LEFT JOIN WorkOrder wo ON spr.WORKORDERID=wo.WORKORDERID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser sdu1 ON wos.OWNERID=sdu1.USERID 
LEFT JOIN AaaUser aau1 ON sdu1.USERID=aau1.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
    WHERE (spre.CREATEDTIME >= UNIX_TIMESTAMP('2014-04-01 00:00:00')*1000) AND (spre.CREATEDTIME <= UNIX_TIMESTAMP('2014-08-31 23:59:59')*1000)
AND aau.FIRST_NAME LIKE '%Pumputis%'
        ORDER BY spre.CREATEDTIME

Thanks a lot for the help

Best Answer

You mentioned Unixtime being major problem

Please note how FROM_UNIXTIME() is being used

FROM_UNIXTIME((spre.CREATEDTIME/1000)) "criacao da pesquisa",

From this line, here is what I can tell:

spre.CREATEDTIME is number of milliseconds since 1970-01-01 00:00:00 UTC
FROM_UNIXTIME() requires the number of seconds, not milliseconds
spre.CREATEDTIME has to be divided by 1000 before calling FROM_UNIXTIME()

Based on someone else's post in StackOverflow (Truncate Datetime to Second (Remove Milliseconds) in T-SQL), you will have to change it to something like:

CONVERT(datetime,char(19),spre.CREATEDTIME/1000,126) "criacao da pesquisa",

Please read T-SQL Documentation on CONVERT

what is this # in front of some sentences??

The # character as the first character makes that whole line a comment

When you execute this script, only the first SELECT is executed

The other SELECT queries and the one UPDATE query are not executed

The first query has comments as well. If you ignore those two lines, this is what really runs:

 SELECT wo.WORKORDERID,
dpt.DEPTNAME "Department",
aau.USER_ID,
aau.FIRST_NAME "Requester", 
sadt.RATING "Rating Value", 
spre.COMMENTS "Comments",
    FROM_UNIXTIME((spre.CREATEDTIME/1000)) "criacao da pesquisa",
            MONTH(FROM_UNIXTIME((spre.CREATEDTIME)/1000)) "Mes Pesquisa",
            YEAR(FROM_UNIXTIME((spre.CREATEDTIME)/1000)) "Ano Pesquisa",
aau1.FIRST_NAME "Technician"
    FROM SurveyPerRequest spr 
INNER JOIN SurveyPerRequestExt spre ON spr.SURVEYID=spre.SURVEYID 
LEFT JOIN SurveyDetails sdt ON spre.SURVEYID=sdt.SURVEYID 
LEFT JOIN SurveyAnswers sadt ON sdt.SURANSID=sadt.SURANSID 
LEFT JOIN WorkOrder wo ON spr.WORKORDERID=wo.WORKORDERID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser sdu1 ON wos.OWNERID=sdu1.USERID 
LEFT JOIN AaaUser aau1 ON sdu1.USERID=aau1.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
    WHERE (spre.CREATEDTIME >= UNIX_TIMESTAMP('2014-04-01 00:00:00')*1000) AND (spre.CREATEDTIME <= UNIX_TIMESTAMP('2014-08-31 23:59:59')*1000)
AND aau.FIRST_NAME LIKE '%Pumputis%'
        ORDER BY spre.CREATEDTIME

SUGGESTION #1 : Rewrite the query

You should rewrite the query as follows

SELECT http,
COUNT( http )  AS count 
FROM reqs
WHERE date >= ( DATE(NOW() - INTERVAL 1 DAY) + INTERVAL 0 SECOND )
GROUP BY http
ORDER BY count;

SELECT * FROM
(
    SELECT http,
    COUNT( http )  AS count 
    FROM reqs
    WHERE date >= ( DATE(NOW() - INTERVAL 1 DAY) + INTERVAL 0 SECOND )
    GROUP BY http
) A ORDER BY count;

The WHERE should not have a function on both sides of the equal sign. Having date on the left side of the equals sign makes it easier for the Query Optimizer to use an index against it.

SUGGESTION #2 : Supporting Index

I would also suggest a different index

ALTER TABLE reqs ADD INDEX date_http_ndx (date,http); -- not (http,date)

I suggest this order of columns because the date entries would all be contiguous in the index. Then, the query simply collects http values without skipping gaps in http.

SUGGESTION #3 : Bigger Key Buffer (Optional)

MyISAM only uses index caching. Since the query should not touch the .MYD file, you should use a slightly bigger MyISAM Key Buffer.

To set it to 256M

SET @newsize = 1024 * 1024 * 256;
SET GLOBAL key_buffer_size = @newsize;

Then, set it in my.cnf

[mysqld]
key_buffer_size = 256M

Restart of MySQL not required

Best Answer

Related Solutions

Sql-server – SQL Server and SharePoint time problem

MySQL Performance Issue with Indexed Datetime Column

SUGGESTION #1 : Rewrite the query

SUGGESTION #2 : Supporting Index

SUGGESTION #3 : Bigger Key Buffer (Optional)

Give it a Try !!!

Related Question