|
|
|
Forum Member
 
Group: Forum Members
Last Login: 10/19/2009 6:44:26 PM
Posts: 38,
Visits: 409
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
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)
|
|
|
|