Show seconds in date format

[dimitrios]

Hey there, I am struggling to find the option to activate “seconds” on the date format.

f.e. in Airtable it shows me 2023-01-13 15:11 but when I do
DATETIME_FORMAT(timestampElementTriggered), “YYYY-MM-DD HH:mm:ss”) it generates me the correct seconds, since I fill the field from API with the ISO format.

Its important for us to find the elapsed time between to timestamps like
DATETIME_DIFF(timestampElementTriggered,sessionStartTime,‘seconds’)

However, in Seatable, I can’t find the right option, or formular. Only dateDif({sessionStartTime},{timestampElementTriggered}) will return the seconds. But since the Date field crops the seconds, I end up with 0 seconds for (2023-01-13 15:11:10, 2023-01-13 15:11:49) instead of 39 seconds.

Any idea how to solve this?

Best Dimitrios

[AkDk7]

Hi @dimitrios.

Yeah, I totally agree with you. Time handling in SeaTable is very improvable!

I tried it right now and I could only get it working by combining formulas.

For example:

• The columns “From Time” and “To Time” are of type duration.
• “From Total” and “To Total” are formulas: {From} + {From Time}
• “Seconds” calculates the difference in seconds, so I have to devide it by 60: dateDif({From Total}, {To Toal}) / 60
• And at the end you can combine it to “Dif plus seconds”: dateDif({From Total},{To Toal}) + {Seconds}

So you can play around with the date values and combine it like you want. But you will always end with several columns.

A function to format a iso datetime value with timezone would be a big benefit to the application. Maybe some day in future they will implement it

[AkDk7]

I think it didn’t work with different days, but it will work if it is the same day

[AkDk7]

I think I got the wrong solution

This should do the trick:

• Dif Dates: dateDif({From Date1},{To Date1})
• Dif Durations: {To Time2}-{From Time1}
• Duration Toal: value({Dif Dates}+{Dif Durations}, ‘duration’)

[dimitrios]

Thanks for the input!

F.e. the API is pushing it like this “sessionStartTime”: “2022-12-22T12:53:30.000Z”
And the date field is removing the seconds

I can’t split it before sending to have the correct Date & Time and then process them by formular.
And writing an automation to do this on every 10.000+ daily entries is also not possible.

best Dimitrios

[AkDk7]

Hm, I understand. What about import the datetime value into a string column and then convert it into date and time columns?

For the date column:
date(left({Time String}, 4), right(left({Time String}, 7), 2), right(left({Time String}, 10), 2))

And for the time column:
(left(right({Time String}, 13), 2) * (60 * 60)) +
(left(right({Time String}, 10), 2) * 60) +
left(right({Time String}, 7), 2)

You have to set the time column data type to duration with seconds.

[dimitrios]

did the exact thing also

your “Time” is a formular generating a string, which is not possible to get the dateDiff from

[AkDk7]

What formula did you put in Formula 3?

[dimitrios]

@AkDk7

You have to set the time column data type to duration with seconds.

this was important. was not aware that this is possible.

works now. what an effort for what is one simple formula in Airtable…

thanks for the examples and your help!

[AkDk7]

It is, unfortunately. But the team around SeaTable is improving the functionallity very well. And have to keep in mind it is still a very young software.