[RESOLVED] Epoch Time to VB6 Date

[Elroy]

Ok, I found this thread, but it's not exactly what I want.

For the uninitiated, Epoch time (aka, Unix time) is the number of seconds elapsed since #1/1/1070# GMT.

My Epoch time will be sitting in a Long.
I will need to appreciate local time, and I will need hours, minutes, and seconds in the VB6 Date.

It'd also be nice if the Long's sign-bit was ignored so this thing wouldn't quit working in about 10 years.

I'll be working on it, but if anyone has anything, that'd be nice.

p.s. I don't really need the vice-versa, at least not right now.

[Elroy]

Hmmm, well, it wasn't as bad as I thought. I don't have the timezones worked out, but here's what I've got:

Option Explicit
Private Declare Function GetMem4 Lib "msvbvm60" (ByRef Source As Any, ByRef Dest As Any) As Long ' Always ignore the returned value, it's useless.


Public Function vbDateFromEpoch(iEpoch As Long) As Date
    Dim c As Currency
    GetMem4 iEpoch, c
    ' 86400 is the number of seconds in a day.
    ' 25569 is the days between 1/1/1970 (Epoch origin) and 12/30/1899 (VB6 origin).
    vbDateFromEpoch = CDbl(c * 10000@) / 86400# + 25569#
End Function

Private Sub Form_Load()
    Debug.Print Format$(vbDateFromEpoch(1583020800), "mm/dd/yyyy hh:nn:ss")
End Sub

[Elroy]

Ok, I think I've got it.

Option Explicit
'
Private Type TIME_ZONE_INFORMATION
  Bias                  As Long     ' UTC = local_time + bias    OR    local_time = UTC - bias
  StandardName(63)      As Byte
  StandardDate(15)      As Byte
  StandardBias          As Long
  DaylightName(63)      As Byte
  DaylightDate(15)      As Byte
  DaylightBias          As Long
End Type
'
Private Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
Private Declare Function GetMem4 Lib "msvbvm60" (ByRef Source As Any, ByRef Dest As Any) As Long ' Always ignore the returned value, it's useless.

Public Function vbDateFromEpoch(iEpoch As Long) As Date
    Dim c As Currency
    Dim u As TIME_ZONE_INFORMATION
    GetMem4 iEpoch, c
    ' 86400 is the number of seconds in a day.
    ' 25569 is the days between 1/1/1970 (Epoch origin) and 12/30/1899 (VB6 origin).
    GetTimeZoneInformation u
    vbDateFromEpoch = CDbl(c * 10000@) / 86400# + 25569# - (CDbl(u.Bias) / 1440#)
End Function

Private Sub Form_Load()
    Debug.Print Format$(vbDateFromEpoch(-1583020800), "mm/dd/yyyy hh:nn:ss")
End Sub

I'll let some other eyes stare at it before I mark it as resolved.

EDIT1: Ok, I made a small units mistake. It's now corrected. I'll probably simplify the algebra just a bit, but I think that's it.
EDIT2: Actually, I think the algebra is fine.

EDIT3: Here, if you want a number to test, 1584574200, that's March 18, 2020 7:30PM Eastern time (6:30 Central, 5:30 Mountain, 4:30 Pacific).

[qvb6]

Your code in post #3 gives me the following output(I am in Eastern Time zone):

12/09/2055 01:28:16

And when I use the number you suggested in your edit, 1584574200, it's one hour off:

03/18/2020 18:30:00

[qvb6]

Here is the fix for the daylight saving issue(my changes are highlighted in bold):

Option Explicit
'
Private Type TIME_ZONE_INFORMATION
  Bias                  As Long     ' UTC = local_time + bias    OR    local_time = UTC - bias
  StandardName(63)      As Byte
  StandardDate(15)      As Byte
  StandardBias          As Long
  DaylightName(63)      As Byte
  DaylightDate(15)      As Byte
  DaylightBias          As Long
End Type
'
Private Const TIME_ZONE_ID_STANDARD As Long = 1
Private Const TIME_ZONE_ID_DAYLIGHT As Long = 2
Private Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
Private Declare Function GetMem4 Lib "msvbvm60" (ByRef Source As Any, ByRef Dest As Any) As Long ' Always ignore the returned value, it's useless.

Public Function vbDateFromEpoch(iEpoch As Long) As Date
    Dim c As Currency
    Dim u As TIME_ZONE_INFORMATION
    Dim Bias As Long
    Dim ret As Long
    
    GetMem4 iEpoch, c
    ' 86400 is the number of seconds in a day.
    ' 25569 is the days between 1/1/1970 (Epoch origin) and 12/30/1899 (VB6 origin).
    ret = GetTimeZoneInformation(u)
    If ret = TIME_ZONE_ID_DAYLIGHT Then
        Bias = u.Bias + u.DaylightBias
    Else
        Bias = u.Bias
    End If
    vbDateFromEpoch = CDbl(c * 10000@) / 86400# + 25569# - (CDbl(Bias) / 1440#)
End Function

Private Sub Form_Load()
    Debug.Print Format$(vbDateFromEpoch(1584574200), "mm/dd/yyyy hh:nn:ss")
End Sub

Output:

03/18/2020 19:30:00

[dz32]

I have been using the following for calculating PE compile timestamps. It has always matched all the other tools I have used.also matches this online tools output:
https://www.epochconverter.com/batch#results

'output:  1584574200   Mar 18 2020 23:30:00         1584574200 

Private Sub Form_Load()
    Dim s As String
    s = timeStampToDate(1584574200)
    Debug.Print 1584574200, s, DatetoTimeStamp(CDate(s))
End Sub

Property Get timeStampToDate(timestamp As Long) As String

    On Error Resume Next
    Dim base As Date
    Dim compiled As Date
    
    base = DateSerial(1970, 1, 1)
    compiled = DateAdd("s", timestamp, base)
    timeStampToDate = Format(compiled, "mmm d yyyy h:nn:ss")

End Property

Function DatetoTimeStamp(d As Date) As Long

    On Error Resume Next
    Dim base As Date
 
    base = DateSerial(1970, 1, 1)
    DatetoTimeStamp = DateDiff("s", base, d)

End Function

[qvb6]

I have been using the following for calculating PE compile timestamps. It has always matched all the other tools I have used.also matches this online tools output:
https://www.epochconverter.com/batch#results

Because your code takes a Long, it will stop working on Jan 19, 2038 when the number of seconds exceeds 2G limit(2^31-1=2147483647), but it's easy to modify to accept a Double or a Variant with Decimal sub type. Also, DateAdd/DateDiff never takes into account daylight saving, but you are dealing with UTC, which is fine.

[Elroy]

@qvb6: Thanks, good catch on the daylight savings.

Regarding the 12/09/2055 01:28:16 returned for -1583020800, I had just stuck that number in there to see if negatives returned somewhat reasonable value. A better test would be to get the return for &h7FFFFFFF and compare it to the return of &h80000000, and then make sure that exactly one second ticked off ... and it does.

[dz32]

cool thanks i was not aware of the limitation. I will add a note in my source