Unix timestamp to VB Date
in [Visual Basic]
|
Prev: Archiving
Next: Problems shelling another application
From: Karl E. Peterson on 17 Dec 2009 13:43 Dee Earley used his keyboard to write : > On 16/12/2009 15:44, Rick Rothstein wrote: >> I think you can use this... >> >> VBdate = DateAdd("s", UnixTimeStamp, #1/1/1970#) >> >> where you would assign your Unix timestamp to the indicated variable. > > It's not that simple last I looked as we are currently dealing with numbers > that have rolled over into negative values (using VBs signed longs) > > It shoudl be possible, but I never got code working properly as I just asked > my colleague to change the format to a more VB friendly value :) These are what I've always used: Public Function NetTimeToVbTime(ByVal NetDate As Long) As Double Const BaseDate# = 25569 'DateSerial(1970, 1, 1) Const SecsPerDay# = 86400 NetTimeToVbTime = BaseDate + (CDbl(NetDate) / SecsPerDay) End Function Public Function VbTimeToNetTime(ByVal VbDate As Double) As Long Const BaseDate# = 25569 'DateSerial(1970, 1, 1) Const SecsPerDay# = 86400 VbTimeToNetTime = (VbDate - BaseDate) * SecsPerDay End Function I gave them the "NetTime" moniker because that's what the Net* functions use to store last logon, password expiration, etc. But they're totally flexible, in that you can set any basedate, or even add that as an optional parameter. I see where the trouble is coming in, though, but it's still a bit in the future: ?clng(vbtimetonettime(now)) 1261046435 ?cdate(nettimetovbtime(2^31-1)) 1/19/2038 3:14:07 AM I guess I'll have to take another look at those, and add in some sign avoidance. -- ..NET: It's About Trust! http://vfred.mvps.org
From: Karl E. Peterson on 17 Dec 2009 14:09 Dee Earley explained on 12/17/2009 : > On 16/12/2009 15:44, Rick Rothstein wrote: >> I think you can use this... >> >> VBdate = DateAdd("s", UnixTimeStamp, #1/1/1970#) >> >> where you would assign your Unix timestamp to the indicated variable. > > It's not that simple last I looked as we are currently dealing with numbers > that have rolled over into negative values (using VBs signed longs) > > It shoudl be possible, but I never got code working properly as I just asked > my colleague to change the format to a more VB friendly value :) Ah, I see, yes. The following code *should* work, but fails because the second parameter to DateAdd is an *artificial* Double. That is, it's declared As Double, but produces an Overflow if the value is outside the signed range for a Long. Public Function NetTimeToVbTime(ByVal NetDate As Long, Optional BaseDate As Date = #1/1/1970#) As Date Dim Seconds As Double Const Bit31 As Double = 2147483648# ' 0x80000000 Const Bit32 As Double = 4294967296# ' 0x100000000 If NetDate >= 0 Then Seconds = CDbl(NetDate) Else Seconds = CDbl(NetDate) + Bit32 End If NetTimeToVbTime = DateAdd("s", Seconds, BaseDate) End Function Time to do some math. -- ..NET: It's About Trust! http://vfred.mvps.org
From: Nobody on 17 Dec 2009 14:20 "Ralph" <nt_consulting64(a)yahoo.com> wrote in message news:uCEc6PxfKHA.1652(a)TK2MSFTNGP05.phx.gbl... > Many "Unix Timestamps" are GMT So you need to go piddle with time zones as > well. > http://www.trap17.com/index.php/Converting-Unix-Timestamp_t20240.html > > Also as a minor warning to the OP, within any shop that is using "unix" > with > other services such as Oracle or PHP there may be a common critter > everyone > is passing around calling a "Unix TimeStamp", but in reality it might be > based one of several established "Time" formats. Always ask before going > off > and making assumptions, can save a lot of *time*. <g> The time zone conversion in the link you posted doesn't do it correctly. It doesn't handle daylight saving, and few other cases. The code need to check the return value and add other bias values based on the return value. See GetCurrentTimeBias() function in this VB6 sample: http://vbnet.mvps.org/code/locale/gettimezonebias.htm Another way to convert between local and UTC is using FileTimeToLocalFileTime/LocalFileTimeToFileTime to do the conversion. They do all the work. VB's Date can be converted to these structures using Year/Month/Day/Hour/Minute/Second functions and SystemTimeToFileTime(). To convert back, use FileTimeToSystemTime(), then DateSerial() + TimeSerial().
From: Karl E. Peterson on 17 Dec 2009 14:24 Dee Earley explained : > On 16/12/2009 15:44, Rick Rothstein wrote: >> I think you can use this... >> >> VBdate = DateAdd("s", UnixTimeStamp, #1/1/1970#) >> >> where you would assign your Unix timestamp to the indicated variable. > > It's not that simple last I looked as we are currently dealing with numbers > that have rolled over into negative values (using VBs signed longs) > > It shoudl be possible, but I never got code working properly as I just asked > my colleague to change the format to a more VB friendly value :) Okay, here ya go. Beat on these, and let us know if where they break... ' Clock rolls over into negative territory on 1/19/2038 at 3:14:07AM, ' if the basedate is the commonly used midnight on 1/1/1970. Private Const BaseDate As Date = #1/1/1970# Private Const Bit31 As Double = 2147483648# ' 0x80000000 Private Const Bit32 As Double = 4294967296# ' 0x100000000 Public Function NetTimeToVbTime(ByVal NetDate As Long) As Date Dim Seconds As Double Const SecsPerDay As Double = 86400 If NetDate >= 0 Then Seconds = CDbl(NetDate) Else Seconds = CDbl(NetDate) + Bit32 End If NetTimeToVbTime = CDbl(BaseDate) + (Seconds / SecsPerDay) End Function Public Function VbTimeToNetTime(ByVal VbDate As Date) As Long Dim Seconds As Double Seconds = DateDiff("s", BaseDate, VbDate) If Seconds >= Bit32 Then ' Beyond: 2/7/2106 6:28:15 AM ' Houston, we have a problem! ElseIf Seconds >= Bit31 Then Seconds = Seconds - Bit32 End If VbTimeToNetTime = Seconds End Function From my tests, it appears "The End of the [Unix] World" is officially February 7, 2106, at about 6:30am. :-) -- ..NET: It's About Trust! http://vfred.mvps.org
From: Rick Rothstein on 17 Dec 2009 14:44
Changing the argument of your NetTimeToVbTime function from Long to Variant and replacing your VbTimeToNetTime function with the following appears to make them both work through to 12/31/9999 (I think)... Public Function VbTimeToNetTime(ByVal VbDate As Date) As Variant VbTimeToNetTime = CDec(DateDiff("s", BaseDate, VbDate)) End Function Oh, look, a one-liner. <g> -- Rick (MVP - Excel) "Karl E. Peterson" <karl(a)exmvps.org> wrote in message news:%23BgmN60fKHA.5568(a)TK2MSFTNGP02.phx.gbl... > Dee Earley explained : >> On 16/12/2009 15:44, Rick Rothstein wrote: >>> I think you can use this... >>> >>> VBdate = DateAdd("s", UnixTimeStamp, #1/1/1970#) >>> >>> where you would assign your Unix timestamp to the indicated variable. >> >> It's not that simple last I looked as we are currently dealing with >> numbers that have rolled over into negative values (using VBs signed >> longs) >> >> It shoudl be possible, but I never got code working properly as I just >> asked my colleague to change the format to a more VB friendly value :) > > Okay, here ya go. Beat on these, and let us know if where they break... > > ' Clock rolls over into negative territory on 1/19/2038 at 3:14:07AM, > ' if the basedate is the commonly used midnight on 1/1/1970. > Private Const BaseDate As Date = #1/1/1970# > Private Const Bit31 As Double = 2147483648# ' 0x80000000 > Private Const Bit32 As Double = 4294967296# ' 0x100000000 > > Public Function NetTimeToVbTime(ByVal NetDate As Long) As Date > Dim Seconds As Double > Const SecsPerDay As Double = 86400 > > If NetDate >= 0 Then > Seconds = CDbl(NetDate) > Else > Seconds = CDbl(NetDate) + Bit32 > End If > NetTimeToVbTime = CDbl(BaseDate) + (Seconds / SecsPerDay) > End Function > > Public Function VbTimeToNetTime(ByVal VbDate As Date) As Long > Dim Seconds As Double > > Seconds = DateDiff("s", BaseDate, VbDate) > If Seconds >= Bit32 Then > ' Beyond: 2/7/2106 6:28:15 AM > ' Houston, we have a problem! > ElseIf Seconds >= Bit31 Then > Seconds = Seconds - Bit32 > End If > VbTimeToNetTime = Seconds > End Function > > From my tests, it appears "The End of the [Unix] World" is officially > February 7, 2106, at about 6:30am. :-) > > -- > .NET: It's About Trust! > http://vfred.mvps.org > > |