Storing GMT offset in minutes with sign

Database
Enthusiast

Storing GMT offset in minutes with sign

Hi,

I am trying to make a table which should be to store GMT offset time along with the Sign for a city. For example
City GMT Offset Time
---------------------------------- -------------------
(GMT-04:00) Atlantic Time (Canada) -4
(GMT+05:30) Calcutta, Chennai, Mumbai, New Delhi 5.5
(GMT+06:00) Almaty, Novosibirsk 5.75

I can create a table like
create table My_Table
(
city Varchar(60),
gmt_offset_tm integer )
primary index (city)

but then how can I cast this integer to time hh:mi and then perform addition and subtraction on it?

please help

1 REPLY
Enthusiast

Re: Storing GMT offset in minutes with sign

Hold the time in an interval hour to minute field, not an integer - it is much easier to do time arithmetic on it. See below:

create Volatile table My_Table
(
city Varchar(60),
gmt_offset_tm interval Hour(2) To minute )
primary index (city)
On Commit Preserve Rows
;

Insert Into My_Table Values ('Calcutta',+'3:30');
Insert Into My_Table Values ('Almaty, Novosibirsk', +'6:00');
Insert Into My_Table Values ('Edmonton, Canada','-4:00');

Select City, GMT_OffSet_Tm, Current_Time + GMT_OffSet_TM
From My_Table
Order By 1;

Returns:

city gmt_offset_tm (Current Time(0)+gmt_offset_tm)
Edmonton, Canada -4:00 09:52:02+00:00
Calcutta 3:30 17:22:02+00:00
Almaty, Novosibirsk 6:00 19:52:02+00:00

(Sorry, the web site loses the spacing)