[SIZE=12px]I have a project which needs to accommodate different timezones. All data is timestamped in UTC and there is a system setting where local timezone is stored.
I have written simple code to accomplish this which works in the SQL builder but does which gives an error at run time in my project.[/SIZE]
[SIZE=16px][SIZE=12px]SELECT [/SIZE][/SIZE]
[SIZE=16px][SIZE=12px] MAC,[/SIZE][/SIZE]
[SIZE=16px][SIZE=12px] time,[/SIZE][/SIZE]
[SIZE=16px][SIZE=12px] TIMESTAMPADD(HOUR, tz_int, time) AS localtime[/SIZE][/SIZE]
[SIZE=16px][SIZE=12px]FROM [/SIZE][/SIZE]
[SIZE=16px][SIZE=12px] entry[/SIZE][/SIZE]
[SIZE=16px] [/SIZE]
[SIZE=16px] [/SIZE]
[SIZE=16px][SIZE=12px]where timezone data comes from the field tz_int which uses a lookup:[/SIZE][/SIZE]
[SIZE=16px][SIZE=12px]SELECT body [/SIZE][/SIZE]
[SIZE=16px][SIZE=12px]FROM settings [/SIZE][/SIZE]
[SIZE=16px][SIZE=12px]WHERE header = ‘Time Zone’[/SIZE][/SIZE]
[SIZE=12px]I can see tz_int returning the correct integer offset to be added/subtracted from the hour. Importantly, when I replace the field name tz_int with an integer (below), the application works as expected without error but I need to be able to use a variable here to allow user changes.[/SIZE] [SIZE=16px][SIZE=12px]SELECT [/SIZE][/SIZE]
[SIZE=16px][SIZE=12px] MAC,[/SIZE][/SIZE]
[SIZE=16px][SIZE=12px] time,[/SIZE][/SIZE]
[SIZE=16px][SIZE=12px] TIMESTAMPADD(HOUR, -5, time) [/SIZE][/SIZE]
[SIZE=16px][SIZE=12px]FROM [/SIZE][/SIZE]
[SIZE=16px][SIZE=12px] entry[/SIZE][/SIZE]
[SIZE=12px]Please let me know if anyone has any suggestions on how to resolve this.
Thanks![/SIZE]