PostgreSQL - timezone

Databases
1

I have a simple prepared statement like this, in order to update a table.

UPDATE tbl_note SET note1 = $2, note2 = $3, obsolete = FALSE, fk_modifiedby = $4, ts_modified = now() WHERE id = $1

So the field ts_modified gets the timestamp according to this update.
My PostgreSQL databases are remote and I see times being stored as GMT + 0 timezone.
When I need to present them later, I must correct this value according to the users’ timezone, let’s say GMT + 2.
Does anybody know how to do this for mac and for windows ?

2

Piece of cake. Assuming ts_modified is a column defined as TIMESTAMP WITH TIME ZONE (which it should be), then you can select it at whatever time zone you need.

SELECT ts_modified AT TIME ZONE INTERVAL '+02:00' AS ts_modified FROM tbl_note

That’s it. Now in Xojo you can just use .DateValue from the DatabaseField. The syntax I prefer though, is

SELECT ts_modified AT TIME ZONE 'America/New_York' AS ts_modified FROM tbl_note

or you could use ‘EST’ instead. See https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT for more details.

3

Thanks Thom for the clear explanation of this great database feature.
Think I was not clear in my question since I do use the TIMESTAMP field allready.
My point is that the application needs to figure out in which timezone the user is working. His OS is aware of it and I want to use that information in the SQL as you have perfectly explained here.
My project is desktop for mac and windows.

4

Use Xojo.Core.TimeZone.Current.Abbreviation with the SQL provided.

5

postgresGetLocalTimezoneValue is a local property of the App
I call this at the start of the app, to call only once and not call it each time you store a Date
that would slow down the database access too much if you have a remote database.

[code]Function postgresGetLocalTimezone() As String
’ retourne la local timezone pour postgres ajouter quand on enregistre des dates
’ stocke la valeur dans postgresGetLocalTimezoneValue pour optimiser les acces rseaux

dim ch as String
dim rs as RecordSet

if postgresGetLocalTimezoneValue="" then
ch = “SELECT EXTRACT(TIMEZONE FROM now())/3600”
rs = VNSGetDatabase.SQLSelect( ch)
if rs<>nil then
if rs.RecordCount>0 then
postgresGetLocalTimezoneValue = rs.IdxField(1).StringValue
end if
end if
end if

Return postgresGetLocalTimezoneValue

End Function
[/code]

6

Thanks Thom and Jean-Yves for the to-the-point support.

7

[quote=278398:@Thom McGrath]The syntax I prefer though, is
SELECT ts_modified AT TIME ZONE ‘America/New_York’ AS ts_modified FROM tbl_note[/quote]

what about a simple db.SQLExecute "SET TIME ZONE '" + Xojo.Core.TimeZone.Current.Abbreviation + ";" after connecting to the DB? No need to alter each SELECT statement if you can configure it on a per session base :wink:

8

Yeah that is a pretty convenient option. I like doing it explicitly on select so that in 6 months when I need to review the code, I don’t forget it is happening.