UTC dates to local

  1. 4 months ago

    Wayne G

    Jan 10 Pre-Release Testers New Zealand axisdirect.nz

    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.

    -image-

    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

    // 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)

    I think you're over-complicating things (famous last words)...

    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

    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.

  2. Antonio R

    Jan 11 Pre-Release Testers, Xojo Pro Europe (Italy)
    //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
    
  3. Wayne G

    Jan 11 Pre-Release Testers New Zealand axisdirect.nz

    @Antonio R Dim realDate As date=New date(gmtDate.SecondsFrom1970, New Xojo.Core.TimeZone("NZ"))

    Thanks @Antonio R, 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.

  4. Antonio R

    Jan 11 Pre-Release Testers, Xojo Pro Europe (Italy)

    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?

  5. Wayne G

    Jan 11 Pre-Release Testers New Zealand axisdirect.nz

    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)

  6. Kem T

    Jan 11 Pre-Release Testers, Xojo Pro, XDC Speakers New York

    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 )
  7. Wayne G

    Jan 11 Pre-Release Testers New Zealand axisdirect.nz

    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.

  8. Antonio R

    Jan 11 Pre-Release Testers, Xojo Pro Europe (Italy)
    Edited 4 months ago

    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.

  9. Alberto D

    Jan 11 Pre-Release Testers, Xojo Pro

    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.

  10. Ian J

    Jan 12 Pre-Release Testers, Xojo Pro Answer Somerset, UK

    I think you're over-complicating things (famous last words)...

    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

    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.

  11. Wayne G

    Jan 14 Pre-Release Testers New Zealand axisdirect.nz

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

or Sign Up to reply!