Converting 10 digit date from MySQL

[Scott Miller via AccessMonster.com]

1140373503 is an example of the date field I Imported from a MySQL database
into an Access 2003 table. How can I update this to dd-mmm-yy medium date?

 

[Ricky Hicks [MVP]]

Hmmmm ...

This appears to be a Unix (Epoch) time value ...

The conversion of .. 1140373503 to a date/time value woould be ... 2/19/2006
12:25:03 PM (central time US).

Here is a user defined function to convert the value:

' ************************ Start Code ************************
' Convert Unix Epoch time (Time in seconds since Jan 1, 1970)
'
' UTS_Offset is the hours offset from GMT where you are locate
' Eastern Time (US) = -5
' Central Time (US) = -6
' Mountain Time (US) = -7
' Pacific Time (US) = -8
'
Public Function fConvertEpoch(varEpochVal As Variant, UTC_OffSet As Integer)
As Variant
Dim tmpDate As Date
Dim StartDaylight As Date
Dim EndDaylight As Date

If IsNull(varEpochVal) Then Exit Function

tmpDate = DateAdd("s", varEpochVal, #1/1/1970#)
tmpDate = DateAdd("h", UTC_OffSet, tmpDate)

' Get the last day of March by subtracting one day from 4/1
StartDaylight = DateAdd("d", -1, DateSerial(Year(tmpDate), 4, 1))

' Now skip to the next Sunday
StartDaylight = DateAdd("d", 5 - WeekDay(StartDaylight), StartDaylight)
StartDaylight = DateAdd("h", 2, StartDaylight)
EndDaylight = DateSerial(Year(tmpDate), 11, 1)

' Back up to the previous Sunday
EndDaylight = DateAdd("d", -5 + WeekDay(EndDaylight), EndDaylight)
EndDaylight = DateAdd("h", 1, EndDaylight)

If (tmpDate >= StartDaylight And tmpDate < EndDaylight) Then
tmpDate = DateAdd("h", 1, tmpDate)
End If

fConvertEpoch = tmpDate

End Function
' ************************ End Code ************************

Here is an example of the usage:

=fConvertEpoch(1140373503,-6)

The above is using Central Time (US) ...

R. Hicks

 

[John Nurick]

Neat function, Ricky! It would be even better if it got the daylight
saving information from the workstation instead of hard-coding it. That
way it would work world-wide, not just in most of North America (see
e.g. http://webexhibits.org/daylightsaving/g.html).