Convert time zone

Anyone know of a simple way I can change the time zones on a date time value?

Hi @dataman,

The Time and Timestamp values in the database are stored in UTC and don’t contain any TZ information. There isn’t any specific built-in function in the database to change the TimeZone to such datatypes.

What do you mean precisely by "Changing TZ on DateTime value?
You can change them programmatically in your client application (e.g., as default in JDBC, the Timestamps/Time are adjusted to the actual client TimeZone) using the appropriate APIs, or try to use the intervals built-in functions like timestampadd to add or subtract hours to Timestamp fields to reflect the local timezone.

as an example:

CREATE TABLE timezone_test (
  ts_field TIMESTAMP(0),
  t_field TIME,
  tz_offset TINYINT);
omnisql> insert into timezone_test values ('2011-04-10 14:06:59','17:24:10',2);
omnisql> insert into timezone_test values ('2011-01-04 22:11:10','21:04:08',-5);
omnisql> insert into timezone_test values ('2011-05-01 02:35:45','21:04:08',-5);

We are storing the offset on a separate field, then run a query applying the offset to the field content

omnisql> select timestampadd(hour,tz_offset,ts_field),ts_field,tz_offset from timezone_test;
2011-04-10 16:06:59|2011-04-10 14:06:59|2
2011-01-04 17:11:10|2011-01-04 22:11:10|-5
2011-04-30 21:35:45|2011-05-01 02:35:45|-5
3 rows returned.
Execution time: 16 ms, Total time: 17 ms

Or you can store the TZ in another field and use a client APIs to apply it to values.

Hopes it helps

Changing time zone is a lot more complicated than adding an offset as you have to account for things like DST. I was hoping to be able to do it on the server side so I don’t have to go back and forth. Dealing with timezones is very important for time series analysis.

I know it is more complicated.

You could insert all timestamps using UTC timezone for analysis, so they are comparable (and if needed store the timestamp using local timezone on another column).

I know it’s not ideal but I had to deal for a lot of years with oracle database; the timestamp with tz datatype isn’t a so old feature.

BTW I will ask internally if that feature is on the radar