Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2008
    Posts
    2

    Unix Time

    Does anyone know how to convert Unix time into real time in Access? I am getting time stamps like this: 1193252290 and I need it to look like this: 20080701. Thanks,

    Wifor

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I think this was written by Bob Askew but I'n not sure.
    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

  3. #3
    Join Date
    Jul 2008
    Posts
    2

    Unix time w/ timezones

    Can anyone show me what is wrong with this code. I need to accurately portray time in the eastern time zone. It is off by 5 hours. The red code is what I have been playing with. Cant seem to get it to work. thanks ahead of time...Wifor



    SELECT dbo_CDRMAIN.LocalDay, dbo_CDRMAIN.CallerNum, dbo_CDRMAIN.TargetNum, dbo_CDRMAIN.ExitState, Format(([dbo_CDRMAIN.StartTime]/86400-5),"hh:nn:ss") AS Real_Start_Time, Format(([dbo_CDRMAIN.endTime]/86400-5),"hh:nn:ss") AS Real_End_Time, Format(([dbo_CDRMAIN.TalkDuration]/86400),"hh:nn:ss") AS Real_Talk_Duration, qry_testtotaldayduration.[Total Day Talk Duration]
    FROM dbo_CDRMAIN INNER JOIN qry_testtotaldayduration ON dbo_CDRMAIN.LocalDay=qry_testtotaldayduration.Loca lDay
    WHERE (((dbo_CDRMAIN.LocalDay) Between [Enter Start Date yyyymmdd] And [Enter End Date yyyymmdd]) AND ((dbo_CDRMAIN.TargetNum)="18888209266" Or (dbo_CDRMAIN.TargetNum)="918888209266" Or (dbo_CDRMAIN.TargetNum)="618888209266") AND ((dbo_CDRMAIN.DNIS)="7196") AND ((dbo_CDRMAIN.ExitState)=9))
    GROUP BY dbo_CDRMAIN.LocalDay, dbo_CDRMAIN.CallerNum, dbo_CDRMAIN.TargetNum, dbo_CDRMAIN.ExitState, qry_testtotaldayduration.[Total Day Talk Duration], dbo_CDRMAIN.StartTime, dbo_CDRMAIN.EndTime, dbo_CDRMAIN.TalkDuration
    ORDER BY dbo_CDRMAIN.LocalDay, dbo_CDRMAIN.ExitState;

    bdb_HaP_Conference_Calls
    LocalDayCallerNumTargetNumExitStateReal_Start_TimeReal_End_TimeReal_Talk_DurationTotal DayTalkDuration
    20080701 8914186656092669 18:51:54 18:52:4500:00:4800:13:4720080701891 4186656092669 17:24:35 17:25:2100:00:4300:13:47 20080701 8914186656092669 16:59:22 17:06:3800:07:1300:13:47

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums