4

My shapefile contains a attribute field called "created" (type QString) and this field holds the date and time of every feature in this format: "2022-03-04-13-28-26" (yyyy-mm-dd-hh-mm-ss)

I want to create a new attribute field that automatically converts this format into the following format: "dd.mm.yyyy hh:mm" (consider space between date and time and leave away the seconds)

How to do that?

I am using QGIS 3.16.6

2
  • Have you checked with the documentation, especially the format_date-page? If so, what is unclear to you?
    – Erik
    Commented Mar 5, 2022 at 12:28
  • Shapefiles, based on the dBase-Iii+ (-ish) format of the '80s, do not support a date type at higher than day resolution (amazingly, they were not subject to Y2K issues). Reformatting a string to new string is a trivial exercise in Python, requiring only str.split() and str.format(). Also the definition of "automatically" is unclear. What have you tried?
    – Vince
    Commented Mar 5, 2022 at 12:46

2 Answers 2

6

A Field Calculator expression tested in QGIS 3.16.16

array_to_string(array_reverse(string_to_array(left("created", 10), '-')),'.')+' '+substr("created", 12, 2)+':'+substr("created", 15, 2)

enter image description here

which works like below:

array_to_string(                            // This will change the array to string
     array_reverse(                         // Re-orders [2022,03,04] to [04,03,2022]
         string_to_array(                   // Changes "created" to an array
             left("created", 10), '-')),'.' // Takes the first 10 chars
              )+' '+                        // Inserts a space
substr("created", 12, 2)+':'+               // Extracts hh and adds ':'
substr("created", 15, 2)                    // Extracts mm
2
  • 1
    Kazuhito: Thank you very much for giving a simple solution (and explanation) without being arrogant or making me feel like a silly person. I am no GIS expert and don't want to become one at all but I unfortunately have some tasks to do in GIS for which I need help from kind persons like you! All the best for you and kindest regards... Hannes
    – Hannes
    Commented Mar 5, 2022 at 13:29
  • Hannes, Thanks so much, but please see the other answer provided by Matt. I think you should follow that one.
    – Kazuhito
    Commented Mar 6, 2022 at 0:34
4

While @Kazuhito's answer works perfectly, I just wanted to add an alternative solution using to_datetime to make a datetime object and format_date to convert it back to a formatted string:

format_date(
    to_datetime(original_datetime_string, 'yyyy-MM-dd-HH-mm-ss'),  -- make datetime of input string
    'dd.MM.yyyy HH:mm'                                             -- format the datetime object back to a string with elements rearranged
)

enter image description here

2
  • This is far better than my answer, neat and clear!
    – Kazuhito
    Commented Mar 6, 2022 at 0:32
  • thanks matt! very useful to me.
    – Hannes
    Commented Mar 6, 2022 at 7:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.