[AccessD] UNIX time. Was: Crack Code

Hi Mark and Matty

Nice exploration! 
Incredible what some did those days to save a few bytes to cut the diskspace and cost.

I found a couple of functions for converting to and from UNIX time:

Public Function DateFromUnix( _
  ByVal dblSeconds As Double, _
  Optional lngLocalTimeBias As Long) _
  As Date
  
' Converts UNIX time value to UTC date value.
' Optionally, a local time bias can be specified;
' this must be in minutes with a resolution of 15 minutes.
'
' Examples:
'   UTC. dblSeconds: 1000000000, lngLocalTimeBias: 0
'     2001-09-09 01:46:40
'   CET. dblSeconds: 1000000000, lngLocalTimeBias: -60
'     2001-09-09 02:46:40
'
' 2004-03-23. Cactus Data ApS. CPH.
  
  ' UNIX UTC start time.
  Const cdatUnixTimeZero    As Date = #1/1/1970#
  ' Maximum time bias, 12 hours.
  Const clngTimeBiasMax     As Long = 12& * 60&
  ' Count of seconds of one day.
  Const clngSecondsPerDay   As Long = 24& * 60& * 60&
  
  Dim dblDays               As Double
  Dim dblDaysSeconds        As Double
  Dim lngSeconds            As Long
  Dim datTime               As Date
  
  ' Limit intervals for DateAdd to Long to be acceptable.
  dblDays = Int(dblSeconds / clngSecondsPerDay)
  dblDaysSeconds = dblDays * clngSecondsPerDay
  lngSeconds = dblSeconds - dblDaysSeconds
  
  datTime = DateAdd("d", dblDays, cdatUnixTimeZero)
  datTime = DateAdd("s", lngSeconds, datTime)
  If lngLocalTimeBias <> 0 Then
    If Abs(lngLocalTimeBias) < clngTimeBiasMax Then
      datTime = DateAdd("n", -lngLocalTimeBias, datTime)
    End If
  End If
  
  DateFromUnix = datTime

End Function

Public Function UnixTimeValue( _
  ByVal datTime As Date, _
  Optional ByVal lngLocalTimeBias As Long) _
  As Double
  
' Converts UTC date value to UNIX time value.
' Optionally, a local time bias can be specified;
' this must be in minutes with a resolution of 15 minutes.
'
' Examples:
'   UTC. datTime: #09/09/2001 01:46:40#, lngLocalTimeBias: 0
'     1000000000
'   CET. datTime: #09/09/2001 02:46:40#, lngLocalTimeBias: -60
'     1000000000
'
' 2004-03-23. Cactus Data ApS. CPH.
  
  ' UNIX UTC start time.
  Const cdatUnixTimeZero    As Date = #1/1/1970#
  ' Maximum time bias, 12 hours.
  Const clngTimeBiasMax     As Long = 12& * 60&
  
  Dim dblSeconds            As Double
  
  If lngLocalTimeBias <> 0 Then
    If Abs(lngLocalTimeBias) < clngTimeBiasMax Then
      datTime = DateAdd("n", lngLocalTimeBias, datTime)
    End If
  End If
  dblSeconds = DateDiff("s", cdatUnixTimeZero, datTime)
  
  UnixTimeValue = dblSeconds

End Function

Have fun!

/gustav

>>> markamatte at hotmail.com 23-03-2007 15:00 >>>
Thanks Marty,

I found that it is actually counting from UNIX time 1/1/1970 00:00:00 
UTC...which happens to be 12/31/1969 4:00:00 PM in my time zone.  it is a 36 
base counting system...and I had to shift my initial logic ove 2 spaces.  
You take the following and add all of the results to come up with a single 
number...this number is how many seconds have passed since your start time.

>> > 1st  Value*60466176
>> > 2nd  Value*1679616
>> > 3rd  Value*46656
>> > 4th  Value*1296
>> > 5th  Value*36
>> > 6th  Value

The last 2 are irrelevant when converting back to time...they are there for 
when more than 1 record is created in a single second.

Thanks again,

Mark A. Matte

>From: MartyConnelly <martyconnelly at shaw.ca>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] OT(kinda): Crack Code
>Date: Thu, 22 Mar 2007 18:32:50 -0700
>
>  I played around with this last night and figured out it was based on some date in 1974
>with AM and PM being indicated by last two characters and first 6 as seconds
>from the start date whatever that is, still  it seems a little out as to accuracy