Using a date field with the concat formula

frederikdc · May 8, 2023, 7:54am

Hi,

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?

Regards
Frederik

Harsh · May 8, 2023, 11:12am

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

github.com

bram2w/baserow/blob/39f0d137de359c383da4af9e9405657fff5f2661/backend/src/baserow/contrib/database/formula/handler.py#L284

      
        
            
            
    :param formula_field: An instance of a formula field whose type stored in the
                    database should be looked up and returned.
                :return: Returns a populated instance of a BaserowFormulaType object which
                    represents the stored type for the formula field.
                """
            
            
    formula_type = _lookup_formula_type_from_string(formula_field.formula_type)
                return formula_type.construct_type_from_formula_field(formula_field)
            
            
@classmethod
            def get_typed_internal_expression_from_field(
                cls, formula_field
            ) -> BaserowExpression[BaserowFormulaType]:
                """
                Returns a typed expression which can be directly translated to a Django
                Expression using the two baserow_expression_to_{update,insert}_django_expression
                methods above.
            
            
    The internal typed expression differs from formula_field.formula in a number of
                ways:

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.

davide · May 8, 2023, 1:37pm

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.

davide · May 16, 2023, 1:49pm

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.