Getting the local timezone

Databases
1

I am using a Postgres database the exists in GMT -05 and I have a timestamp with timezone field.

Right now, no matter what time the local time is, the times are coming up (in Xojo) as what time it was where the database exists.

How would I display the times correctly for the local timezone?

2

it depends on WHEN the date/time is to know what the right TZ offset for that date is when you show it in local terms

if you just create a new date and shove in the year, month, day, hour, minute, and second from what you get from postgresql you have todays offset with a date that MAY have a different offset than today does
ie/ suppose you have a date from postgresql that is “jan 3, 2016”
here the tz offset should be GMT-7 on that date but “new date” would, today, tell you its GMT-6

this is SUCH fun

3

Assuming mDb = a living connection to a PostgreSQL database, in DbOSTimeOfset you will find the time-diff between you local machine and the remove PG database.

[code] // Get ofset in seconds OS vss GMT
Using Xojo.Core
Dim myDate As Date = Date.Now // Get date for current time zone
Dim gmt As New TimeZone(0) // Get GMT time zone
Dim gmtDate As New Date(myDate.SecondsFrom1970, gmt)
mOsTimeOfsetGMT = (myDate.hour - gmtDate.hour) * 3600

// Case 2 // PostgreSQL

Dim rsDbOfset As RecordSet = mDb.SQLSelect("SELECT EXTRACT(TIMEZONE FROM now())")
If rsDbOfset <> Nil Then intDbTimeOfsetGMT = rsDbOfset.field("date_part").integervalue
DbOsTimeOfset = mOsTimeOfsetGMT - intDbTimeOfsetGMT[/code]
4

Pretty sure that only tells you what it is TODAY and thats already easy to get

Getting the right GMT offset for ANY day for the client & server is significantly harder

I know I solved this once before but that’s code I’m not at liberty to give away

5

yes.