Compare SQL Server datetime to MySQL unixtime

I have 2 tables, one in a SQL Server database with a datetime column and one in a MySQL database with a date in unixtime column.

I need to pull records from the MySQL table where the unix time is greater than the latest date in my sql server table.

so pseudo code would be:
"select * from MySQL Table where datecol > select max(datecol) from SQLServer table"

The SQL below is what I'm using but it returns all the records from the MySQL database, and not just the ones I want:

declare @lastDate datetime
set @lastDate = (select max(datediff(second, {d '1970-01-01'}, createdOn)) from sqlServerTable)

SELECT cast(createdate as varchar(50))
	FROM OPENQUERY 
		(MYLINKEDSERVER,'SELECT createdate, from_unixtime(createdate) as createdOn 
						FROM MySQLSERVERTable 
						WHERE createdate > '' + @lastDate + ''')

Open in new window


The above SQL returns all the rows but I also get the error: "Arithmetic overflow error converting expression to data type datetime."

Any ideas?
BrianFordAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrianFordAuthor Commented:
I figured out the last error, simply changed the data type of @lastDate from datetime to varchar

but still getting all rows returned when I shouldn't
0
David Johnson, CD, MVPOwnerCommented:
1 January 1970 Unix Epoch
1 January 1601 Windows Epoch

What you have to do is convert your Windows Time to Unix Time
subtract 11,644,473,600 seconds from your windows time
PS D:\Documents\WindowsPowerShell\Scripts> $date1 = [datetime]"01/01/1970 00:00"
PS D:\Documents\WindowsPowerShell\Scripts> $date2 = [datetime]"01/01/1601 00:00"
PS D:\Documents\WindowsPowerShell\Scripts> New-TimeSpan -end $date1 -start $date2


Days              : 134774
Hours             : 0
Minutes           : 0
Seconds           : 0
Milliseconds      : 0
Ticks             : 116444736000000000
TotalDays         : 134774
TotalHours        : 3234576
TotalMinutes      : 194074560
TotalSeconds      : 11644473600
TotalMilliseconds : 11644473600000

Open in new window

0
BrianFordAuthor Commented:
the windows time is being converted in the dateadd function and does result in a correct unixtime, but when using that in the WHERE clause, the result set is not correct
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Vitor MontalvãoMSSQL Senior EngineerCommented:
Is the table from MySQL big? If not you can create a SQL view to return all the data from MySQL table and then query the created view with the date filter (should work as MSSQL date type now).
0
BrianFordAuthor Commented:
the table is not big yet, but it will get big over time so not sure this metod will perform very well
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I suggest you to give a try. You wouldn't lose nothing and will have an alternative to compare with.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrianFordAuthor Commented:
I will give this a try
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.