QGIS 3.12 – How to convert a UTC time to local time in QGIS

After exporting a recorded route from my smartphone to the universal format *.GPX (standard format for GPS), the reported time is expressed in UTC time (Universal Time Coordinated) in order to be able to mount a report with the correct geolocation and time, I have looked for the way to convert the UTC time to the local time of Copenhagen, Denmark (CET+1) inside QGIS 3.12. To make it, I used the QGIS calculator accessible when the Attribute Table is opened.

Here is a summary of the steps to follow:

  1. Record the route in the GPS with Komoot, con Google Fit, with Strava ...
  2. Export the route in *.GPX format using Share…
  3. Import the *.GPX file into QGIS 3.12
  4. Convert *.GPX file that cannot be edited to *.SHP (o *.GPKG)
  5. Open attribute table
  6. Go to field calculator
  7. Calculate a New Field of type “Date and Time”
  8. Define the formula: to_datetime(“_datetime_”) + to_interval(‘X hours’)
  9. In my case

to_interval() also accepts the words in English: month(s), day(s), hour(s) and their combinations‘1 day 2 hours’

List of functions in QGIS:

  1. Aggregation functions
  2. Matrix functions
  3. color features
  4. conditional functions
  5. conversion functions
  6. custom functions
  7. Date and time functions
  8. Fields and values
  9. File and path functions
  10. Fuzzy matching functions
  11. General functions
  12. geometry functions
  13. design features
  14. map layers
  15. map functions
  16. math functions
  17. operators
  18. processing functions
  19. plot functions
  20. Logging Functions and Attributes
  21. rope functions
  22. Variable functions
  23. recent features
