Date column in superset displayed as unix timestamp

[alfredho811]

A clear and concise description of what the bug is.

How to reproduce the bug

Using Superset verion 2.0.0 and postgres as data warehouse,

I created a heatmap where the x-axis are the dates and y-axis stock codes.

The SQL is as follows

SELECT date_trunc('month', release_time_hkt) AS release_month,
       sehk_code AS sehk_code,
       COUNT(*) AS count
FROM l1_trading.vw_trd_hkex_listed_company_publication
GROUP BY date_trunc('month', release_time_hkt),
         sehk_code
ORDER BY count DESC
LIMIT 10000;

I would expect that the x-axis would show the dates in the standard format (e.g. 2022-08-01), not in unix timestamp.

I found a similar issue reported here: https://github.com/apache/superset/issues/3027

but that's for older superset version.

Environment

(please complete the following information):

• browser type and version:

• superset version: superset version ---> 2.0.0

• python version: python --version (I did not check, because I am simply using the official apache superset docker image apache/superset:2.0.0)

• node.js version: node -v

• any feature flags active:

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• [ ] I have checked the superset logs for python stacktraces and included it here as text if there are any. ---> I don't see any logs specific to this conversion to unix timestamp

• [ ] I have reproduced the issue with at least the latest released version of superset. --> 2.0.0 is the latest superset version

• [ ] I have checked the issue tracker for the same issue and I haven't found one similar. ---> I cannot find a similar issue with superset version 2.0.0

Additional context

Add any other context about the problem here.

[mdeshmu]

You will have to mark column as "is temporal" in dataset settings and specify date format as epoch_s or epoch_ms based on the granularity of the stored values. Let me know if this helps.

[alfredho811]

Hey @mdeshmu

Thanks for your reply.

I guess I did not phrase my problem correcty.

As you can see from the above screenshot, the problem is that the column date_trunc('month', release_time_hkt) becomes unix timestamp.

Instead, I want it to be like 2022-08-01, 2022-09-01 instead.

=============

For the column release_time_hkt, I notice that Superset marked it as TIMESTAMP WITHOUT TIME ZONE

========== Similar problem

Given that I am using Postgres as my database and the problematic column is of type date in postgres, I find that the below issue is quite similar to mine.

• Link: https://github.com/apache/superset/issues/13295

The solution here is to format the datetime as string

https://github.com/apache/superset/issues/13295#issuecomment-783986140

Is that the only solution?

Thanks

[alfredho811]

I have created a varchar version of the timestamp/date in postgres and it's working like a charm.

============

Below is what I did in the code.

[sfirke]

Is this same / related to https://github.com/apache/superset/issues/20074 ? That one should be fixed in upcoming 2.0.1

[sfirke]

This is fixed in 2.0.1 for me, is this still an issue for you?

[rusackas]

Closing since this appears to be fixed :)

[sqja]

This problem with timestamp field on X axis seems to be back in 2.1.0, the same was working well with 2.0.1

[michael-s-molina]

It seems that to resolve this problem we need to add a control to the Heatmap chart so that users can select the appropriate time format similar to what other chart types do. This is an example from Bar chart:

The problem is that Heatmap is a legacy chart so it would be more valuable if we did this fix when migrating it to ECharts to avoid duplicated work.

This problem with timestamp field on X axis seems to be back in 2.1.0, the same was working well with 2.0.1

I don't know how this was working before given that this control never existed in this chart? Did it assumed a specific format by default?

[sqja]

I don't know how this was working before given that this control never existed in this chart? Did it assumed a specific format by default?

Indeed in this regards here is an example of a currently working Heatmap we use (superset 2.0.1, last working version) with same data and displaying properly formatted date on X. Visible also on the screenshot some settings currently applied to configure it

The problem is that Heatmap is a legacy chart so it would be more valuable if we did this fix when migrating it to ECharts to avoid duplicated work.

Is there currently any plan in your roadmap to migrate Heatmap to EChart ?

[michael-s-molina]

Is there currently any plan in your roadmap to migrate Heatmap to EChart ?

Yes. We're planning to migrate more charts after 3.0. We already have some PRs in the works here an here. I added a card to our roadmap to ensure this chart gets prioritized given this problem.