I have a simple prepared statement like this, in order to update a table.
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 ?
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.
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.
Use Xojo.Core.TimeZone.Current.Abbreviation with the SQL provided.
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
Thanks Thom and Jean-Yves for the to-the-point support.
[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
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.