UTC dates to local

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]

//Say tDate is a text with your sqldatetime date 
//Sample date as sqldatetime
dim tDate as text="2018-01-11 00:00:00"

//Convert to date
dim readDate as Xojo.Core.Date=Xojo.Core.Date.fromText(tDate)
//readDate is in your current Time Zone

//Transform it to GMT (same date-time but with GMT TZ)
dim gmtDate as Xojo.Core.Date=new Xojo.Core.Date(readDate.Year, readDate.Month, readDate.Day, readDate.Hour, readDate.Minute, readDate.Second, New TimeZone(0))

//Now convert to Time Zone you want
Dim realDate As date=New date(gmtDate.SecondsFrom1970, New Xojo.Core.TimeZone("NZ"))

//You can use some methods (in a module)
//To convert a GMT sqlDateTime text to GMT date
Public Function toGMT(extends t as Text) as Xojo.Core.Date
  Dim d As Xojo.Core.Date=Xojo.Core.Date.FromText(t)
  Dim gmt As Xojo.Core.Date=New Xojo.Core.Date(d.Year, d.Month, d.Day, d.Hour, d.Minute, d.Second, New Xojo.Core.TimeZone(0))
  Return gmt
End Function

//To convert a GMT date to the desired TimeZone (default to Current)
Public Function fromGMT(extends d as Xojo.Core.Date, tz as Xojo.Core.TimeZone=nil) as Xojo.Core.Date
  Return New Xojo.Core.Date(d.SecondsFrom1970, If(tz=Nil, Xojo.Core.TimeZone.Current, tz))
End Function

//To convert a GMT sqlDateTime text to a date with the desired TimeZone (default Current)
Public Function fromGMT(extends t as Text, tz as Xojo.Core.TimeZone=nil) as Xojo.Core.Date
  Return t.toGMT.fromGMT(tz)
End Function
1 Like

Thanks @Antonio Rinaldi, the problem is that New Xojo.Core.TimeZone (“NZ”) [“Pacific/Auckland” is correct] is that it has the current GMT offset (SecondsfromGMT) which is not correct for the GMT date/time. I was hoping that getting the date interval between now & then in GMT and then applying that to current Date/Time NZ would get the correct time. What I’m getting is the GMT time converted to NZDT instead of NZST.

Either I’m missing something obvious or this is a bug in the new framework.

A right conversion should be: a date like 2018-09-01 converted as NZST and a 2018-01-01 as a NZDT. (If I have understood well the border dates)
What do you get?

GMT Date 2017-09-01 02:00:00 becomes 2017-08-31 03:00:00 (this is wrong by 1 hr, should be 02:00:00)
GMT Date 2018-01-01 02:00:00 becomes 2017-12-31 03:00:00 (this is correct)

If you’re interested in the classic Date solution…

dim d as new Date( y, m, d, h, mm, s )
d.GMTOffset = d.GMTOffset + d.GMTOffset
d = new Date( d.Year, d.Month, d.Day, d.Hour, d.Minute, d.Second )
1 Like

I thought the whole point of dateinterval was to accommodate daylight savings adjustments? I can use the classic date, but would need to write my own rules for the boundaries which is fine for one timezone, but a pain for many.

I don’t understand:
GMT 2017-09-01 02:00:00 should become (NZ): 2017-09-01 14:00:00(you are before GMT not after)
GMT 2018-01-01 02:00:00 should become (NZ): 2018-01-01 15:00:00

And with my functions work. (at least on macOS)
How can GMT Date 2018-01-01 02:00:00 be transformed to 2017-12-31 03:00:00?
delta for UTC is from -12:00 to +14:00

You don’t have to use dateInterval. Simply use the TimeZone parameter.

Antonio good info and code. I learned from it.

I copy/past the first half of your code and got errors, I fixed them by adding Xojo.Core in a couple of places. In the end I removed all the Xojo.Core references and put the Using Xojo.Core at first line.

Now I’m learning/testing the second half, methods, modules and extends are new to me. I hope I can make it work.

I think you’re over-complicating things (famous last words)…

[code]Dim GMTDate As Xojo.Core.Date = New Xojo.Core.Date(2017, 9, 1, 2, 0, 0, 0, New Xojo.Core.TimeZone(0))
Dim NZDate As Xojo.Core.Date = New Xojo.Core.Date(GMTDate.SecondsFrom1970, New Xojo.Core.TimeZone(“NZ”))

GMTDateLabel.Text = GMTDate.ToText + " " + GMTDate.TimeZone.Abbreviation // 2017-09-01 02:00:00 GMT
NZDateLabel.Text = NZDate.ToText + " " + NZDate.TimeZone.Abbreviation // 2017-09-01 14:00:00 NZ

GMTDate = New Xojo.Core.Date(2018, 1, 1, 2, 0, 0, 0, New Xojo.Core.TimeZone(0))
NZDate = New Xojo.Core.Date(GMTDate.SecondsFrom1970, New Xojo.Core.TimeZone(“NZ”))

GMTDateLabel2.Text = GMTDate.ToText + " " + GMTDate.TimeZone.Abbreviation // 2018-01-01 02:00:00 GMT
NZDateLabel2.Text = NZDate.ToText + " " + NZDate.TimeZone.Abbreviation // 2018-01-01 15:00:00 NZ[/code]

You can replace the usage of Xojo.Core.TimeZone("NZ") with Xojo.Core.TimeZone.Current for the real deal when you want to display the date in the user’s timezone.

Specifying “NZ” as the timezone takes care of daylight saving as the date my code uses to create the NZ (or local) date uses the constructor that takes seconds from the epoch in GMT.

I was sure I’d tried that. Thanks for the input everyone.