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:
- Record the route in the GPS with Komoot, con Google Fit, with Strava ...
- Export the route in *.GPX format using Share…
- Import the *.GPX file into QGIS 3.12
- Convert *.GPX file that cannot be edited to *.SHP (o *.GPKG)
- Open attribute table
- Go to field calculator
- Calculate a New Field of type “Date and Time”
- Define the formula: to_datetime(“_datetime_”) + to_interval(‘X hours’)
- In my case
to_interval() also accepts the words in English: month(s), day(s), hour(s) and their combinations‘1 day 2 hours’
- Reference Link:
List of functions in QGIS:
- Aggregation functions
- Matrix functions
- color features
- conditional functions
- conversion functions
- custom functions
- Date and time functions
- Fields and values
- File and path functions
- Fuzzy matching functions
- General functions
- geometry functions
- design features
- map layers
- map functions
- math functions
- operators
- processing functions
- plot functions
- Logging Functions and Attributes
- rope functions
- Variable functions
- recent features
| Function | Description |
|---|---|
| aggregate | Returns an aggregate value calculated using features from another layer |
| collect | Returns the multipart geometry of aggregated geometries from an expression |
| concatenate_unique | Returns all unique aggregated strings from a field |
| count_distinct | Returns the count of distinct values |
| iqr | Returns the calculated inter quartile range from a field or expression |
| max_length | Returns the maximum length of strings from a field or expression |
| mean | Returns the aggregate mean value from a field or expression |
| min_length | Returns the minimum length of strings from a field or expression |
| minority | Returns the aggregate minority of values (least commonly occurring |
| q3 | Returns the calculated third quartile from a field or expression |
| relation_aggregate | Returns an aggregate value calculated using all matching |
| sum | Returns the aggregate summed value from a field or expression |
| array | Returns an array containing all the values passed as parameter |
| array_append | Returns an array with the given value added at the end |
| array_contains | Returns true if an array contains the given value |
| array_filter | Returns an array with only the items for which an expression |
| array_first | Returns the first value of an array |
| array_get | Returns the Nth value (0 for the first one) of an array |
| array_intersect | Returns true if any element of array_1 exists in array_2 |
| array_length | Returns the number of elements of an array |
| array_remove_all | Returns an array with all the entries of the given value removed |
| array_reverse | Returns the given array with array values in reversed order |
| array_sort | Returns the provided array with its elements sorted |
| generate_series | Creates an array containing a sequence of numbers |
| string_to_array | Splits string into an array using supplied delimiter and |
| color_cmyk | Returns a string representation of a color based on its cyan, |
| color_grayscale_average | Applies a grayscale filter and returns a string |
| color_hsla | Returns a string representation of a color based on its hue, |
| color_hsva | Returns a string representation of a color based on its hue, |
| color_part | Returns a specific component from a color string, eg the red component or alpha component |
| color_rgba | Returns a string representation of a color based on its red, |
| darker | Returns a darker (or lighter) color string |
| project_color | Returns a color from the project’s color scheme |
| set_color_part | Sets a specific color component for a color string, eg the red component or alpha component |
| CASE WHEN … THEN … END | Evaluates an expression and returns a result if true. You can test multiple conditions |
| coalesce | Returns the first non-NULL value from the expression list |
| nullif(value1, value2) | Returns a null value if value1 equals value2 |
| to_date | Converts a string into a date object |
| to_dm | Converts a coordinate to degree, minute |
| to_int | Converts a string to integer number |
| to_real | Converts a string to a real number |
| to_time | Converts a string into a time object |
| age | Returns as an interval the difference between two dates or datetimes |
| day_of_week | Returns a number corresponding to the day of the week for a specified date or datetime |
| hour | Extracts the hour from a datetime or time, or the number of hours from an interval |
| month | Extracts the month part from a date or datetime, or the number of months from an interval |
| second | Extracts the second from a datetime or time, or the number of seconds from an interval |
| year | Extracts the year part from a date or datetime,or the number of years from an interval |
| base_file_name | Returns the base name of the file without the directory or file suffix. |
| file_name | Returns the name of a file (including the file extension), excluding the directory. |
| file_size | Returns the size (in bytes) of a file. |
| is_directory | Returns true if a path corresponds to a directory. |
| hamming_distance | Returns the number of characters at corresponding |
| longest_common_substring | Returns the longest common substring between two strings |
| env | Gets an environment variable and returns its content as a string. |
| is_layer_visible | Returns true if a specified layer is visible |
| was | Returns the value stored within a specified variable. See variable functions below |
| $area | Returns the area size of the current feature |
| $length | Returns the length of the current line feature |
| $x | Returns the X coordinate of the current feature |
| $y | Returns the Y coordinate of the current feature |
| angle_at_vertex | Returns the bisector angle (average angle) to the geometry |
| azimuth | Returns the north-based azimuth as the angle in radians measured |
| bounds | Returns a geometry which represents the bounding box of an input geometry. |
| bounds_width | Returns the width of the bounding box of a geometry. |
| buffer_by_m | Creates a buffer along a line geometry where the buffer diameter |
| closest_point | Returns the point on a geometry that is closest to a second geometry |
| combine | Returns the combination of two geometries |
| convex_hull | Returns the convex hull of a geometry (this represents the minimum |
| difference(a,b) | Returns a geometry that represents that part of geometry |
| distance | Returns the minimum distance (based on Spatial Reference System) |
| end_point | Returns the last node from a geometry (see alsoExtract specific vertices) |
| exterior_ring | Returns a line string representing the exterior ring of a polygon geometry, |
| flip_coordinates | Returns a copy of the geometry with the X and Y coordinates |
| geom_from_gml | Returns a geometry created from a GML representation of geometry |
| geom_to_wkt | Returns the well-known text (WKT) representation of the geometry without SRID metadata |
| geometry_n | Returns the nth geometry from a geometry collection, |
| inclination | Returns the inclination measured from the zenith (0) to the nadir (180) on point_a to point_b |
| intersection | Returns a geometry that represents the shared portion of two |
| intersects_bbox | Tests whether a geometry’s bounding box overlaps another geometry’s |
| length | Returns length of a line geometry feature (or length of a string) |
| line_interpolate_point | Returns the point interpolated by a specified |
| line_merge | Returns a (Multi-)LineString geometry, where any connected LineStrings |
| m | Returns the M value of a point geometry |
| make_ellipse | Creates 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_polygon | Creates a polygon geometry from an outer ring and optional series |
| make_regular_polygon | Creates a regular polygon |
| make_triangle | Creates a triangle polygon |
| nodes_to_points | Returns a multipoint geometry consisting of every node in |
| num_interior_rings | Returns the number of interior rings in a polygon or |
| num_rings | Returns the number of rings (including exterior rings) in a polygon |
| order_parts | Orders the parts of a MultiGeometry by a given criteria |
| overlaps | Tests whether a geometry overlaps another. Returns 1 (true) if |
| point_n | Returns a specific node from a geometry (see alsoExtract specific vertices) |
| pole_of_inaccessibility | Calculates the approximate pole of inaccessibility for |
| relate | Tests or returns the Dimensional Extended 9 Intersection Model (DE-9IM) |
| segments_to_lines | Returns a multi line geometry consisting of a line for |
| simplify | Simplifies a geometry by removing nodes using a distance based |
| single_sided_buffer | Returns a geometry formed by buffering out just one |
| start_point | Returns the first node from a geometry (see alsoExtract specific vertices) |
| tapered_buffer | Creates a buffer along a line geometry where the buffer |
| transform | Returns the geometry transformed from the source CRS to the |
| union | Returns 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_max | Returns the maximum X coordinate of a geometry. |
| y | Returns the Y coordinate of a point geometry, or the Y coordinate of |
| y_min | Returns the minimum Y coordinate of a geometry. |
| item_variables | Returns a map of variables from a layout item inside this print layout |
| decode_uri | Takes a layer and decodes the uri of the underlying data provider. |
| from_json | Loads a json-formatted string |
| json_to_map | Creates a map from a json-formatted string |
| map_akeys | Returns all the keys of a map as an array |
| map_concat | Returns a map containing all the entries of the given maps. |
| map_exist | Returns true if the given key exists in the map |
| map_insert | Returns a map with an added key/value |
| map_to_json | Merges map elements into a json-formatted string |
| abs | Returns the absolute value of a number |
| asin | Returns the inverse sine of a value in radians |
| atan2(y,x) | Returns the inverse tangent of Y/X by using the signs of the |
| ceil | Rounds a number upwards |
| cos | Returns the cosine of a value in radians |
| exp | Returns exponential of a value |
| inclination | Returns the inclination measured from the zenith (0) to the |
| log | Returns the value of the logarithm of the passed value and base |
| max | Returns the largest not null value in a set of values |
| pi | Returns the value of pi for calculations |
| rand | Returns the random integer within the range specified by the minimum |
| round | Rounds to number of decimal places |
| scale_linear | Transforms a given value from an input domain to an output |
| sqrt | Returns the square root of a value |
| a + b | Addition of two values (a plus b) |
| a * b | Multiplication of two values (a multiplied by b) |
| a % b | Remainder of division of a by b (eg, 7 % 2 = 1, or 2 fits into 7 three times with remainder 1) |
| a < b | Compares two values and evaluates to 1 if the left value is less than the right value (a is smaller than b) |
| a <> b | Compares two values and evaluates to 1 if they are not equal |
| a != b | a and b are not equal |
| a >= b | Compares 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 |
| LIKE | Returns 1 if the first parameter matches the supplied pattern |
| a IS b | Tests whether two values are identical. Returns 1 if a is the same as b |
| a AND b | Returns 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 |
| NULL | null value |
| a IS NOT NULL | a has a value |
| a NOT IN (value[,value]) | a is not below the values listed |
| parameter | Returns the value of a processing algorithm input parameter |
| raster_statistic | Returns statistics from a raster layer |
| $currentfeature | Returns the current feature being evaluated. This can be used |
| attribute | Returns the value of a specified attribute from a feature |
| get_feature | Returns the first feature of a layer matching a given attribute value |
| is_selected | Returns if a feature is selected |
| represent_value | Returns the configured representation value for a field value |
| uuid | Generates a Universally Unique Identifier (UUID) for each row. Each UUID is 38 characters long. |
| char | Returns the character associated with a unicode code |
| format | Formats a string using supplied arguments |
| format_number | Returns a number formatted with the locale separator for |
| length | Returns length of a string (or length of a line geometry feature) |
| lpad | Returns a string padded on the left to the specified width, using the fill character |
| regexp_replace | Returns a string with the supplied regular expression replaced |
| replace | Returns a string with the supplied string, array, or map of strings replaced |
| rpad | Returns a string padded on the right to the specified width, using the fill character |
| substr | Returns a part of a string |
| trim | Removes all leading and trailing white space (spaces, tabs, etc.) from a string |
| wordwrap | Returns a string wrapped to a maximum/minimum number of characters |
| algorithm_id | Returns the unique ID of an algorithm |
| atlas_featureid | Returns the current atlas feature ID |
| atlas_filename | Returns the current atlas file name |
| atlas_layerid | Returns the current atlas coverage layer ID |
| atlas_pagename | Returns the current atlas page name |
| canvas_cursor_point | Returns the last cursor position on the canvas in the |
| cluster_size | Returns the number of symbols contained within a cluster |
| current_geometry | Returns the geometry of the feature currently being edited |
| fullextent_maxy | Maximum y value from full canvas extent (including all layers) |
| fullextent_miny | Minimum x value from full canvas extent (including all layers) |
| geometry_part_num | Returns the current geometry part number for feature being rendered |
| geometry_point_num | Returns the current point number in the rendered geometry’s part |
| grid_number | Returns the current grid annotation value |
| item_uuid | Returns the layout item unique ID |
| layer_id | Returns the ID of current layer |
| layout_dpi | Returns the composition resolution (DPI) |
| layout_numpages | Returns the number of pages in the layout |
| layout_pageheight | Returns the active page height in the layout (in mm) |
| legend_column_count | Returns the number of columns in the legend |
| legend_filter_out_atlas | Indicates if the atlas is filtered out of the legend |
| legend_title | Returns the title of the legend |
| map_crs | Returns the Coordinate reference system of the current map |
| map_crs_definition | Returns the full definition of the Coordinate |
| map_crs_ellipsoid | Returns the acronym of the ellipsoid of the |
| map_crs_wkt | Returns the WKT definition of the Coordinate reference |
| map_extent_center | Returns the point feature at the center of the map |
| map_extent_width | Returns the current width of the map |
| map_layer_ids | Returns the list of map layer IDs visible in the map |
| map_rotation | Returns the current rotation of the map |
| map_units | Returns the units of map measurements |
| parent | Refers to the current feature in the parent layer, providing access to |
| project_area_units | Returns the area unit for the current project, used when |
| project_basename | Returns the basename of current project’s filename |
| project_crs | Returns the Coordinate reference system of the project |
| project_distance_units | Returns the distance unit for the current project, |
| project_filename | Returns the filename of the current project |
| project_home | Returns the home path of the current project |
| project_keywords | Returns the project keywords, taken from the project’s metadata |
| project_title | Returns the title of current project |
| qgis_os_name | Returns the current Operating system name, eg ‘windows’, ‘linux’ or ‘osx’ |
| qgis_release_name | Returns current QGIS release name |
| qgis_version | Returns current QGIS version string |
| snapping_results | Gives access to snapping results while digitizing a |
| symbol_angle | Returns the angle of the symbol used to render the feature |
| user_account_name | Returns the current user’s operating system account name |
| row_number | Stores the number of the current row |
| with_variable | Allows setting a variable for usage within an expression |