Converts a UTC or GMT timestamp to local time as a dual value.
Syntax:
ConvertToLocalTime(timestamp [, place [, ignore_dst=false]])
Return data type: dual
| Argument | Description |
|---|---|
| timestamp | The date to evaluate as a timestamp or expression resolving to a timestamp, to convert, for example '2012-10-12'. |
| place |
A place or timezone from the table of places and timezones below. Alternatively, you can use GMT or UTC to define the local time. The following values and time offset ranges are valid:
Information noteIf you use a DST offset (that is, you specify an ignore_dst argument value evaluating to False), you must specify a place, rather than a GMT offset, in the place argument. This is because adjusting for Daylight Saving Time requires latitudinal information in addition to the longitudinal information provided by a GMT offset. For information, see Using GMT offsets in combination with DST.
Information noteYou can only use standard time offsets. It's not possible to use an arbitrary time offset, for example, GMT-04:27.
|
| ignore_dst |
If this argument evaluates to True, DST (daylight saving time) is ignored. Valid argument values evaluating to True include -1 and True(). If this argument evaluates to False, the timestamp is adjusted for daylight saving time. Valid argument values evaluating to False include 0 and False(). If the ignore_dst argument value is invalid, the function evaluates the expression as if the ignore_dst value evaluates to True. If the ignore_dst argument value is not specified, the function evaluates the expression as if the ignore_dst value evaluates to False. |
The resulting time is adjusted for daylight-saving time, unless ignore_dst is set to True.
Places and time zones
The ConvertToLocalTime function retrieves information on places and time zones around the world from the Windows registry. This means that for the function to work, the name of the place you want to use must match the corresponding name in the Windows registry.
The exact name with which a place is registered in the Windows registry may also change according to the language settings of your Windows installation. This is because the names of certain cities and countries are spelled differently in different languages.
There is a full list of places and time zones in the windows registry in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones
Alternatively, you can find the list of time zones and places in Windows Control Panel. Open Control Panel, click on Date and Time settings and then Change time zone. You can see the full list of cities or countries associated with each time zone from the drop-down menu.
| A-C | D-K | L-R | S-Z |
|---|---|---|---|
| Abu Dhabi | Darwin | La Paz | Samoa |
| Adelaide | Dhaka | Lima | Santiago |
| Alaska | Eastern Time (US & Canada) | Lisbon | Sapporo |
| Amsterdam | Edinburgh | Ljubljana | Sarajevo |
| Arizona | Ekaterinburg | London | Saskatchewan |
| Astana | Fiji | Madrid | Seoul |
| Athens | Georgetown | Magadan | Singapore |
| Atlantic Time (Canada) | Greenland | Mazatlan | Skopje |
| Auckland | Greenwich Mean Time : Dublin | Melbourne | Sofia |
| Azores | Guadalajara | Mexico City | Solomon Is. |
| Baghdad | Guam | Mid-Atlantic | Sri Jayawardenepura |
| Baku | Hanoi | Minsk | St. Petersburg |
| Bangkok | Harare | Monrovia | Stockholm |
| Beijing | Hawaii | Monterrey | Sydney |
| Belgrade | Helsinki | Moscow | Taipei |
| Berlin | Hobart | Mountain Time (US & Canada) | Tallinn |
| Bern | Hong Kong | Mumbai | Tashkent |
| Bogota | Indiana (East) | Muscat | Tbilisi |
| Brasilia | International Date Line West | Nairobi | Tehran |
| Bratislava | Irkutsk | New Caledonia | Tokyo |
| Brisbane | Islamabad | New Delhi | Urumqi |
| Brussels | Istanbul | Newfoundland | Warsaw |
| Bucharest | Jakarta | Novosibirsk | Wellington |
| Budapest | Jerusalem | Nuku'alofa | West Central Africa |
| Buenos Aires | Kabul | Osaka | Vienna |
| Cairo | Kamchatka | Pacific Time (US & Canada) | Vilnius |
| Canberra | Karachi | Paris | Vladivostok |
| Cape Verde Is. | Kathmandu | Perth | Volgograd |
| Caracas | Kolkata | Port Moresby | Yakutsk |
| Casablanca | Krasnoyarsk | Prague | Yerevan |
| Central America | Kuala Lumpur | Pretoria | Zagreb |
| Central Time (US & Canada) | Kuwait | Quito | - |
| Chennai | Kyiv | Riga | - |
| Chihuahua | - | Riyadh | - |
| Chongqing | - | Rome | - |
| Copenhagen | - | - | - |
| Example | Result |
|---|---|
|
ConvertToLocalTime('2023-08-14 08:39:47','Paris') |
Returns '2023-08-14 10:39:47' and the corresponding internal timestamp representation. |
| ConvertToLocalTime(UTC(), 'Stockholm') | Returns the time for Stockholm, adjusting for daylight saving time. |
| ConvertToLocalTime(UTC(), 'Stockholm', -1) | Returns the time for Stockholm, without daylight saving time adjustment. |
| ConvertToLocalTime(UTC(), 'GMT-05:00') | Returns the time for the North American east coast, for example, New York. No adjustment is made for daylight saving time because a GMT offset, rather than a place, is specified. |
| ConvertToLocalTime(UTC(), 'New York', -1) | Returns the time for the North American east coast (New York), without daylight saving time adjustment. |
| ConvertToLocalTime(UTC(), 'New York', True()) | Returns the time for the North American east coast (New York), without daylight saving time adjustment. |
| ConvertToLocalTime(UTC(), 'New York', 0) | Returns the time for the North American east coast (New York), adjusting for daylight saving time. |
| ConvertToLocalTime(UTC(), 'New York', False()) | Returns the time for the North American east coast (New York), adjusting for daylight saving time. |
Using GMT offsets in combination with DST
Following the implementation of International Components for Unicode (ICU) libraries in QlikView November 2018, the use of GMT (Greenwich Mean Time) offsets in combination with DST (Daylight Saving Time) requires additional latitudinal information.
GMT is a longitudinal (east-west) offset, whereas DST is a latitudinal (north-south) offset. For example, Helsinki (Finland) and Johannesburg (South Africa) share the same GMT+02:00 offset, but they do not share the same DST offset. This means that, further to the GMT offset, any DST offset requires information on the latitudinal position of the local time zone (geographical time zone input) in order to have full information about local DST conditions.
