I have a table in a database where date/times are stored in the UTC timezone. I’m trying to convert these to local date/times, but not having much success.
In NZ 1st September is NZST (GMT-12) while currently we are at NZDT (GMT-13) and I’m having trouble getting the UTC date to present in NZST. At the moment I’m getting the current date/time using xojo.core.date.current. Creating the GMT version, getting the Date Interval from that and applying it to the current time to arrive at the incorrect date.
Any ideas on how to work around this?
This is my code to perform the conversion
[code]// Parse the SQL Date/Time from the text field
Dim y As Integer = Val(Left(dbDatefield.Text, 4))
Dim m As Integer = Val(Mid(dbDatefield.Text, 6, 2))
Dim d As Integer = Val(Mid(dbDatefield.Text, 9, 2))
Dim h As Integer = Val(Mid(dbDatefield.Text, 12, 2))
Dim mm As Integer = Val(Mid(dbDatefield.Text, 15, 2))
Dim s As Integer = Val(Mid(dbDatefield.Text, 18, 2))
Dim t As New Xojo.Core.TimeZone(0) ’ GMT
Dim dg As New Xojo.Core.Date(y, m, d, h, mm, s, t) ’ Converted to date
Dim dn As Xojo.Core.Date = Xojo.Core.Date.Now ’ Current Date/Time local
currentTime.Text = dn.ToText(Xojo.Core.Locale.Current, xojo.Core.Date.FormatStyles.Short, Xojo.Core.Date.FormatStyles.Short)
currentSecondsFromGMT.Text = Str(dn.TimeZone.SecondsFromGMT)
Dim dgn As New Xojo.Core.Date(dn.SecondsFrom1970, t) ’ Current Date/Time converted to GMT
currentTimeUTCField.Text = dgn.ToText(Xojo.Core.Locale.Current, xojo.Core.Date.FormatStyles.Short, Xojo.Core.Date.FormatStyles.Short)
// Get the Date interval (current GMT - Original GMT)
Dim di As Xojo.Core.DateInterval = dgn - dg
IntervalLabel.Text = Str(di.Years) + ", " _
- Str(di.Months) + ", " _
- Str(di.Days) + ", " _
- Str(di.Hours) + ", " _
- Str(di.Minutes) + ", " _
- Str(di.Seconds)
// Apply the interval to the current Date/Time
Dim dt As Xojo.Core.Date = dn - di
localDateField.Text = dt.ToText(Xojo.Core.Locale.Current, Xojo.Core.Date.FormatStyles.Short, xojo.Core.Date.FormatStyles.Short)
dbSecondsFromGMT.Text = Str(dt.TimeZone.SecondsFromGMT)[/code]