How to load time difference from psql to google data studio

user_7874234 0
Viewed 70

I have a psql connection where one of the columns are sql intervals, as such: showing the intervals

However, to me it seems that this is interpreted in a text format:

showing data type

I would like to convert these into the GDS equivalent of interval / timedelta / duration; However, I cannot seem to find a function that does that.

I am aware of the DATE_DIFF function, with which I can picture a workaround, pseudocode: DATE_DIFF(DATE(1000, 01, 01), DATE(1000+avg_timedelta.regexmatchyear, 01+avg_timedelta.regexmatchmonth, 01 + avg_timedelta.regexmatchday)

However, it feels strange to me that there is no interval type in GDS, even if one of the function's output is an interval; but the docs don't say anything about converting to interval.

google-data-studio postgresql
1 Answers

I made the workaround in SQL, I created a column that converts the interval into int< number of hours> with:

EXTRACT(
        epoch
        FROM
            CTE.myInterval
    ) / 3600 AS timedelta_hours

This does not answer the underlying problem that in GDS you cannot easily convert to interval type. (which they call duration).

user_78742340
Related