_shift_timezone

Shifts a timestamp value from one time zone to another time zone. This function honors the Daylight Savings Time when applicable. If the first argument is of type "timestamp", then the second and third arguments represent the "from" and "target" time zones, respectively. If the first argument is of type "timestamp with time zone", then the "from" time zone is already implied in the first argument therefore the second argument represents the "target" time zone. The data type of the first argument will also determine the data type of the return value. The second and third arguments are of type "string" and represent time zone identifiers. A list of these identifiers can be found below. Note: using this function will cause local processing.

Note: Supported only in dynamic query mode.

Syntax

_shift_timezone ( timestamp_value , from_time_zone ,
 target_time_zone )
_shift_timezone ( timestamp_with_time_zone_value , target_time_zone )

Example 1

_shift_timezone( 2013-06-30 12:00:00 , 'EST' , 'GMT' )

Result

2013-06-30 16:00:00

Example 2

_shift_timezone( 2013-11-30 12:00:00-05:00 , 'PST' )

Result

2013-11-30 09:00:00-08:00

Example 3

Time zone abbreviations:

Result data

GMT	(GMT+00:00) Greenwich Mean Time
UTC	(GMT+00:00) Coordinated Universal Time
WET	(GMT+00:00) Western Europe Time: Lisbon, Faeroe Islands, Canary 
Islands
ECT	(GMT+01:00) European Central Time: Amsterdam, Brussels, Paris, 
Rome, Vienna
MET	(GMT+01:00) Middle European Time
ART	(GMT+02:00) Egypt Time: Cairo, Damascus, Beirut, Amman, Nicosia
CAT	(GMT+02:00) Central African Time: Johannesburg, Blantyre, Harare, 
Tripoli
EET	(GMT+02:00) Eastern Europe Time: Athens, Kiev, Sofia, Minsk, 
Bucharest, Vilnius, Tallinn
EAT	(GMT+03:00) East Africa Time: Addis Ababa, Asmera, Kampala, 
Nairobi, Mogadishu, Khartoum
NET	(GMT+04:00) Near East Time
PLT	(GMT+05:00) Pakistan Lahore Time
IST	(GMT+05:30) Indian Time
BST	(GMT+06:00) Bangladesh Time
VST	(GMT+07:00) Vietnam Time
CTT	(GMT+08:00) Asia, Hong Kong S.A.R. of China
JST	(GMT+09:00) Japan Time: Tokyo
ACT	(GMT+09:30) Australian Central Time: Darwin
AET	(GMT+10:00) Australian Eastern Time: Sydney, Melbourne, Canberra
SST	(GMT+11:00) Solomon Time
AGT	(GMT-03:00) Argentina Time
BET	(GMT-03:00) Brazil Eastern Time: Sao Paulo, Buenos Aires
CNT	(GMT-03:30) Newfoundland Time: St. Johns
PRT	(GMT-04:00) Puerto Rico and U.S. Virgin Islands Time
EST	(GMT-05:00) Eastern Time: Ottawa, New York, Toronto, Montreal, 
Jamaica, Porto Acre
CST	(GMT-06:00) Central Time: Chicago, Cambridge Bay, Mexico City
MST	(GMT-07:00) Mountain Time: Edmonton, Yellowknife, Chihuahua
PST	(GMT-08:00) Pacific Time: Los Angeles, Tijuana, Vancouver
AST	(GMT-09:00) Alaska Time: Anchorage, Juneau, Nome, Yakutat
HST	(GMT-10:00) Hawaii Time: Honolulu, Tahiti
MIT	(GMT-11:00) Midway Islands Time: Midway, Apia, Niue, Pago Pago

Example 4

A customized time zone identifier may also be used, using the format 
GMT(+|-)HH:MM. For example, GMT-06:30 or GMT+02:00.
A more complete 
list of time zone idenfitiers (including longer form identifiers such 
as "Europe/Amsterdam") may be found in the "i18n_res.xml" file from 
the product's configuration folder.