Unix DateTime Stamp in ACCESS

[dschomburg]

I'm trying to take a file containing info from a unix box. Each record has a START and STOP field. An example is; A_START=" 969429786 " and A_STOP=" 969512403 ". I'm told this is the number of seconds since 01/01/1970. and that the above example would be around the 3rd of October. Does anyone know how to convert this number into a Date/Time format Access can use? I need both date and time from each number.
Any help would help a heap! [sig][/sig]

 

[Robertd]

Hi

add this to your global module

Public Function CalcUnixTime(theUnixTime As Variant)

Dim myDays As Long
Dim myUnixTime As Long
Dim myTimeRem As Long
Dim myTemp As Long
Dim myAccessHMS As Double

If Not IsNull(theUnixTime) Then
' calculate number of days from unix value
If theUnixTime > 0 Then
If myDays >= 86400 Then
myDays = theUnixTime / 86400
myTimeRem = theUnixTime - myDays * 86400
Else
myDays = 0
myTimeRem = theUnixTime
End If
' calculate whats left
If myTimeRem > 0 Then
' convert remainder to Access/IBM PC Format
myAccessHMS = myTimeRem / 86400
Else
myAccessHMS = 0
End If
' add to UNIX birth date and return
CalcUnixTime = #1/1/70# + myDays + myAccessHMS
End If
Else
' do something if UNIX date was null
' uncomment the return type desired
' return a null
CalcUnixTime = Null
' return systems date/time
'CalcUnixTime = Now
End If

End Function


in the query you use to add your data,
make your source field something like
SomeName: CalcUnixTime([sourceField])
append
DestinationField

based on 86400 sec (24*60*60) = 1 day
and PC/Access uses 1/68400 as the decimal part of a day
ie hrs/mins/secs

I haven't done much testing so you might like to do so
969429786 = 20-Sep-00 6:03:06 AM
and
969512403 = 21-Sep-00 5:00:03 AM
let us know,..

the limit (if it works!) is 19-Jan-2038 3:14:07 AM
long 2147483647 i don't think it will bother me ;-)
[sig]<p>Robert Dwyer<br><a href=mailto:rdwyer@orion-online.com.au>rdwyer@orion-online.com.au</a><br>[/sig]

 

[Robertd]

Sory

you might want to change the second and third if statement to

from
If theUnixTime > 0 Then
If myDays >= 86400 Then

To
If theUnixTime >= 0 Then ' include 1-1-70
If theUnixTime >= 86400 Then 'check theUnixTime

opps,...
[sig]<p>Robert Dwyer<br><a href=mailto:rdwyer@orion-online.com.au>rdwyer@orion-online.com.au</a><br>[/sig]

 

[dschomburg]

Good Gravy! I'll give this a whirl.
I found a calculation at a different site.
x= whatever the unix time is
((x+((365*70+19)*86400))/86400)-0.41667

This seems to work in the expression builder. But I don't know how accuarate the time is. The Date is fine.

I will try your function and compare the two.

Thanks alot.

Dave
[sig][/sig]