How to create correct format inside a multivalue field?

[Flenwy]

Hello to all,

I have a multivalue field with a date and also a null value. In addition I have the problem that the format of the date is twisted from the original data.

Hope someone can help me 🙂

example:

outputdates (mv-field)
2023-07-29 12:06:20
28-07-2023 00:03:05
null

needed result:
outputdates (mv-field)
2023-07-29 12:06:20
2023-07-28 00:03:05

i tried splitting the field with mvexpand and then using strftime to adjust the format, unfortunately this is not working and i don't know exactly why.
Do I need to transform the "outputdates" as a time field to make strtime work again?

Am any help grateful.
Many greetings,
Flenwy

[Flenwy]

Thank you all for the great help!
All your examples helped me understand some mechanics more in detail.

The solutions from @bowesmana was on the point the think i though about but i did not know how to do it.

Kind regards to all of you!

[Miasm1]

Hello Flenwy,

Certainly! It looks like you're dealing with dates in two different formats and also null values in a multivalue field. Here's a step-by-step solution to help you address the problem:

Identify the Format: You need to identify the format of each date string and then apply the necessary transformation. In your case, you have two formats: 'YYYY-MM-DD hh:mm:ss' and 'DD-MM-YYYY hh:mm:ss'.

Handle Null Values: Since you also have null values, you need to check for them before applying any transformations.

Transform the Dates: Depending on the identified format, you can then convert the date to the required format.

Here's a snippet of code that should help you achieve your goal:

from datetime import datetime

outputdates = [
    "2023-07-29 12:06:20",
    "28-07-2023 00:03:05",
    None
]

result = []

for date in outputdates:
    if date is None:
        continue
    try:
        # If it's in 'YYYY-MM-DD' format
        parsed_date = datetime.strptime(date, '%Y-%m-%d %H:%M:%S')
    except ValueError:
        # If it's in 'DD-MM-YYYY' format
        parsed_date = datetime.strptime(date, '%d-%m-%Y %H:%M:%S')

    result.append(parsed_date.strftime('%Y-%m-%d %H:%M:%S'))

print(result) # Output: ['2023-07-29 12:06:20', '2023-07-28 00:03:05']

The code snippet above reads through the outputdates list, recognizes the two formats, and standardizes them into the 'YYYY-MM-DD hh:mm:ss' format, ignoring any null values.

Remember to adjust the code according to your specific environment or programming language if you are not using Python.

[bowesmana · Accepted solution]

You can do it like this

| makeresults
| fields - _time
| eval outputdates=split("2023-07-29 12:06:20,28-07-2023 00:03:05,", ",")
``` This uses any number of strftime variants to parse the dates - the succeeding one will prevail ```
| eval outputdates=mvmap(outputdates, strftime(max(
                         strptime(outputdates, "%F %T"), 
                         strptime(outputdates, "%d-%m-%Y %T")), 
                                               "%F %T"))

mvmap is used to iterate through the MV values. The first 3 lines set up your example

[richgalloway]

To convert textual dates from one format to another requires both strptime and strftime.

| eval outputdates=if(match(outputdates,"\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d"), outputdates, strftime(strptime(outputdates, "%d-%m-%Y %H:%M:%S"), "%Y-%m-%d %H:%M:%S"))

Because strptime will return null if the input doesn't match the specified format, we use if to test for the format we want to convert.