Convert Unix Time Stamp to Windows date

[warren50]

How can i convert a unix time stamp to an Access date?

 

[Karl E. Peterson]

How can i convert a unix time stamp to an Access date?

Kind of a reach, asking a quesion like that here, huh? I mean, presuming a bunch of
Windows users know what format a "unix time stamp" may be.

Personally, that's a piece of the puzzle that I'm missing. I do know that some
systems mark time as the number of seconds since 1/1/1970 0:00:00. If that's the
case with your system, a function like this will do nicely:

Public Function NetTimeToVbTime(NetDate As Long) As Double
Const BaseDate# = 25569 'DateSerial(1970, 1, 1)
Const SecsPerDay# = 86400
NetTimeToVbTime = BaseDate + (CDbl(NetDate) / SecsPerDay)
End Function

If that's not the case, can you describe your timestamp scheme?

 

[warren50]

Karl,

Thanks for a great answer to my question. I just realized there is an Access
Database duscussion group that I didn't see earlier and I probabaly should
have posted there with a UNIX time stamp definition. You got it right on the
head, seconds since 1/1/1970 GMT. assume 86,400 seconds in a day.

Can I use this function in Access? I think I can create a user defined
function in my database and then use the function in my query to convert the
data field's UNIX times.

I'm not sure what "Const BaseDate# = 25569" refers to though. 1/1/1970 would
be "0" as a UNIX time. What is "25569"? Stay with me here I understand most
programming concepts from C++ but its been a while.

 

[Karl E. Peterson]

Hi Warren --

Thanks for a great answer to my question. I just realized there is an
Access Database duscussion group that I didn't see earlier and I
probabaly should have posted there with a UNIX time stamp definition.

Nah, VBA is fine for the question, probably even better than some random Access
group. The only problem was, you left folks guessing what format the timestamp might
be needing to be converted from.

You got it right on the head, seconds since 1/1/1970 GMT. assume
86,400 seconds in a day.

Can I use this function in Access?
Yep.

I think I can create a user defined
function in my database and then use the function in my query to
convert the data field's UNIX times.

There ya go.

I'm not sure what "Const BaseDate# = 25569" refers to though.
1/1/1970 would be "0" as a UNIX time. What is "25569"? Stay with me
here I understand most programming concepts from C++ but its been a
while.

VB uses a different zero time; 25569 days before that used by Unix, to be exact. I
never remember it, myself, so I drop to the Immediate window, and do this:

?format(0, "long date"), format(0, "long time")
Saturday, December 30, 1899 12:00:00 AM

So, this function calculates the number of (fractional) days since 1/1/1970, then
adds to that number the number of days between 12/30/1899 and 1/1/1970 to arrive at
the correct day/time. You might actually want to change the return type to Date,
though, for use in Access. Maybe more like this:

Public Function NetTimeToVbTime(ByVal NetDate As Long) As Date
Const BaseDate# = 25569 'DateSerial(1970, 1, 1)
Const SecsPerDay# = 86400
NetTimeToVbTime = BaseDate + (CDbl(NetDate) / SecsPerDay)
End Function

More background: Internally, Date variables are stored as Double values. The number
of days since 12/30/1899 is stored in the whole part, and the fraction of a day
(time) is stored in the fractional part.

Deep background: I pulled that routine from one that reads user information
(http://vb.mvps.org/samples/NetUser). Just goes to show you NT's roots in VMS that
they both store dates, like last logon/logoff dates and account expirations, with the
same technique, huh? <g>

Hope this helps!

Later... Karl

 

[warren50]

i got an answer to my question from The Access database discussion group and
it is the simplest solution to the problem. Thank you all for your solutions,
I'm sure they all would have worked or got me on the right path to the
solution, but John Spencer (MVP) gave me this one:

DateAdd("s",UnixDate,#1/1/1970#)

Whic uses a built in function and gives me the quick fix I needed.

Thanks All!

 

[Karl E. Peterson]

DateAdd("s",UnixDate,#1/1/1970#)

Whic uses a built in function and gives me the quick fix I needed.

Ah, but wouldn't you rather understand what was happening in there? <g>