Getting current timezone

General
1

I had problems in getting TimeZone like “+01:00” on Mac, Windows and Linux…
I found a solution:

[code] Dim GlobalTime, LocalTime As String
Dim GlobalD As New Date
Dim LocalD As New Date
Dim TZ As String

Dim db As New SQLiteDatabase
dim rec as RecordSet

// Get GlobalTime and LocalTime With SQLite Database

If db.Connect Then
rec = db.SQLSelect(“SELECT datetime(‘now’) As GlobalTime, datetime(‘now’, ‘localtime’) As LocalTime;”)
If rec <> Nil and Not rec.EOF Then
GlobalTime = rec.Field(“GlobalTime”).StringValue
LocalTime = rec.Field(“LocalTime”).StringValue
End if
End If
db.Close

// Tranform String 2015-01-07 13:50:01 in Date

GlobalD.Year = Val(Mid(GlobalTime, 1, 4))
GlobalD.Month = Val(Mid(GlobalTime, 6, 2))
GlobalD.Day = Val(Mid(GlobalTime, 9, 2))
GlobalD.Hour = Val(Mid(GlobalTime, 12, 2))
GlobalD.Minute = Val(Mid(GlobalTime, 15, 2))
GlobalD.Second = Val(Mid(GlobalTime, 18, 2))

LocalD.Year = Val(Mid(LocalTime, 1, 4))
LocalD.Month = Val(Mid(LocalTime, 6, 2))
LocalD.Day = Val(Mid(LocalTime, 9, 2))
LocalD.Hour = Val(Mid(LocalTime, 12, 2))
LocalD.Minute = Val(Mid(LocalTime, 15, 2))
LocalD.Second = Val(Mid(LocalTime, 18, 2))

// Calculate DateDiff in Hours

TZ = Str((GlobalD.TotalSeconds - LocalD.TotalSeconds) / 3600)

// Transform -1,5 => +01:30

If Left(TZ,1) = “-” Then
TZ = ReplaceAll(TZ,"-","+")
Else
TZ = “-” + TZ
End If

If Right(TZ,2) = “.5” Then
TZ = ReplaceAll(TZ,".5",":30")
Else

If Right(TZ,2) = ",5" Then
  TZ = ReplaceAll(TZ,",5",":30")
Else
  TZ = TZ + ":00"
End If

End If

If Mid(TZ,3,1) = “:” Then TZ = Left(TZ,1) + “0” + Right(TZ,4)

// see the result

MsgBox TZ[/code]

Perhaps it might be helpful to someone else :slight_smile:

(Someone could test if it works in other Counties? Thank you)

Rob

2

you realize that SQLDATE will translate this in one line
// Tranform String 2015-01-07 13:50:01 in Date

3

Really Dave? Could you make a sample?

4

GlobalID.SqlDateTime=“2015-01-07 13:50:01”

5

Thanks Dave, now the code looks better:

[code] Dim GlobalTime, LocalTime As String
Dim GlobalD As New Date
Dim LocalD As New Date
Dim TZ As String

Dim db As New SQLiteDatabase
dim rec as RecordSet

// Get GlobalTime and LocalTime With SQLite Database Calculation

If db.Connect Then
rec = db.SQLSelect(“SELECT datetime(‘now’) As GlobalTime, datetime(‘now’, ‘localtime’) As LocalTime;”)
If rec <> Nil and Not rec.EOF Then
GlobalTime = rec.Field(“GlobalTime”).StringValue
LocalTime = rec.Field(“LocalTime”).StringValue
End if
End If
db.Close

// Tranform String 2015-01-07 13:50:01 in Date
GlobalD.SqlDateTime = GlobalTime
LocalD.SqlDateTime = LocalTime

// Calculate DateDiff in Hours
TZ = Str((GlobalD.TotalSeconds - LocalD.TotalSeconds) / 3600)

// Transform -1,5 => +01:30

If Left(TZ,1) = “-” Then
TZ = ReplaceAll(TZ,"-","+")
Else
TZ = “-” + TZ
End If

If Right(TZ,2) = “.5” Then
TZ = ReplaceAll(TZ,".5",":30")
Else

If Right(TZ,2) = ",5" Then
  TZ = ReplaceAll(TZ,",5",":30")
Else
  TZ = TZ + ":00"
End If

End If

If Mid(TZ,3,1) = “:” Then TZ = Left(TZ,1) + “0” + Right(TZ,4)

// see the result
MsgBox TZ[/code]

6

Dim tz As xojo.Core.TimeZone = xojo.core.TimeZone.Current

dim offset as integer = tz.SecondsFromGMT

dim prefix as string
if offset < 0 then prefix = “-” else prefix = “+”

offset = abs(offset)

dim hours as integer = offset / 3600
offset = offset mod 3600
dim minutes as integer = offset / 60

dim s as string = prefix + str(hours,“00”) + “:” + str(minutes,“00”)

7

Very very good Norman :slight_smile:

8

just for kicks

prefix=if(offset<0,"-","+")
9

Norman that’s a super solution I wish I had found it before I wrote this:

  Dim s As String
  Dim strOffset As String
  Dim strGMTOffset As String
  Dim d As New Date
  Dim i As integer
  Dim arr() As String
  
  strGMTOffset = str(d.GMTOffset)
  
  arr = strGMTOffset.Split( "." )
  
  if arr.Ubound > 0 then
    Select Case arr(1)
    Case "5"
      strOffset = arr(0) + ":30"
    Case "75"
      strOffset = arr(0) + ":45"
    End Select
  else
    strOffset = strGMTOffset + ":00"
  end if
  
  s = strOffset.left(1)
  
  if s <> "-" then
    i = cDbl(s)
    if i > 1 then
      strOffset = "+" + strOffset
    end
  end
  
  Return strOffset