CODEWITHSUNDEEP
ms-accessdatetimevba
user3101112
user3101112

Reputation: 41

Convert Ticks to Datetime value in MS Access

I have been handed a large number of tables with large integer values for the date and time.

Example: 1382575236393 represents Dec 2, 2013 10:30 PM

My understanding is that this is a tick (nanosecond). I have been utterly unable to figure out or locate a formula or function that will convert that tick to a date/time value for using in Microsoft Access 2010.

Anyone tackled this before? I was thinking there'd be a VBA function lurking around somewhere.

Thanks

Upvotes: 1

Views: 3148

Answers (2)

Dr DG
Dr DG

Reputation: 1

Even easier:

Public Function TicksToDate(dblMilliseconds As Double) As Date
    TicksToDate = DateAdd("s", dblMilliseconds / 1000, #1/1/1970#)
End Function

Upvotes: 0

Dai
Dai

Reputation: 155558

VBA for the most-part remains frozen in time since the mid-1990s, back when the VB ecosystem was worlds apart from everything else. Thesedays it's a lot more cohesive (e.g. the .NET framework).

Anyway, there is no standard definition of a "tick" - on Unix/POSIX a timestamp is the number of seconds since 1970-01-01 00:00 UTC, in .NET DateTime.Tick is one hundred nanoseconds, but Stopwatch.ElapsedTicks is an arbitrary unit, so it's a bit complicated in-general.

I wasn't sure what scale to use, so I did some division and found it's actually the number of milliseconds since the UNIX epoch:

Nanoseconds:     1382575236393 / 86400000000000 = 0.01 of a day
100-Nanoseconds: 1382575236393 /   864000000000 = 1.6 days
Microseconds:    1382575236393 /    86400000000 = 16 days
Milliseconds:    1382575236393 /       86400000 = 160002 days (43 years)
Seconds:         1382575236393 /          86400 = 16002028 days (43,841 years!)

43 years ago today, co-incidentally, is 1970, so now we know the scale. I'm going to wager since this is obviously based on the 1970-01-01 00:00 epoch it's also going to be in UTC. VBA does not include any built-in functionality for handling timezones (stupid, I know) so I think it's best we assume this is a UTC date value, so you'll need to do your own timezone conversion in your application where appropriate.

You'll need to use 64-bit integers to do this properly, but there's a problem: only VBA in 64-bit versions of Office 2010 or later include the LongLong type, if you're using 32-bit Office or running Office 2007 or earlier then things get a lot harder. For now, we'll assume you are running 64-bit Office 2010 or 2013.

Anyway, the solution (with LongLong) is to convert the milisecond timestamp into a UNIX timestamp, then let VBA add the seconds to the epoch. Note: never add seconds to a year by yourself, because you'll forget things like leap-seconds, leap-years, and other tidbits.

Public Function MilisecondUnixTimestampToVBDate(milisecondUnixTimestamp As LongLong) As Date
    Dim unixTimestamp As Long
    unixTimestamp = milisecondUnixTimestamp / 1000

    Dim dt As Date
    dt = DateAdd("s", unixTimestamp , #1/1/1970#)
    MilisecondUnixTimestampToVBDate = dt

End Function

Upvotes: 1

Related Questions