如何在Superset中将秒数总和转换为小时分钟格式

Got it, let's work through this problem together. You’ve got a durationInSeconds column that you’re summing with SUM(durationInSeconds), and the result’s showing up as something like 49.2B (billion seconds) in Superset. You want to turn that into a human-friendly hours and minutes format—here are two solid ways to make that happen:

Method 1: Modify Your Custom SQL Metric Directly #

You can adjust your original SUM calculation to output the formatted duration right in the metric. This works because most databases (like PostgreSQL, MySQL, BigQuery) support the necessary string and math functions. Use this SQL snippet:

CONCAT(
  FLOOR(SUM(durationInSeconds) / 3600), ' hours ',
  FLOOR(MOD(SUM(durationInSeconds), 3600) / 60), ' minutes'
) AS total_duration

Let me break down what each part does:

• SUM(durationInSeconds) / 3600 calculates total hours (including decimals)
• FLOOR() strips off the decimal to get full, whole hours
• MOD(SUM(durationInSeconds), 3600) grabs the remaining seconds after accounting for full hours
• Dividing that remainder by 60 and using FLOOR() gives you the full remaining minutes

If you want to include leftover seconds too, extend the formula like this:

CONCAT(
  FLOOR(SUM(durationInSeconds) / 3600), ' hours ',
  FLOOR(MOD(SUM(durationInSeconds), 3600) / 60), ' minutes ',
  MOD(SUM(durationInSeconds), 60), ' seconds'
) AS total_duration

Method 2: Use a Calculated Column in Superset’s Explore View #

If you already have the summed durationInSeconds saved as a metric in your dashboard, you can create a calculated column to convert it without rewriting the original metric:

1. Navigate to your Explore view and click Add Calculated Column
2. Paste in this formula (replace {{ sum_duration }} with the actual name of your summed metric):

   CONCAT(
     FLOOR({{ sum_duration }} / 3600), ' hours ',
     FLOOR(MOD({{ sum_duration }}, 3600) / 60), ' minutes'
   )
   

3. Name the column (e.g., "Total Duration (Hours & Minutes)") and save it. The formatted value will now appear alongside your original metric.

Quick Note on the "49.2B" Display #

Don’t worry about the "B" suffix in Superset’s display—that’s just the tool formatting the large number for readability. The underlying value from SUM(durationInSeconds) is still the full integer (like 49200000000 for 49.2B), so the math in the formulas above will work correctly.

内容的提问来源于stack exchange，提问作者Surabhi Singh