Getting the hour part from a date in a different timezone

Q&A
1

Friends,

I have some issues with the hour part of a date and it is probably because of timezones and myself missing something very obvious.

I have a date field (with timezone) “start” which is in a Postgres table / data source. Now I need the hour part to create labels in the visual (see below)

Screenshot 2025-03-03 at 12.35.56
Screenshot 2025-03-03 at 12.35.56676×654 17.1 KB

When I’m adding calculated fields to the dataset it all seems to work, the below formula gives me the correct datatime and with extract() I get the correct hour

parseDate(toString(formatDate(start, 'dd/MM/yyyy HH:mm:ss.SSS', 'Europe/Amsterdam')),'dd/MM/yyyy HH:mm:ss.SSS')

Unfortunately, as soon as I publish the dataset, nothing happens, the dates are exactly the same as if the calculation on that field never happend. I fooled around with doing it in multiple steps, in one go, etc, but can’t seem to get it right.

What am I missing and is it even possibly to reliably get that hour string?

2

To clarify further. If I set the analysis to a different timezone, I do see the timestamps changing, but the hour part / label stays exactly the same.

3

Thanks to ChatGPT I have it working, but it’s not pretty :wink:

formatDate(
    ifelse(
        {start} >= truncDate('WK', addDateTime(31 - extract('WD', truncDate('MM', addDateTime(-2, 'MM', {start}))), 'DD', truncDate('MM', addDateTime(-2, 'MM', {start})))) AND
        {start} < truncDate('WK', addDateTime(31 - extract('WD', truncDate('MM', addDateTime(10, 'MM', {start}))), 'DD', truncDate('MM', addDateTime(10, 'MM', {start})))),

        addDateTime(2, 'HH', {start}),  // Apply UTC+2 for DST
        addDateTime(1, 'HH', {start})   // Apply UTC+1 for Standard Time
    ),
    'yyyy-MM-dd HH:mm:ss'
)

This is the solution to work with Amsterdam/Berlin/Paris timezones. Unfortunately this will only work in preview when editing the dataset. It throws an error in the analysis and this has to do with nesting too deep. I eventually ended up creating 4 (6 previously) different calculated fields

// previous ChatGPT answers in comments
//FirstDayMarch = truncDate('MM', addDateTime(-2, 'MM', {start}))
//FirstDayOctober = truncDate('MM', addDateTime(10, 'MM', {start}))
//LastDayMarch = addDateTime(-1, 'DD', addDateTime(1, 'MM', {FirstDayMarch}))
//LastDayOctober = addDateTime(-1, 'DD', addDateTime(1, 'MM', {FirstDayOctober}))

LastDayMarch = parseDate(
    concat(toString(extract('YYYY', {start})), '-03-31 00:00:00'),
    'yyyy-MM-dd HH:mm:ss'
)

LastDayOctober = parseDate(
    concat(toString(extract('YYYY', {start})), ‘-10-31 00:00:00'),
    'yyyy-MM-dd HH:mm:ss'
)

LastSundayMarch = ifelse(
    extract('WD', {LastDayMarch}) = 1, {LastDayMarch}, 
    addDateTime(-1, 'DD', ifelse(
        extract('WD', {LastDayMarch}) = 2, addDateTime(-1, 'DD', {LastDayMarch}),
        ifelse(
            extract('WD', {LastDayMarch}) = 3, addDateTime(-2, 'DD', {LastDayMarch}),
            ifelse(
                extract('WD', {LastDayMarch}) = 4, addDateTime(-3, 'DD', {LastDayMarch}),
                ifelse(
                    extract('WD', {LastDayMarch}) = 5, addDateTime(-4, 'DD', {LastDayMarch}),
                    ifelse(
                        extract('WD', {LastDayMarch}) = 6, addDateTime(-5, 'DD', {LastDayMarch}),
                        addDateTime(-6, 'DD', {LastDayMarch})  -- If WD = 7 (Saturday), subtract 6 days
                    )
                )
            )
        )
    ))
)

LastSundayOctober = ifelse(
    extract('WD', {LastDayOctober}) = 1, {LastDayOctober}, 
    addDateTime(-1, 'DD', ifelse(
        extract('WD', {LastDayOctober}) = 2, addDateTime(-1, 'DD', {LastDayOctober}),
        ifelse(
            extract('WD', {LastDayOctober}) = 3, addDateTime(-2, 'DD', {LastDayOctober}),
            ifelse(
                extract('WD', {LastDayOctober}) = 4, addDateTime(-3, 'DD', {LastDayOctober}),
                ifelse(
                    extract('WD', {LastDayOctober}) = 5, addDateTime(-4, 'DD', {LastDayOctober}),
                    ifelse(
                        extract('WD', {LastDayOctober}) = 6, addDateTime(-5, 'DD', {LastDayOctober}),
                        addDateTime(-6, 'DD', {LastDayOctober})  // If WD = 7 (Saturday), subtract 6 days
                    )
                )
            )
        )
    ))
)

And then using that in a final calculation

formatDate(
    ifelse(
        {start} >= {LastSundayMarch} AND {start} < {LastSundayOctober},
        addDateTime(2, 'HH', {start}), // Apply UTC+2 for DST
        addDateTime(1, 'HH', {start})  // Apply UTC+1 for Standard Time
    ),
    'yyyy-MM-dd HH:mm:ss'
)

After that I run into some practical limit with another calculated field based on the final calculation. Luckily the exact same field as a calculated field in the analysis works fine.
(still true, with just 4 fields instead of 6)

All in all, this is such a hassle. I’m going to ask my dev team to add an extra column, as it really shouldn’t be that complicated

1 Like
4

I’m editing my answer above - as it didn’t properly work and blindsided by ChatGPTs perceived confidence, I totally missed that you need two fields less and can very easily just get the last days of March and October with a different formula