Formatting UTC Date

[Lug-gl]

Heyho,
I have already seen a few posts from others who also have problems with date formatting in formulas, just like me.

I have the following example that shows how awkward it can be to have a DateTime formula field in the CET time zone with summer winter time formatting and German month formatting.

I thought I’d share this here and am curious to hear any comments, improvements, or discussions about it. Looking forward to your thoughts!

If(
  Month([Test Date]) = 1,
  "Jan",
  If(
    Month([Test Date]) = 2,
    "Feb",
    If(
      Month([Test Date]) = 3,
      "Mär",
      If(
        Month([Test Date]) = 4,
        "Apr",
        If(
          Month([Test Date]) = 5,
          "Mai",
          If(
            Month([Test Date]) = 6,
            "Jun",
            If(
              Month([Test Date]) = 7,
              "Jul",
              If(
                Month([Test Date]) = 8,
                "Aug",
                If(
                  Month([Test Date]) = 9,
                  "Sep",
                  If(
                    Month([Test Date]) = 10,
                    "Okt",
                    If(
                      Month([Test Date]) = 11,
                      "Nov",
                      If(Month([Test Date]) = 12, "Dez", "Unknown")
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  )
) +
  " " +
  ToText(Day([Test Date])) +
  ", " +
  ToText(Year([Test Date])) +
  " " +
  If(
    If(
      Today() >= DateTime(Year(Today()), 3, 26, 2, 0, 0) and
        Today() < DateTime(Year(Today()), 10, 29, 3, 0, 0),
      Hour([Test Date] + Hours(2)),
      Hour([Test Date] + Hours(1))
    ) < 10,
    "0",
    ""
  ) +
  ToText(
    If(
      Today() >= DateTime(Year(Today()), 3, 26, 2, 0, 0) and
        Today() < DateTime(Year(Today()), 10, 29, 3, 0, 0),
      Hour([Test Date] + Hours(2)),
      Hour([Test Date] + Hours(1))
    )
  ) +
  If(
    Minute([Test Date]) != 0,
    ":" +
      If(
        Minute([Test Date]) < 10,
        "0" + ToText(Minute([Test Date])),
        ToText(Minute([Test Date]))
      ),
    ""
  ) +
  " Uhr"

output example: “Feb 10, 2024 10 Uhr”

[njyo]

Why does ToText(Today()) not work for you?
Outputs: 2024-02-10 10:07:35
… and if you need, you use Left() to get a substring.

But, I agree, having the standard C formatting option of %MMM %dd, %YYYY %HH Uhr and possibly a locale of DE-li or whatever, would be neat.

[Lug-gl]

Heyho,

unfortunately, the conversion from UTC to CET doesn’t work as intended. Currently, it is only possible for dateTime fields, as they include the timezone. But formula fields with string output does not use the current user timezone.

This means that there would always be one hour less displayed at the moment. Depending on the daylight saving time changes in Europe, I need to check and set it accordingly (as in the formula).

The remaining formatting is more of a goody to demonstrate how I sometimes do it to display a nicer formatting.

[Chr1sG]

Is this any use?

Timezone offset calculation

I have dug into timezones a little, and concluded that it is possible to write a formula that will convert a DateTime value to a Date field based on a specific timezone, taking into account daylight saving. In the case of Europe, the formula would be as follows: DateTimeField + Hours( If( DateTimeField > Date(Year(DateTimeField), 4, 1) - Days( If( WeekDayName(Date(Year(DateTimeField), 4, 1)) = "Monday", 1, If( …

Note: If a date field is typecast to a datetime field, it takes the time as midnight (UTC).

[Lug-gl]

Really cool to see a global approach to timezone calculation.

If I got that correctly depending on which weekday it is you can decide which timezone the user is in and set the specific date for this timezone.

My example is just for Europe, but just a bit more precise because of the time calculation

[njyo]

Ah, the daylight savings time thing.

Sounds to me like an extension of the World space would be helpful. I’ve been using the currency conversion a number of times successfully. I think a similar approach would work for offsets.

[Chr1sG]

@Lug-gl

If I got that correctly depending on which weekday it is you can decide which timezone the user is in and set the specific date for this timezone.

Not quite.
The formula needs to calculate the correct dates in the year when summer time starts and ends, and the day of the week calculation is used for this.
As far as I can tell, your formula will not work for every year, since it assumes that summer time always starts and ends on 26/03 and 29/10.

@Lug-gl

My example is just for Europe, but just a bit more precise because of the time calculation

Not sure what you mean by this. My formula will correct a datetime field, even taking into account the time of the night when summer time changes occur.

[Chr1sG]

@njyo

Sounds to me like an extension of the World space would be helpful. I’ve been using the currency conversion a number of times successfully. I think a similar approach would work for offsets.

You would think so, but actually it’s quite complicated. There is not a simple relationship between timezone and country. We would probably need to extend the world space to introduce zones within countries if we wanted timezones to be usable

[Lug-gl]

@Chr1sG

Not quite.
The formula needs to calculate the correct dates in the year when summer time starts and ends, and the day of the week calculation is used for this.
As far as I can tell, your formula will not work for every year, since it assumes that summer time always starts and ends on 26/03 and 29/10.

ahh now I got it, this is really tricky Didn’t thought about this.

[Lug-gl]

ok now it’s getting really creazy. I updated my original version with your timezone calculation, so that I’ve got the whished formatting too.

This is a lot to think and a lot of code for such date calculations. In my opinion and probably yours too would be a default timezone cool, a possibility to set variables in formulas and a replace all functionality to replace all field names in a formula with a new naming like: “[Test Field]” => “[New Field]”

my updated code snipped:

"Termin: " +
  If(
    Day([Test Date]) > 9,
    ToText(Day([Test Date])),
    "0" + ToText(Day([Test Date]))
  ) +
  " " +
  If(
    Month([Test Date]) = 1,
    "Jan",
    If(
      Month([Test Date]) = 2,
      "Feb",
      If(
        Month([Test Date]) = 3,
        "Mär",
        If(
          Month([Test Date]) = 4,
          "Apr",
          If(
            Month([Test Date]) = 5,
            "Mai",
            If(
              Month([Test Date]) = 6,
              "Jun",
              If(
                Month([Test Date]) = 7,
                "Jul",
                If(
                  Month([Test Date]) = 8,
                  "Aug",
                  If(
                    Month([Test Date]) = 9,
                    "Sep",
                    If(
                      Month([Test Date]) = 10,
                      "Okt",
                      If(
                        Month([Test Date]) = 11,
                        "Nov",
                        If(Month([Test Date]) = 12, "Dez", "Unknown")
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  ) +
  " " +
  ToText(Year([Test Date])) +
  " - " +
  If(
    Hour(
      [Test Date] +
        Hours(
          If(
            [Test Date] >
              Date(Year([Test Date]), 4, 1) -
                Days(
                  If(
                    WeekDayName(Date(Year([Test Date]), 4, 1)) = "Monday",
                    1,
                    If(
                      WeekDayName(Date(Year([Test Date]), 4, 1)) =
                        "Tuesday",
                      2,
                      If(
                        WeekDayName(Date(Year([Test Date]), 4, 1)) =
                          "Wednesday",
                        3,
                        If(
                          WeekDayName(Date(Year([Test Date]), 4, 1)) =
                            "Thursday",
                          4,
                          If(
                            WeekDayName(Date(Year([Test Date]), 4, 1)) =
                              "Friday",
                            5,
                            If(
                              WeekDayName(Date(Year([Test Date]), 4, 1)) =
                                "Saturday",
                              6,
                              7
                            )
                          )
                        )
                      )
                    )
                  )
                ) +
                Hours(1) and
              [Test Date] <
                Date(Year([Test Date]), 11, 1) -
                  Days(
                    If(
                      WeekDayName(Date(Year([Test Date]), 11, 1)) =
                        "Monday",
                      1,
                      If(
                        WeekDayName(Date(Year([Test Date]), 11, 1)) =
                          "Tuesday",
                        2,
                        If(
                          WeekDayName(Date(Year([Test Date]), 11, 1)) =
                            "Wednesday",
                          3,
                          If(
                            WeekDayName(Date(Year([Test Date]), 11, 1)) =
                              "Thursday",
                            4,
                            If(
                              WeekDayName(Date(Year([Test Date]), 11, 1)) =
                                "Friday",
                              5,
                              If(
                                WeekDayName(
                                  Date(Year([Test Date]), 11, 1)
                                ) = "Saturday",
                                6,
                                7
                              )
                            )
                          )
                        )
                      )
                    )
                  ) +
                  Hours(1),
            2,
            1
          )
        )
    ) < 10,
    "0",
    ""
  ) +
  ToText(
    Hour(
      [Test Date] +
        Hours(
          If(
            [Test Date] >
              Date(Year([Test Date]), 4, 1) -
                Days(
                  If(
                    WeekDayName(Date(Year([Test Date]), 4, 1)) = "Monday",
                    1,
                    If(
                      WeekDayName(Date(Year([Test Date]), 4, 1)) =
                        "Tuesday",
                      2,
                      If(
                        WeekDayName(Date(Year([Test Date]), 4, 1)) =
                          "Wednesday",
                        3,
                        If(
                          WeekDayName(Date(Year([Test Date]), 4, 1)) =
                            "Thursday",
                          4,
                          If(
                            WeekDayName(Date(Year([Test Date]), 4, 1)) =
                              "Friday",
                            5,
                            If(
                              WeekDayName(Date(Year([Test Date]), 4, 1)) =
                                "Saturday",
                              6,
                              7
                            )
                          )
                        )
                      )
                    )
                  )
                ) +
                Hours(1) and
              [Test Date] <
                Date(Year([Test Date]), 11, 1) -
                  Days(
                    If(
                      WeekDayName(Date(Year([Test Date]), 11, 1)) =
                        "Monday",
                      1,
                      If(
                        WeekDayName(Date(Year([Test Date]), 11, 1)) =
                          "Tuesday",
                        2,
                        If(
                          WeekDayName(Date(Year([Test Date]), 11, 1)) =
                            "Wednesday",
                          3,
                          If(
                            WeekDayName(Date(Year([Test Date]), 11, 1)) =
                              "Thursday",
                            4,
                            If(
                              WeekDayName(Date(Year([Test Date]), 11, 1)) =
                                "Friday",
                              5,
                              If(
                                WeekDayName(
                                  Date(Year([Test Date]), 11, 1)
                                ) = "Saturday",
                                6,
                                7
                              )
                            )
                          )
                        )
                      )
                    )
                  ) +
                  Hours(1),
            2,
            1
          )
        )
    )
  ) +
  If(
    Minute([Test Date]) != 0,
    ":" +
      If(
        Minute([Test Date]) < 10,
        "0" + ToText(Minute([Test Date])),
        ToText(Minute([Test Date]))
      ),
    ""
  ) +
  " Uhr"

[Chr1sG]

@Lug-gl

In my opinion and probably yours too would be a default timezone cool, a possibility to set variables in formulas

There are clearly some areas for improvement, but one thing to bear in mind is that Fibery is intentionally designed to support multiple users (who may be in different timezones).

This leads to some interesting questions, for example, if I want to know the date of a datetime field, which timezone should be used? Or should the result displayed on the UI depend upon who is viewing it? If I use the entity data in a report view, it could mean different users are presented with completely different analytics :-/
Similarly, if I want to use a date or datetime value in name field formula (which is quite a common use case) what should the text show? Should it depend on the user’s chosen locale? Maybe an american worker wants to see things differently than a uk worker. Having the same entity take different names depending on who’s looking seems a bit weird.

[Lug-gl]

I totally understand the remaining questions, but for dateTime field formulas a user depending time calulcations is already working or? In my opinion it makes totally sense how you do it currently (backend UTC, front end user depending time zone).

Why shouldn’t it be possible for text fields too? a text field could display a combination of of values like

`date of this one: ` + dateTimeFieldFormatted + ` some more text afterwards`

=> in total it would be just a log but the dateTimeFieldFormatted would be calculated as the current dateTime formula

[njyo]

Why shouldn’t it be possible for text fields too?

Because then the data of the field would be different for different users and the text field would need to remember or recognise dates.

[Chr1sG]

@njyo

Because then the data of the field would be different for different users and the text field would need to remember or recognise dates.

Yes, indeed.
With datetime fields, a single value (in UTC) is stored on the backend, and the frontend component knows to convert this to the correct timezone for the user.
For text fields, the frontend simply displays the same value to every user (whatever is stored on the backend).
Making text fields ‘timezone aware’ would imply changing the frontend so that it is capable of recognising if the string actually contains a converted datetime.