Using a date field with the concat formula


I have a table with a field that contains a date and time. If I add a column with a formula that just copies this field, it shows the exact same value: field('Start date and time')

However, If i use the concat() function, it reduces the time with two hours in my formula field: concat(field('Start date and time'),' test')

What is the reason for this behaviour? Has it something to do with timezones?


Hi @frederikdc

Pretty interesting scenario.

From my understanding Baserow breaks up the content of the concat formula into various parts (I believe Baserow uses Abstract Syntax trees in the backend to manage this structure) and calls each part’s appropiate to_text function

From their Github

" - All transformations of the formula that occur during the typing process
have been applied. For instance if you have the formula
concat(1, 'a', field('a date field')) during the typing process the
concat function will wrap all of its arguments in the appropriate to_text
function if they are of different types. This internal formula will
then look something like concat(totext(1), 'a', datetime_format(field( 'field_NN', 'YYYY-MM-DD'))

Here is the link

So I belive what may be happening is internally Baserow is calling the datetime_format (with some default values??) and your date is getting messed up as a result

You might try calling the concat function with the datetime_format explicitly mentioned (with the appropriate parameters to datetime_format). It might get you the results in the format you are wanting.

Hi @frederikdc,

yes, it has to do with timezones.
Datetimes are always saved in UTC in Baserow. The web frontend converts the datetime value in the user timezone unless the “Set timezone for all collaborators” has been set at a field level.
Anyway, as @Harsh correctly reported, at the moment the datetime_format formula cannot format dates into a different timezone than UTC.

In this MR: Resolve “Formula’s totext function doesn’t take into account timezones for date fields”
I’ve added a function to format dates in different timezones. If the review goes well, I think can be part of the next release.
Any feedback or comment to the MR is welcome.

Hey @frederikdc,

a new datetime_format_tz formula has been released today as reported here: Release 1.17: up to 3x faster // Baserow

The default behaviour of totext (the formula used to concat dates) is now looking for a timezone in the field settings, so if you set a timezone for all the collaborators in the field you’ll be able to format the date in the expected timezone.