Problem with timestamp columns

[joaopedro02]

Hi everyone!

When i try to read a table using trino with this power bi connector, the timestamp columns does not work:

I'm trying to fix this issue. Do you have any tips of what can be the problem ?

[joaopedro02]

I have tried to change the mapped type in this line, from a datetime to a text:

Now it works on power bi with the timestamp columns imported as text columns:

I'm having trouble to figure out how can i convert this column to datetime type within the code. I'm new in the power query M language.

[joaopedro02]

I believe I've resolved the issue. I made a PR with the solution.

[pichlerpa]

Hi @joaopedro02!

Can you please share your table definition? I tried to reproduce your problem, but I couldn't, everything seemed to work just fine with a TIMESTAMP column.

Regards, Patrick

CREATE TABLE memory.default.test ( id INTEGER, event_time TIMESTAMP );

INSERT INTO memory.default.test (id, event_time) VALUES (1, TIMESTAMP '2023-01-01 09:00:00.000');

[joaopedro02]

Hi @pichlerpa !

Shure! the table definition:

CREATE TABLE hive_metastore.temp_tables.teste_trino (  
      id INTEGER,  
      created_at_timestamp TIMESTAMP(3) WITH TIME ZONE,   
      created_at_date DATE
);

insert into hive_metastore.temp_tables.teste_trino (id, created_at_timestamp, created_at_date) VALUES (1, TIMESTAMP '2024-03-28 15:00 UTC',  date '2024-03-28' );

I think the problem is in the time zone part of the timestamp.

[joaopedro02]

we use trino with the delta lake connector and it seems that all timestamp columns in delta lake are mapped to a TIMESTAMP(3) WITH TIME ZONE on Trino.

[pichlerpa]

I updated the PR and submitted a few changes https://github.com/pichlerpa/PowerBITrinoConnector/pull/16. All Trino date and time data types should now work, data types including time zones are treated as text to be handled within Power BI.

CREATE TABLE memory.default.test (
    id INTEGER,
    created_at_date DATE,
    created_at_time TIME,
    created_at_time_precision TIME(6),
    created_at_time_with_tz TIME WITH TIME ZONE,
    created_at_timestamp TIMESTAMP,
    created_at_timestamp_precision TIMESTAMP(6),
    created_at_timestamp_with_tz TIMESTAMP WITH TIME ZONE,
    created_at_timestamp_precision_with_tz TIMESTAMP(6) WITH TIME ZONE,
    interval_year_to_month INTERVAL YEAR TO MONTH,
    interval_day_to_second INTERVAL DAY TO SECOND
);

INSERT INTO memory.default.test (
    id, 
    created_at_date, 
    created_at_time, 
    created_at_time_precision, 
    created_at_time_with_tz, 
    created_at_timestamp, 
    created_at_timestamp_precision, 
    created_at_timestamp_with_tz, 
    created_at_timestamp_precision_with_tz, 
    interval_year_to_month, 
    interval_day_to_second
) VALUES (
    1, 
    DATE '2024-03-28', 
    TIME '15:00:00', 
    TIME '15:00:00.123456', 
    TIME '15:00:00+00:00', 
    TIMESTAMP '2024-03-28 15:00:00', 
    TIMESTAMP '2024-03-28 15:00:00.123456', 
    TIMESTAMP '2024-03-28 15:00:00+00:00', 
    TIMESTAMP '2024-03-28 15:00:00.123456+00:00', 
    INTERVAL '2' YEAR TO MONTH, 
    INTERVAL '15 10:30:45.123456' DAY TO SECOND
);