We help IT Professionals succeed at work.

Date difference in MYSQL with access query

Medium Priority
117 Views
Last Modified: 2019-06-05
I have a MYSQL database with access forms frontend. i.e. linked tables in access.
Now I would like to get difference of two dates.
Difference between a date from the database and today. Here is the date format.
 Capture5.JPGBTW this database already running with another frontend as well. I want to access the database and trigger date difference as per our convenience.
This is MYSQL database of ManageEngine servicedeskplus.
Comment
Watch Question

MASEE Solution Guide - Technical Dept Head
CERTIFIED EXPERT
Most Valuable Expert 2017

Author

Commented:
BTW this is MYSQL database of ManageEngine servicedeskplus.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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'; 

Open 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)
MASEE Solution Guide - Technical Dept Head
CERTIFIED EXPERT
Most Valuable Expert 2017

Author

Commented:
Thanks John for your response.
Will the below VBA help?
http://www.vbforums.com/showthread.php?513727-RESOLVED-Convert-Unix-Time-to-Date
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
The set of functions below will do the conversion. Then, as your values are in milliseconds and look like text, the expression will be:

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

Open in new window


    ' 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


' Returns the date of a specified Unix Time with a resolution of 1 ms.
' UnixDate can be any value that will return a valid VBA Date value.
'
' Minimum value:  -59011459200
'   ->  100-01-01 00:00:00.000
' Maximum value:  253402300799.999
'   -> 9999-12-31 23:59:59.999
'
' 2016-02-08. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateUnix( _
    ByVal UnixDate As Variant) _
    As Date
    
    Dim Timespan    As Variant
    Dim ResultDate  As Date
    
    Timespan = (CDec(UnixDate) / SecondsPerDay) - CDec(UtOffset)
    ResultDate = DateFromTimespan(Timespan)
    
    DateUnix = ResultDate
    
End Function


' Converts a timespan value to a date value.
' Useful only for result date values prior to 1899-12-30 as
' these have a negative numeric value.
'
'   2015-12-15. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateFromTimespan( _
    ByVal Value As Date) _
    As Date
  
    ConvTimespanToDate Value
  
    DateFromTimespan = Value
  
End Function


' Converts a linear timespan value by reference to a date value.
' Example:
'
'   Date     Time  Timespan      Date
'   19000101 0000  2             2
'
'   18991231 1800  1,75          1,75
'   18991231 1200  1,5           1,5
'   18991231 0600  1,25          1,25
'   18991231 0000  1             1
'
'   18991230 1800  0,75          0,75
'   18991230 1200  0,5           0,5
'   18991230 0600  0,25          0,25
'   18991230 0000  0             0
'
'   18991229 1800 -0,25         -1,75
'   18991229 1200 -0,5          -1,5
'   18991229 0600 -0,75         -1,25
'   18991229 0000 -1            -1
'
'   18991228 1800 -1,25         -2,75
'   18991228 1200 -1,5          -2,5
'   18991228 0600 -1,75         -2,25
'   18991228 0000 -2            -2
'
'   2015-12-15. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub ConvTimespanToDate( _
    ByRef Value As Date)
   
    Dim DatePart    As Double
    Dim TimePart    As Double
  
    If Value < 0 Then
        ' Get date (integer) part of TimeSpan shifted one day
        ' if a time part is present as Int() rounds down.
        DatePart = Int(CDbl(Value))
        ' Retrieve and reverse time (decimal) part.
        TimePart = DatePart - Value
        ' Assemble the date and time parts to return a date value.
        Value = CDate(DatePart + TimePart)
    Else
        ' Positive timespan values are identical to date values by design.
    End If
  
End Sub

Open in new window

MASEE Solution Guide - Technical Dept Head
CERTIFIED EXPERT
Most Valuable Expert 2017

Author

Commented:
I am getting error at line below
Capture2.JPGCapture1.JPG
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Then you miss the constants listed top.

Also, at the top of the module, have this line (always):

Option Explicit

Open in new window

MASEE Solution Guide - Technical Dept Head
CERTIFIED EXPERT
Most Valuable Expert 2017

Author

Commented:
Thank you very much for your help.
Please correct me
Capture3.JPG
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Open in new window

MASEE Solution Guide - Technical Dept Head
CERTIFIED EXPERT
Most Valuable Expert 2017

Author

Commented:
Thanks Gustav
Now I am getting this error
Capture6.JPG
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Press Debug and study the offending line and the values.
Menu: View, Locals Window.

Perhaps you have Null values - empty to- or from-dates? If so, try:

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

Open in new window

MASEE Solution Guide - Technical Dept Head
CERTIFIED EXPERT
Most Valuable Expert 2017

Author

Commented:
-->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
Capture7.JPG
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes, that value is way too large.
Did you divide by 1000?
What does UnixDate show if you press the + sign?
MASEE Solution Guide - Technical Dept Head
CERTIFIED EXPERT
Most Valuable Expert 2017

Author

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

below is the screenshot of unixdate
Capture8.JPG
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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))

Open in new window

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Probably you should implement value rounding...just round it to 2 or 3 digits and you should be good to go.
MASEE Solution Guide - Technical Dept Head
CERTIFIED EXPERT
Most Valuable Expert 2017

Author

Commented:
-->Days = DateDiff("d", DateUnix(Val([FROMDATE]) / 1000), DateUnix(Val([TODATE]) / 1000))
Where I will use this
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
That could be in a function of a form.
In a query, the expression could be in the designer:

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

Open in new window

In SQL, it would be:

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

Open in new window

MASEE Solution Guide - Technical Dept Head
CERTIFIED EXPERT
Most Valuable Expert 2017

Author

Commented:
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));
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
OK, great. But in some way you are reinventing DateDiff, as you could do:

DateDiff("d", Date(), DateUnix(Val([TODATE])/1000)) - 1 AS Days

and:

WHERE DateDiff("d", Date(), DateUnix(Val([TODATE])/1000)) = 1

Open in new window

MASEE Solution Guide - Technical Dept Head
CERTIFIED EXPERT
Most Valuable Expert 2017

Author

Commented:
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
Capture4.JPG
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));

Open in new window

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

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

That's because you modified it.
MASEE Solution Guide - Technical Dept Head
CERTIFIED EXPERT
Most Valuable Expert 2017

Author

Commented:
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;

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Great! Thanks for the feedback.
MASEE Solution Guide - Technical Dept Head
CERTIFIED EXPERT
Most Valuable Expert 2017

Author

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.