Date difference in MYSQL with access query

BTW this is MYSQL database of ManageEngine servicedeskplus.

Your date is stored as Unix Time stamp...you just need to convert it to "human readable format" and perform the diff operation
For example :
value : 1514754001000 is actually
select convert_tz(from_unixtime(1514754001000 ), 'UTC', 'MST') as 'local time'; -->

select convert_tz(from_unixtime(1514754001 ), 'UTC', 'MST') as 'local time'; 

Select allOpen in new window

2017-12-31T14:00:01Z (the last 3 000 should be truncated as they are milliseconds)
Take a look if this what you need...i am pretty sure that i have a VBA function to also handle milliseconds)

Thanks John for your response.
Will the below VBA help?
http://www.vbforums.com/showthread.php?513727-RESOLVED-Convert-Unix-Time-to-Date

I think is somewhat the code i had...if you have massive records to filter out...better to handle them on mysql as your milliseconds are defaulted to zero...it would be much faster...otherwise the above code should work just fine.

I am getting error at line below

Thank you very much for your help.
Please correct me

Oh, you can't do like that. The constants' declarations must be at the top of the module, like:

Option Compare Database
Option Explicit

    ' Unix Time.
    Public Const UtOffset               As Long = -25569
    
    Public Const HoursPerDay            As Long = 24
    Public Const MinutesPerHour         As Long = 60
    Public Const SecondsPerMinute       As Long = 60
    Public Const SecondsPerHour         As Long = MinutesPerHour * SecondsPerMinute
    Public Const SecondsPerDay          As Long = HoursPerDay * SecondsPerHour

<snip>

'   Functions to follow here

Select allOpen in new window

Thanks Gustav
Now I am getting this error

-->Perhaps you have Null values - empty to- or from-dates? If so, try:
There is no null values.

This is what I get when I click debug

Yes, that value is way too large.
Did you divide by 1000?
What does UnixDate show if you press the + sign?

-->Did you divide by 1000?
I tried but no luck.

below is the screenshot of unixdate

Well, it was the values to see.

However, you don't divide by 1000 as I showed. As long as you don't, you will get that overflow error.
Correct that:

Days = DateDiff("d", DateUnix(Val([FROMDATE]) / 1000), DateUnix(Val([TODATE]) / 1000))

Select allOpen in new window

-->Days = DateDiff("d", DateUnix(Val([FROMDATE]) / 1000), DateUnix(Val([TODATE]) / 1000))
Where I will use this

I ended up using this. Below is for 90 days before TODATE. All these copied from above.
SELECT maintenancecontract.CONTRACTNAME, maintenancecontract.TODATE, Round(Date()-DateUnix(Val([TODATE])/1000)+1,0)*-1 AS Days, contract_fields.UDF_CHAR1, contract_fields.UDF_CHAR4
FROM maintenancecontract INNER JOIN contract_fields ON maintenancecontract.CONTRACTID = contract_fields.CONTRACTID
WHERE (((Round(Date()-DateUnix(Val([TODATE])/1000)+1,0)*-1)=0));

When I add your above snippet I am not getting the result as expected.
I have one more issue
When I added one more table I am not getting any value
I am getting blank

SELECT maintenancecontract.CONTRACTNAME, maintenancecontract.TODATE, DateDiff("d",Date(),DateUnix(Val([TODATE])/1000))-1 AS Days, contract_fields.UDF_CHAR1, contract_fields.UDF_CHAR4, DateDiff("d",Date(),DateUnix(Val([TODATE])/1000)) AS days2
FROM maintenancecontract INNER JOIN contract_fields ON maintenancecontract.CONTRACTID = contract_fields.CONTRACTID
WHERE (((DateDiff("d",Date(),DateUnix(Val([TODATE])/1000)))=0));

Select allOpen in new window

Probably something in the JOIN criteria is wrong...check the values of the fields you try to join......a common issue is with string values when declared as CHAR and they are padded with blank spaces.

When I add your above snippet I am not getting the result as expected.

That's because you modified it.

Apologize Gustav your comment is correct. I modified with your statement and added one more table and it worked.
Many thanks four your help.

SELECT maintenancecontract.CONTRACTNAME, maintenancecontract.TODATE, DateDiff("d",Date(),DateUnix(Val([TODATE])/1000))-1 AS difference, contract_fields.UDF_CHAR1, contract_fields.UDF_CHAR4, contractnotificationsettings.DAYS
FROM (maintenancecontract INNER JOIN contract_fields ON maintenancecontract.CONTRACTID = contract_fields.CONTRACTID) INNER JOIN contractnotificationsettings ON maintenancecontract.CONTRACTID = contractnotificationsettings.CONTRACTID;

Select allOpen in new window

Great! Thanks for the feedback.

Pleasure :))
Honestly I removed -1 as it is creating issue for calculation.
For example if an expiry is tomorrow it should show 1 as of now it shows 0 so I removed -1 in the query.