Date Conversion

[StephanieHarris]

Hello,

I'm really stuck on this one. I need to be able to convert a UTC date into the local date and time. The value that I read is the number of seconds since midnight on january 1, 1970. I need to convert that number into the local date and time. For example, I'm given the number 1114706663. Does anyone know how I can convert this to the local date and time?

Thank you so much!

Steph

[PeejAvery]

Here are some links that might help.

http://www.freevbcode.com/ShowCode.asp?ID=6774
http://www.vb-helper.com/howto_utc_to_local_time.html

[StephanieHarris]

Thanks for the links. I actually was looking at these earlier. The problem I am having with these functions is that they take a date value as a parameter. The value I have to work with is an integer value ie:1114706663, that represents the number of seconds since midnight jan 1, 1970.

Here is the one function...

' Convert UTC time to local time for current time zone
Public Function UTCtoLocal(ByVal tDate As Date) As Date
    Dim tzi As TIME_ZONE_INFORMATION
    Dim stUTC As SYSTEMTIME
    Dim stLocal As SYSTEMTIME
    Dim lRes As Long
    
    lRes = GetTimeZoneInformation(tzi)
    stUTC.wYear = Year(tDate)
    stUTC.wMonth = Month(tDate)
    stUTC.wDay = Day(tDate)
    stUTC.wHour = Hour(tDate)
    stUTC.wMinute = Minute(tDate)
    stUTC.wSecond = Second(tDate)
    stUTC.wMilliseconds = 0
    lRes = SystemTimeToTzSpecificLocalTime(tzi, stUTC, stLocal)
    UTCtoLocal = DateSerial(stLocal.wYear, stLocal.wMonth, stLocal.wDay) + TimeSerial(stLocal.wHour, stLocal.wMinute, stLocal.wSecond)
End Function

Am I overlooking something?

Thanks,

Steph

[rxbagain]

Private Type SYSTEMTIME
   wYear As Integer
   wMonth As Integer
   wDayOfWeek As Integer
   wDay As Integer
   wHour As Integer
   wMinute As Integer
   wSecond As Integer
   wMilliseconds As Integer
End Type
Private Declare Function SystemTimeToTzSpecificLocalTime Lib "kernel32" ( _
   lpTimeZoneInformation As Any, _
   ByRef lpUniversalTime As SYSTEMTIME, _
   ByRef lpLocalTime As SYSTEMTIME) As Long
Private Declare Function VariantTimeToSystemTime Lib "oleaut32.dll" ( _
   ByVal vTime As Double, _
   ByRef lpSystemTime As SYSTEMTIME) As Long

Function MakeLocalDateTime(ByVal nSecondsSince1970 As Double) As Date
   Dim dateUTC As Date, stUTC As SYSTEMTIME, stLTC As SYSTEMTIME
   dateUTC = DateAdd("s", nSecondsSince1970, #1/1/1970#)
   Call VariantTimeToSystemTime(dateUTC, stUTC)
   Call SystemTimeToTzSpecificLocalTime(ByVal 0&, stUTC, stLTC)
   MakeLocalDateTime = DateSerial(stLTC.wYear, stLTC.wMonth, stLTC.wDay) _
      + TimeSerial(stLTC.wHour, stLTC.wMinute, stLTC.wSecond)
End Function

Private Sub Form_Load()
   MsgBox MakeLocalDateTime(1114706663)
End Sub

Hope it will help

[Shuja Ali]

Am I overlooking something?

You definetly are overlooking the very basic functions present in VB.

Why make things so complex when you could do it in a single line.

 Debug.Print DateAdd("s",1114706663,"01-Jan-1970")

This will print 4/28/2005 4:44:23 PM and code posted by rxbagain also returns the same value. Why use so many API's and so many lines of code.
I would have rather taken a look at the date functions that are available in VB instead of taking the API route. I only use APIs when the kind of functionality that I want is not present in VB.
Ps: No offenses.

[StephanieHarris]

I'm not that advanced, so I could be wrong but it looks like the code posted by rxbagain will also take into account what time zone you are in.

Is this correct?

Thanks,
Steph

[PeejAvery]

Looks that way. I wonder though, why would the zone matter?

[Shuja Ali]

I'm not that advanced, so I could be wrong but it looks like the code posted by rxbagain will also take into account what time zone you are in.

Well I would still say No. The case of time zones doesn't arise here. You actually need a date that is 1114706663 seconds since 01-Jan-1970. So whether you are in US or Japan won't matter here. It will always give you the same result.

[rxbagain]

I answered the question with the assumption that the time is translated to UTC before he arrived at the number (number of seconds) and what he wants is to convert it in the local time value. And so what I did. I assumed that the date value came from other source (other pc or even other country), translated to UTC and was sent to the program for processing.

StephanieHarris, how/where did you get the number? Is it computed base on UTC?

[StephanieHarris]

It is based on UTC and in fact, it is coming from a different pc. I'm reading this value from a sql server database that could be located anywhere (not on the same machine as my program for sure)

Thanks,

Steph

[rxbagain]

Originally posted by StephanieHarris

It is based on UTC and in fact, it is coming from a different pc. I'm reading this value from a sql server database that could be located anywhere (not on the same machine as my program for sure)

Then the timezone conversion is really needed in your case. If you need to display it as UTC date, you would just use DateAdd, but in your case you really need to do that.

[Shuja Ali]

Then the timezone conversion is really needed in your case. If you need to display it as UTC date, you would just use DateAdd, but in your case you really need to do that.

Well in this case you will have to convert it to the Locat Date/Time.
How could I miss UTC in the First post itself? Confusion confuses

[StephanieHarris]

Thanks for all of the help. I'm on my way now!!!

Steph