Changing field type from string to time in QGIS

Question

I have a file with arrival and departure times for public transport in the format XX:XX:XX and field type Text(string). I need to be able to sort it from smallest to largest (i.e. earliest to latest time of the day) but it doesn't work correctly due to it being text string.

I have tried refactor fields but it returns an error e.g.

"Feature could not be written to Refactored_e65f66f8_6b29_40ae_9050_24d47d402fb1: Could not store attribute "arrival_time": Could not convert value "9:35:00" to target type "time" ".

For some reason out of my 7000 features only 5000 could be converted to the time field type and the rest got deleted.

I have also tried simply creating a new field with type Time (also integer, decimal etc) and copying the field "arrival_time" but it just returns empty.

The reason why I can't do it through Excel is that the file contains many more rows than Excel's maximum capacity.

Is there another way of converting the field type to one that is being able to be sorted properly?

Here is a sample file: https://drive.google.com/file/d/1sS80xe2meC-TkvQuKP8ZbWV94ZDhfXty/view?usp=sharing

• jpinilla: Have you tried to trim the original field? Sometimes, blank spaces are the cause of these headaches.
• Selma: No i havent. How do i do that?
• foxhq: refferring that page doc.qt.io/qt-5/qtime.html#fromString-1 . I used that formula to convert times to_time( replace( "departure_time", '24', '0'), 'h:mm:ss') I assume you have some anomalies in this data like 33:44:00, which cannot be converted to time data format. I'd suggest removing this stop times before parsing it
• til_b: How should 34:00:00 be represented in your final data?
• jpinilla: If there are (useless) blank spaces before or after the actual data (e.g. "03/01/2023 "), you can erase them with the function trim(name_of_the_field). You may use it for creating a new field or for updating the existing one.

Answer 1

There are two problems: Geopackage's field format and converting your string correctly to time (I propose using regular expressions). You can solve both problems using a single expression:

 with_variable(
 'time',
 regexp_matches ("arrival_time",'(\\d*):(\\d*):(\\d*)'),
 to_date (now()) -- or any other date
 + make_time( 
 to_int(@time[0])%24,
 to_int(@time[1]),
 to_int(@time[2])
 )
 + make_interval(days:=to_int(@time[0])/24)
 )

Explanation:

1. Geopackage has no time-only format, it is always datetime, so you have to add a day (like Nov 2 2023) to your time, otherwise you will get empty fields. Use e.g. to_date(now()) to create the current day's date or create any other date.

2. Use Regular expressions with regexp_matches() to isolate the digits (to avoid caring about spaces etc.), then use them as arguments with function make_time() to convert the string to time. To only get the hours smaller than 24, use reminder of division with %. So the expression to use for this part looks like this:

    with_variable(
    'time',
    regexp_matches ("arrival_time",'(\\d*):(\\d*):(\\d*)'),
    make_time( 
    to_int(@time[0])%24, -- creating value for hours
    to_int(@time[1]), -- creating value for minutes
    to_int(@time[2]) -- creating values for seconds
    )
    )
   

3. To correctly represent hour values larger then 24, add the following part to convert all multiples of 24 to days that will add to your date: make_interval(days:=to_int(@time[0])/24)

4. Now simply stick the three parts together with a simple + operator and you're done.

Result, using your data: enter image description here