FunctionDescription
aggregateReturns an aggregate value calculated using features from another layer
collectReturns the multipart geometry of aggregated geometries from an expression
concatenate_uniqueReturns all unique aggregated strings from a field
count_distinctReturns the count of distinct values
iqrReturns the calculated inter quartile range from a field or expression
max_lengthReturns the maximum length of strings from a field or expression
meanReturns the aggregate mean value from a field or expression
min_lengthReturns the minimum length of strings from a field or expression
minorityReturns the aggregate minority of values (least commonly occurring
q3Returns the calculated third quartile from a field or expression
relation_aggregateReturns an aggregate value calculated using all matching
sumReturns the aggregate summed value from a field or expression
arrayReturns an array containing all the values passed as parameter
array_appendReturns an array with the given value added at the end
array_containsReturns true if an array contains the given value
array_filterReturns an array with only the items for which an expression
array_firstReturns the first value of an array
array_getReturns the Nth value (0 for the first one) of an array
array_intersectReturns true if any element of array_1 exists in array_2
array_lengthReturns the number of elements of an array
array_remove_allReturns an array with all the entries of the given value removed
array_reverseReturns the given array with array values in reversed order
array_sortReturns the provided array with its elements sorted
generate_seriesCreates an array containing a sequence of numbers
string_to_arraySplits string into an array using supplied delimiter and
color_cmykReturns a string representation of a color based on its cyan,
color_grayscale_averageApplies a grayscale filter and returns a string
color_hslaReturns a string representation of a color based on its hue,
color_hsvaReturns a string representation of a color based on its hue,
color_partReturns a specific component from a color string, eg the red component or alpha component
color_rgbaReturns a string representation of a color based on its red,
darkerReturns a darker (or lighter) color string
project_colorReturns a color from the project’s color scheme
set_color_partSets a specific color component for a color string, eg the red component or alpha component
CASE WHEN … THEN … ENDEvaluates an expression and returns a result if true. You can test multiple conditions
coalesceReturns the first non-NULL value from the expression list
nullif(value1, value2)Returns a null value if value1 equals value2
to_dateConverts a string into a date object
to_dmConverts a coordinate to degree, minute
to_intConverts a string to integer number
to_realConverts a string to a real number
to_timeConverts a string into a time object
ageReturns as an interval the difference between two dates or datetimes
day_of_weekReturns a number corresponding to the day of the week for a specified date or datetime
hourExtracts the hour from a datetime or time, or the number of hours from an interval
monthExtracts the month part from a date or datetime, or the number of months from an interval
secondExtracts the second from a datetime or time, or the number of seconds from an interval
yearExtracts the year part from a date or datetime,or the number of years from an interval
base_file_nameReturns the base name of the file without the directory or file suffix.
file_nameReturns the name of a file (including the file extension), excluding the directory.
file_sizeReturns the size (in bytes) of a file.
is_directoryReturns true if a path corresponds to a directory.
hamming_distanceReturns the number of characters at corresponding
longest_common_substringReturns the longest common substring between two strings
envGets an environment variable and returns its content as a string.
is_layer_visibleReturns true if a specified layer is visible
wasReturns the value stored within a specified variable. See variable functions below
$areaReturns the area size of the current feature
$lengthReturns the length of the current line feature
$xReturns the X coordinate of the current feature
$yReturns the Y coordinate of the current feature
angle_at_vertexReturns the bisector angle (average angle) to the geometry
azimuthReturns the north-based azimuth as the angle in radians measured
boundsReturns a geometry which represents the bounding box of an input geometry.
bounds_widthReturns the width of the bounding box of a geometry.
buffer_by_mCreates a buffer along a line geometry where the buffer diameter
closest_pointReturns the point on a geometry that is closest to a second geometry
combineReturns the combination of two geometries
convex_hullReturns the convex hull of a geometry (this represents the minimum
difference(a,b)Returns a geometry that represents that part of geometry
distanceReturns the minimum distance (based on Spatial Reference System)
end_pointReturns the last node from a geometry (see alsoExtract specific vertices)
exterior_ringReturns a line string representing the exterior ring of a polygon geometry,
flip_coordinatesReturns a copy of the geometry with the X and Y coordinates
geom_from_gmlReturns a geometry created from a GML representation of geometry
geom_to_wktReturns the well-known text (WKT) representation of the geometry without SRID metadata
geometry_nReturns the nth geometry from a geometry collection,
inclinationReturns the inclination measured from the zenith (0) to the nadir (180) on point_a to point_b
intersectionReturns a geometry that represents the shared portion of two
intersects_bboxTests whether a geometry’s bounding box overlaps another geometry’s
lengthReturns length of a line geometry feature (or length of a string)
line_interpolate_pointReturns the point interpolated by a specified
line_mergeReturns a (Multi-)LineString geometry, where any connected LineStrings
mReturns the M value of a point geometry
make_ellipseCreates an elliptical geometry based on center point, axes and azimuth
make_point(x,y,z,m)Returns a point geometry from X and Y (and optional Z or M) values
make_polygonCreates a polygon geometry from an outer ring and optional series
make_regular_polygonCreates a regular polygon
make_triangleCreates a triangle polygon
nodes_to_pointsReturns a multipoint geometry consisting of every node in
num_interior_ringsReturns the number of interior rings in a polygon or
num_ringsReturns the number of rings (including exterior rings) in a polygon
order_partsOrders the parts of a MultiGeometry by a given criteria
overlapsTests whether a geometry overlaps another. Returns 1 (true) if
point_nReturns a specific node from a geometry (see alsoExtract specific vertices)
pole_of_inaccessibilityCalculates the approximate pole of inaccessibility for
relateTests or returns the Dimensional Extended 9 Intersection Model (DE-9IM)
segments_to_linesReturns a multi line geometry consisting of a line for
simplifySimplifies a geometry by removing nodes using a distance based
single_sided_bufferReturns a geometry formed by buffering out just one
start_pointReturns the first node from a geometry (see alsoExtract specific vertices)
tapered_bufferCreates a buffer along a line geometry where the buffer
transformReturns the geometry transformed from the source CRS to the
unionReturns a geometry that represents the point set union of the geometries
within (a,b)Tests whether a geometry is within another. Returns 1 (true)
x_maxReturns the maximum X coordinate of a geometry.
yReturns the Y coordinate of a point geometry, or the Y coordinate of
y_minReturns the minimum Y coordinate of a geometry.
item_variablesReturns a map of variables from a layout item inside this print layout
decode_uriTakes a layer and decodes the uri of the underlying data provider.
from_jsonLoads a json-formatted string
json_to_mapCreates a map from a json-formatted string
map_akeysReturns all the keys of a map as an array
map_concatReturns a map containing all the entries of the given maps.
map_existReturns true if the given key exists in the map
map_insertReturns a map with an added key/value
map_to_jsonMerges map elements into a json-formatted string
absReturns the absolute value of a number
asinReturns the inverse sine of a value in radians
atan2(y,x)Returns the inverse tangent of Y/X by using the signs of the
ceilRounds a number upwards
cosReturns the cosine of a value in radians
expReturns exponential of a value
inclinationReturns the inclination measured from the zenith (0) to the
logReturns the value of the logarithm of the passed value and base
maxReturns the largest not null value in a set of values
piReturns the value of pi for calculations
randReturns the random integer within the range specified by the minimum
roundRounds to number of decimal places
scale_linearTransforms a given value from an input domain to an output
sqrtReturns the square root of a value
a + bAddition of two values (a plus b)
a * bMultiplication of two values (a multiplied by b)
a % bRemainder of division of a by b (eg, 7 % 2 = 1, or 2 fits into 7 three times with remainder 1)
a < bCompares two values and evaluates to 1 if the left value is less than the right value (a is smaller than b)
a <> bCompares two values and evaluates to 1 if they are not equal
a != ba and b are not equal
a >= bCompares two values and evaluates to 1 if the left value is greater than or equal to the right value
||Joins two values together into a string. If one of the values is NULL the result will be NULL
LIKEReturns 1 if the first parameter matches the supplied pattern
a IS bTests whether two values are identical. Returns 1 if a is the same as b
a AND bReturns 1 when conditions a and b are true
“Column_name”Value of the fieldColumn_name, take care to not be confused with simple quote, see below
NULLnull value
a IS NOT NULLa has a value
a NOT IN (value[,value])a is not below the values listed
parameterReturns the value of a processing algorithm input parameter
raster_statisticReturns statistics from a raster layer
$currentfeatureReturns the current feature being evaluated. This can be used
attributeReturns the value of a specified attribute from a feature
get_featureReturns the first feature of a layer matching a given attribute value
is_selectedReturns if a feature is selected
represent_valueReturns the configured representation value for a field value
uuidGenerates a Universally Unique Identifier (UUID) for each row. Each UUID is 38 characters long.
charReturns the character associated with a unicode code
formatFormats a string using supplied arguments
format_numberReturns a number formatted with the locale separator for
lengthReturns length of a string (or length of a line geometry feature)
lpadReturns a string padded on the left to the specified width, using the fill character
regexp_replaceReturns a string with the supplied regular expression replaced
replaceReturns a string with the supplied string, array, or map of strings replaced
rpadReturns a string padded on the right to the specified width, using the fill character
substrReturns a part of a string
trimRemoves all leading and trailing white space (spaces, tabs, etc.) from a string
wordwrapReturns a string wrapped to a maximum/minimum number of characters
algorithm_idReturns the unique ID of an algorithm
atlas_featureidReturns the current atlas feature ID
atlas_filenameReturns the current atlas file name
atlas_layeridReturns the current atlas coverage layer ID
atlas_pagenameReturns the current atlas page name
canvas_cursor_pointReturns the last cursor position on the canvas in the
cluster_sizeReturns the number of symbols contained within a cluster
current_geometryReturns the geometry of the feature currently being edited
fullextent_maxyMaximum y value from full canvas extent (including all layers)
fullextent_minyMinimum x value from full canvas extent (including all layers)
geometry_part_numReturns the current geometry part number for feature being rendered
geometry_point_numReturns the current point number in the rendered geometry’s part
grid_numberReturns the current grid annotation value
item_uuidReturns the layout item unique ID
layer_idReturns the ID of current layer
layout_dpiReturns the composition resolution (DPI)
layout_numpagesReturns the number of pages in the layout
layout_pageheightReturns the active page height in the layout (in mm)
legend_column_countReturns the number of columns in the legend
legend_filter_out_atlasIndicates if the atlas is filtered out of the legend
legend_titleReturns the title of the legend
map_crsReturns the Coordinate reference system of the current map
map_crs_definitionReturns the full definition of the Coordinate
map_crs_ellipsoidReturns the acronym of the ellipsoid of the
map_crs_wktReturns the WKT definition of the Coordinate reference
map_extent_centerReturns the point feature at the center of the map
map_extent_widthReturns the current width of the map
map_layer_idsReturns the list of map layer IDs visible in the map
map_rotationReturns the current rotation of the map
map_unitsReturns the units of map measurements
parentRefers to the current feature in the parent layer, providing access to
project_area_unitsReturns the area unit for the current project, used when
project_basenameReturns the basename of current project’s filename
project_crsReturns the Coordinate reference system of the project
project_distance_unitsReturns the distance unit for the current project,
project_filenameReturns the filename of the current project
project_homeReturns the home path of the current project
project_keywordsReturns the project keywords, taken from the project’s metadata
project_titleReturns the title of current project
qgis_os_nameReturns the current Operating system name, eg ‘windows’, ‘linux’ or ‘osx’
qgis_release_nameReturns current QGIS release name
qgis_versionReturns current QGIS version string
snapping_resultsGives access to snapping results while digitizing a
symbol_angleReturns the angle of the symbol used to render the feature
user_account_nameReturns the current user’s operating system account name
row_numberStores the number of the current row
with_variableAllows setting a variable for usage within an expression

Without category permalink

Leave a Reply