Convert 0d HH:mm:ss into dd:MM:yyyy HH:mm:ss format in QGIS 3.24

Question

As you can see in this image, I have a point shapefile which contains a time dataset in the
0d HH:mm:ss format (left side image), I want to convert in into dd:mm:yyyy HH:mm:ss format (right side image).

Is there any way to do it in QGIS 3.24?

Answer 1

Pretty complicated, but it seems to work. I use datetime module to create a start date and then add the days, hours, minutes and seconds from your time field to it.

Make sure to check the result very carefully because the string splitting below will be unreliable/wont work if your current time field formatting change in any way.

import datetime as dt
layer = QgsProject.instance().mapLayersByName('test')[0]

fieldindex = layer.fields().indexFromName('newtime') #A string field named newtime needs to be added before executing the code
start_date = dt.datetime.strptime('2022:02:25', '%Y:%m:%d') #Create a datetime object

attrmap = {} # A dictionary to store new values in
for f in layer.getFeatures(): #For each row/feature
    rowval = f['time'] #Fetch the value of time field
    days = int(rowval.split('d')[0]) #Extract the day
    hours = int(rowval.split('d')[1].split(':')[0]) #hour
    minutes = int(rowval.split('d')[1].split(':')[1]) #minute
    seconds = int(rowval.split('d')[1].split(':')[2]) #second
    end_date = start_date + dt.timedelta(days=days, hours=hours, minutes=minutes, seconds=seconds) #Add these to start date
    end_date = dt.datetime.strftime(end_date, '%d.%m.%Y %H:%M:%S') #Convert to string
    attrmap[f.id()] = {fieldindex:end_date}

layer.dataProvider().changeAttributeValues(attrmap) #Make the changes

Answer 2

So you have a string which contains the text 0d followed by a valid time. You want to replace the 0d with a valid date and format it.

There are multiple options to achieve it using the field calculator, including :

1. read the time using a custom format, add a date, format the output:

The trick here is to escape the text 0d using single quotes, which in turn needs to be escaped using \

format_date( 
  to_time( '0d 00:45:38','\'0d\' HH:mm:ss','en') + to_date('2022-02-25','yyyy-MM-dd','en'),
  'dd:MM:yyyy HH:mm:ss','en')

2. Replace the 0d with the static date, read as datetime and format the output

format_date( 
  to_datetime( replace('0d 00:45:38','0d','2022-02-25'),'yyyy-MM-dd HH:mm:ss','en') ,
  'dd:MM:yyyy HH:mm:ss','en')

PS: replace '0d 00:45:38' by your field name