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
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
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
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