|
|
|
#2
07-26-2008, 08:54 AM
|
||||
|
||||
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
07-29-2008, 09:01 AM
|
|||
|
|||
|
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
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Windows 7 
Access 2007 (version 12.0)
Linear Mode
