(Date) to (Long) seconds since GMT 1/1/1970?

[andrew_waycott]

I want to make a function that will convert from a DATE/TIME datatype to a LONG datatype containing the number of seconds from 1-January-1970 to the supplied date.

The trick is that the seconds must always reflect seconds in GMT and the Date supplied may be from a different part of the year (Daylight Savings Time vs Standard Time). So, before converting to seconds, I must convert the DATE/TIME to GMT time.

How can I get the RULES for converting from a TIME in a particular timezone to GMT time? Ideally, it would be nice if I could also send as a parameter the Timezone.

[andrew_waycott]

HHHEEELLLPPP!!!!! Anyone????

[kedaman]

Shoudn't be that hard to convert to and from GMT:

GMTDate=DateAdd("h", -Zone, Date)
Date=DateAdd("h", Zone, GMTDate)

Also what do you mean by converting to long?`That you want to compress the data for file storage?

[JHausmann]

Dates are stored (internally) as real numbers. The integer portion is the number of days from some point in time and I believe the decimal portion is the number of ticks since midnight (although it's been a long time since I've played with this). It is possible to convert a date using CDBL to this numeric format, you could then use the MOD function to remove the day portion...

Do you mean you want it in Long Date? To come out like: 1-January-1970?

Using Kedaman's code:

GMTDate = DateAdd("h", -Zone, Date)
Date = DateAdd("h", Zone, GMTDate)
NewGMTD = Format$(GMTDate, "m-dddd-yyyy")
NewDate = Format$(Date, "m-dddd-yyyy")

MsgBox "GMT Date:  " & NewGMTD & Chr$(10) & "Date:  " & NewDate

And to recieve the time:

GMTTime = DateAdd("h", -Zone, Time)
Time = DateAdd("h", Zone, GMTTime)
NewGMTT= Format$(GMTTime, "hh:mm:ss AMPM")
NewTime = Format$(Time, "hh:mm:ss AMPM")

MsgBox "GMT Time:  " & NewGMTT & Chr$(10) & "Time:  " & NewTime

Thanks for the code Kedaman..it will come in handy one day.

[andrew_waycott]

The conversion to a Long datatype is not the problem... got that code down pat...

number of seconds since 1/1/1970 is:

CLng(ProvidedDate - CDate(DateSerial(1970, 1, 1)))

For the date part. The Time part is:

Hour(ProvidedDate) * 3600 + _
Minute(ProvidedDate) * 60 + _
Second(ProvidedDate)

My Problem is with the conversion to Greenwich Mean Time.

For example:

If it is the middle of Summer (Daylight Savings Time) and I ask to convert a date from the winter to a time stamp that is based on the number of seconds since GMT 1/1/1970 12:00 AM, I have to first convert the Date/Time to GMT.

In order to do this I have to know:

a) how many hours difference it is from GMT to the time zone of the computer running the program (not always the same)

b) if the date falls in DST or Standard Time. If it is in DST, then I also need to know what the difference is for DST as in some places it is not 1 hour.

[andrew_waycott]

Just to clarify, by Long I mean a Long Integer holding the number of seconds from January 1st, 1970.

Thanks for the help...

Andrew

[kedaman]

Something like this you want, andrew?

Function Date2Long(dte As Date) As Long
    Date2Long = Second(dte) + Minute(dte) * 60 + Hour(dte) * 3600 + Day(dte) * 86400 + Month(dte) * 2764800 + (Year(dte) - 1998) * 35942400
End Function
Function Long2Date(lng As Long) As Date
    Long2Date = CDate(Int(lng / 86400) Mod 32 & "." & Int(lng / 2764800) Mod 13 & "." & Int(lng / 35942400) + 1998 & " " & Int(lng / 3600) Mod 24 & ":" & Int(lng / 60) Mod 60 & ":" & lng Mod 60)
End Function

[kedaman]

nah, i think this is what you need (sorry i didn't refresh the browser for your last comments)

DateDiff("s", DateAdd("h", -Zone, CDate(DateSerial(1970, 1, 1))), Now)

[andrew_waycott]

Thanks kedaman, but how to I figure out what Zone should be?

Andrew

[kedaman]

Do you mean if it's entered in your computer somewhere? in registry?

No idea

[kedaman]

ACtually, after searching around in registry i found this:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\DaylightFlag

that's 02 00 00 00 (my timezone is GMT +2)

You could use my Registry module on my homepage for retrieving the value of it with Regval property

[andrew_waycott]

Yeah, I want to check the system (For example the regional settings) to find out what timezone the program is running in and then based on what it tells me adjust the date.

[kedaman]

Would look like this:

YourLong=DateDiff("s", DateAdd("h", -asc(regval("HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\DaylightFlag")), CDate(DateSerial(1970, 1, 1))), Now)

remeber to add my registry module

You can get your time zone (GMT) here: TimeZoneConverter.