Results 1 to 13 of 13

Thread: Uct

  1. #1
    Join Date
    Mar 2004
    Posts
    69

    Unanswered: Utc Time Conversion To Local Time

    I'm logging SCADA data to a MS Access 2000 database. The timestamping is done using a 8 byte float value, which represents the time in milliseconds since Jan 1 1601 0:0:0. i.e 1.2735398118769E+13
    How when I display this in a form can I show it as a standard date format i.e 29/07/2004 11:52:02.
    Last edited by ISLMAN1975; 07-27-04 at 10:40.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Convert the milliseconds to seconds and then use the DateAdd function to add that to your date (Jan 1, 1601). Then you can use the Format command to get it into the final format.

  3. #3
    Join Date
    Mar 2004
    Posts
    69
    Thanks I'l have a go.

  4. #4
    Join Date
    Mar 2004
    Posts
    69
    I've Tried this on my form =
    =DateAdd("s",([Timestamp]/1000),"jan-01-1601")

    Divided my Timestamp by 1000 to convert msec to sec then used the date add function using this as my number of seconds to add onto jan-01-1601. i.e. DateAdd(interval, number, date)

    This doesn't work because my number of seconds is larger than the maximum number the dateadd function alows i.e a long which is 2,147,483,647.
    The number I get from a timestamp I took yesterday was 12,735,398,118.769 so i get a NUM! displayed on the form.
    Last edited by ISLMAN1975; 07-29-04 at 10:35.

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Then I would suggest converting to days and adding days.

  6. #6
    Join Date
    Mar 2004
    Posts
    69
    I lose the hours minutes and seconds then. I need the data acurate to the nearest second.

  7. #7
    Join Date
    Feb 2004
    Location
    Swindon, UK
    Posts
    86
    Am i reading this correctly that you need to convert Milliseconds in to actual hours/minutes/seconds??? if so, you could use the below code, you add the Milliseconds to a text box (MSecs), and click the command button(ConvertMSecs) which runs this code and shows the current date and the converted time in (ConvertedValue). i.e. if you did 540000 Milliseconds today, it would display '30/07/2004 01:30:00' as 540000 Milliseconds is 1.5 hours.

    Private Sub ConvertMSecs_Click()
    Dim MSecs, TTime

    MSecs = MSecs.Value / 100 / 60 / 60 / 24

    TTime = Format(MSecs, "hh:mm:ss")

    ConvertedValue.Value = Left(Now, 11) & TTime

    End Sub
    "Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done."

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    This was kind of challenging. Trying to avoid and Overflow error made this harder than it needed to be but this is what I have:

    [code]
    Public Function ConvertToDate(dblMilliSeconds As Double) As Date

    Dim strSeconds As String
    Dim strDays As String

    Dim dblDays As Double
    Dim dblHours As Double
    Dim dblMinutes As Double
    Dim dblSeconds As Double

    Dim lngDays As Long
    Dim lngHours As Long
    Dim lngMinutes As Long
    Dim lngSeconds As Long

    Dim dblRemainder As Double

    strSeconds = Left(dblMilliSeconds, 11)
    dblSeconds = CDbl(strSeconds)

    dblDays = (dblSeconds / 86400)
    dblHours = (dblDays - Fix(dblDays)) * 24
    dblMinutes = (dblHours - Fix(dblHours)) * 60
    dblSeconds = (dblMinutes - Fix(dblMinutes)) * 60

    lngDays = Fix(dblDays)
    lngHours = Fix(dblHours)
    lngMinutes = Fix(dblMinutes)
    lngSeconds = Fix(dblSeconds)

    ConvertToDate = DateAdd("d", lngDays, #1/1/1601#)
    ConvertToDate = DateAdd("h", lngHours, ConvertToDate)
    ConvertToDate = DateAdd("m", lngMinutes, ConvertToDate)
    ConvertToDate = DateAdd("s", lngSeconds, ConvertToDate)

    End Function
    [\code]

  9. #9
    Join Date
    Mar 2004
    Posts
    69
    Hi thanks for the help but I still can't get the code to produce the right results here are some examples.
    Date/Time Format Timestamp (Milliseconds) ConvertToDate(Timestamp)
    03/08/2004 14:22:25 1.2736012944988E+13 03/07/2006 13:00:06
    03/08/2004 14:22:29 1.2736012949334E+13 03/06/2006 13:00:28
    03/08/2004 14:22:40 1.2736012959579E+13 03/06/2006 13:00:39
    03/08/2004 14:22:50 1.2736012969824E+13 03/06/2006 13:00:48
    03/08/2004 14:23:00 1.2736012980068E+13 03/06/2006 13:00:59
    03/08/2004 14:23:08 1.2736012987519E+13 03/07/2006 13:00:06

    Quote Originally Posted by DCKunkle
    This was kind of challenging. Trying to avoid and Overflow error made this harder than it needed to be but this is what I have:

    [code]
    Public Function ConvertToDate(dblMilliSeconds As Double) As Date

    Dim strSeconds As String
    Dim strDays As String

    Dim dblDays As Double
    Dim dblHours As Double
    Dim dblMinutes As Double
    Dim dblSeconds As Double

    Dim lngDays As Long
    Dim lngHours As Long
    Dim lngMinutes As Long
    Dim lngSeconds As Long

    Dim dblRemainder As Double

    strSeconds = Left(dblMilliSeconds, 11)
    dblSeconds = CDbl(strSeconds)

    dblDays = (dblSeconds / 86400)
    dblHours = (dblDays - Fix(dblDays)) * 24
    dblMinutes = (dblHours - Fix(dblHours)) * 60
    dblSeconds = (dblMinutes - Fix(dblMinutes)) * 60

    lngDays = Fix(dblDays)
    lngHours = Fix(dblHours)
    lngMinutes = Fix(dblMinutes)
    lngSeconds = Fix(dblSeconds)

    ConvertToDate = DateAdd("d", lngDays, #1/1/1601#)
    ConvertToDate = DateAdd("h", lngHours, ConvertToDate)
    ConvertToDate = DateAdd("m", lngMinutes, ConvertToDate)
    ConvertToDate = DateAdd("s", lngSeconds, ConvertToDate)

    End Function
    [\code]
    Last edited by ISLMAN1975; 08-03-04 at 12:35.

  10. #10
    Join Date
    Mar 2004
    Posts
    69
    When using Dateadd m is months isn't it and n is minutes.

    Quote Originally Posted by ISLMAN1975
    Hi thanks for the help but I still can't get the code to produce the right results here are some examples.
    Date/Time Format Timestamp (Milliseconds) ConvertToDate(Timestamp)
    03/08/2004 14:22:25 1.2736012944988E+13 03/07/2006 13:00:06
    03/08/2004 14:22:29 1.2736012949334E+13 03/06/2006 13:00:28
    03/08/2004 14:22:40 1.2736012959579E+13 03/06/2006 13:00:39
    03/08/2004 14:22:50 1.2736012969824E+13 03/06/2006 13:00:48
    03/08/2004 14:23:00 1.2736012980068E+13 03/06/2006 13:00:59
    03/08/2004 14:23:08 1.2736012987519E+13 03/07/2006 13:00:06

  11. #11
    Join Date
    Mar 2004
    Posts
    69
    Hi DCKunkle I 've modded your code slightley so it round's the seconds as it was sometimes upto two seconds difference from my initial time stamp. The only other thing I've done was add 1 hour to the hours as it appears to be 1 hour out. I'm not sure why this should be as i'm on GMTtime so dont need to change the time zone.

    Public Function ConvertToDate(dblMilliSeconds As Double) As Date

    Dim strSeconds As String
    Dim strDays As String

    Dim dblDays As Double
    Dim dblHours As Double
    Dim dblMinutes As Double
    Dim dblSeconds As Double

    Dim lngDays As Long
    Dim lngHours As Long
    Dim lngMinutes As Long
    Dim lngSeconds As Long

    Dim dblRemainder As Double

    dblSeconds = Round((dblMilliSeconds / 1000), 0)
    MsgBox " dblSeconds = " & dblSeconds
    dblDays = (dblSeconds / 86400)
    MsgBox " dblDays = " & dblDays
    dblHours = ((dblDays - Fix(dblDays)) * 24) + 1
    MsgBox " dblHours = " & dblHours
    dblMinutes = (dblHours - Fix(dblHours)) * 60
    MsgBox " dblMinutes = " & dblMinutes
    dblSeconds = (dblMinutes - Fix(dblMinutes)) * 60
    MsgBox " dblSeconds = " & dblSeconds

    lngDays = Fix(dblDays)
    MsgBox " lngDays = " & lngDays
    lngHours = Fix(dblHours)
    MsgBox " lngHours = " & lngHours
    lngMinutes = Fix(dblMinutes)
    MsgBox " lngMinutes = " & lngMinutes
    lngSeconds = Round(dblSeconds, 0)
    MsgBox " lngSeconds = " & lngSeconds

    ConvertToDate = DateAdd("d", lngDays, #1/1/1601#)
    MsgBox " ConvertToDate lngDays = " & ConvertToDate
    ConvertToDate = DateAdd("h", lngHours, ConvertToDate)
    MsgBox " ConvertToDate lngHours = " & ConvertToDate
    ConvertToDate = DateAdd("n", lngMinutes, ConvertToDate)
    MsgBox " ConvertToDate lngMinutes = " & ConvertToDate
    ConvertToDate = DateAdd("s", lngSeconds, ConvertToDate)
    MsgBox " ConvertToDate lngSeconds = " & ConvertToDate

    End Function



    Quote Originally Posted by ISLMAN1975
    When using Dateadd m is months isn't it and n is minutes.
    Last edited by ISLMAN1975; 08-04-04 at 07:25. Reason: spwlling

  12. #12
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Yeah, I noticed my error with the 'n', but when I was trying to debug the rest of it I was also coming up an hour short. And I couldn't figure out why. I also that there would be a rounding issue with the seconds. Definitely more challenging issue than I expected. Hopefully you will be able use the code though.

  13. #13
    Join Date
    Mar 2004
    Posts
    69
    I've tested a far bit and it seems to do what I want. Thanks for the help on this. I'm not really a programmer more control systems engineer only done a bit of C ++ before.
    Quote Originally Posted by DCKunkle
    Yeah, I noticed my error with the 'n', but when I was trying to debug the rest of it I was also coming up an hour short. And I couldn't figure out why. I also that there would be a rounding issue with the seconds. Definitely more challenging issue than I expected. Hopefully you will be able use the code though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •