4

I have a point layer in a GeoPackage with this attribute table.

name  id             timestamp
   A   1   23:03:2022T23:30:00
   A   2
   A   3
 ...
   B   1   23:03:2022T23:30:00
   B   2
   B   3
 ...

What I need is populate the timestamp column adding 5 seconds to the previous value grouped by "name" column and ordered by "id".

Is there a way to do this using the Field Calculator? I am messing around field calculator but I'm stuck.

Improve this question
2
  • 2
    Does id start from 1 and continue sequentially for each name? Is this a shapefile (means timestamp is string) or a geopackage( or any format) accepting datetime field?
    – Kadir Şahbaz
    Commented Mar 25, 2022 at 11:10
  • Yes, it is a geopackage layer and the timestamp column datetime type. I already edited the question.
    – Raúl Casado
    Commented Mar 25, 2022 at 11:51

2 Answers 2

Reset to default
7

If your data are structured strictly like your example, you can do this (which returns a datetime object):

with_variable('start_time', to_datetime('23:03:2022T23:30:00', 'dd:MM:yyyyTHH:mm:ss'),

    CASE 
        WHEN 
            "id" = 1
        THEN 
            @start_time
        ELSE 
            @start_time + to_interval(to_string(("id"-1) * 5) || ' seconds')   -- add an interval of the elapsed seconds according to the `id` field
    END

)

enter image description here

To return a string:

with_variable('form', 'dd:MM:yyyyTHH:mm:ss',    -- make a variable of the format to avoid redundancy
    with_variable('start_time', to_datetime('23:03:2022T23:30:00', @form),

        CASE 
            WHEN 
                "id" = 1
            THEN
                format_date(@start_time, @form)
            ELSE
                format_date(@start_time + to_interval(to_string(("id"-1) * 5) || ' seconds'), @form)                
        END

    )
)

enter image description here

Improve this answer
4
  • 1
    Your solution is based on "time 23:03:2022T23:30:00". What if the first time values are different or unknown. I guess 23:03:2022T23:30:00 is just an example. Somehow, you need to group by name and get the starting time for each name.
    – Kadir Şahbaz
    Commented Mar 25, 2022 at 11:57
  • 1
    Hence my remark If your data are structured strictly like your example, you can do this
    – Matt
    Commented Mar 25, 2022 at 11:58
  • It would be great, if you could add a general solution for that kind of issues.
    – Kadir Şahbaz
    Commented Mar 25, 2022 at 11:59
  • 1
    @KadirŞahbaz I will take another look when I have some more time to add a more generalised expression
    – Matt
    Commented Mar 25, 2022 at 12:09
5

If the first time values are different or unknowns (as mentioned in the comment by @Kadir Şahbaz), use this expression:

array_max (
    array_agg( 
        timestamp,
        group_by:=name
    )
) + to_interval ( to_string (id*5-5)  ||  ' seconds' )

Explanation: with array_agg(), you collect all timestamp values from each name group in an array. Take the maximum value (if all other values are NULL as here, otherwise depending on what kind of dattime values you have in this field).

enter image description here

Improve this answer
2
  • 1
    Nice solution to make it dynamic. Though you will need to use to_string (id*5-5) else you add an extra 5 seconds. Where id=1, you actually want to add 0, id=2, add 5, etc.
    – Matt
    Commented Mar 25, 2022 at 14:17
  • 1
    Indeed,thanks for pointing me to that. Changed my soultion accordingly.
    – Babel
    Commented Mar 25, 2022 at 15:02

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.