ts-recipe-formula-hms-display-snowflake

name

ts-recipe-formula-hms-display-snowflake

description

Creates four Snowflake scalar UDFs that format integer durations (seconds or minutes) as human-readable time strings — HH:MM:SS, DD:HH:MM:SS, HH:MM, DD:HH:MM — then shows the ThoughtSpot formula syntax to display them in any Model. Use this skill whenever the user asks about displaying durations, formatting elapsed time, converting seconds or minutes to a readable format, showing call duration, handle time, SLA elapsed time, ticket age as HH:MM:SS, or any scenario where an integer count of seconds or minutes should appear as a formatted time string in ThoughtSpot.

ThoughtSpot + Snowflake: Duration Display UDFs

ThoughtSpot stores durations as integer columns (seconds or minutes) but cannot natively format them as HH:MM:SS. This skill deploys four Snowflake scalar UDFs that convert integer durations to formatted strings, then shows how to call them from ThoughtSpot formulas using sql_string_op.

UDF	Input	Returns	Example
format_seconds_to_hms	seconds INT	STRING	3665 → 01:01:05
format_seconds_to_dhms	seconds INT	STRING	90061 → 01:01:01:01
format_minutes_to_hm	minutes INT	STRING	65 → 01:05
format_minutes_to_dhm	minutes INT	STRING	1501 → 01:01:01

All four UDFs are independent — no creation order constraint.

Ask one question at a time. Wait for each answer before proceeding.

---

Prerequisites

• Role with CREATE FUNCTION privilege on the target schema
• Access to run SQL in Snowsight

---

Step 1 — Collect Target Database and Schema

Ask:

Which Snowflake database should these UDFs be created in?
(e.g. ANALYTICS, PROD_DB)

Save as {target_db} (uppercase).

Which schema within {target_db}?
(e.g. PUBLIC, SHARED, UTILS)

Save as {target_schema} (uppercase).

Confirm before creating anything:

Ready to create 4 UDFs in {target_db}.{target_schema}:

  format_seconds_to_hms(seconds)     → STRING  e.g. 3665 → '01:01:05'
  format_seconds_to_dhms(seconds)    → STRING  e.g. 90061 → '01:01:01:01'
  format_minutes_to_hm(minutes)      → STRING  e.g. 65 → '01:05'
  format_minutes_to_dhm(minutes)     → STRING  e.g. 1501 → '01:01:01'

Existing functions with these names will be replaced (CREATE OR REPLACE).

Proceed? (Y / N)

---

Step 2 — Create the UDFs

Run each statement in Snowsight. All four are independent — any can be skipped if not needed.

2a. format_seconds_to_hms

CREATE OR REPLACE FUNCTION {target_db}.{target_schema}.format_seconds_to_hms(seconds INT)
RETURNS STRING
AS
$$
    LPAD(TRUNC(seconds / 3600)::STRING, 2, '0') || ':' ||
    LPAD(TRUNC(MOD(seconds, 3600) / 60)::STRING, 2, '0') || ':' ||
    LPAD(MOD(seconds, 60)::STRING, 2, '0')
$$;

2b. format_seconds_to_dhms

CREATE OR REPLACE FUNCTION {target_db}.{target_schema}.format_seconds_to_dhms(seconds INT)
RETURNS STRING
AS
$$
    LPAD(TRUNC(seconds / 86400)::STRING, 2, '0') || ':' ||
    LPAD(TRUNC(MOD(seconds, 86400) / 3600)::STRING, 2, '0') || ':' ||
    LPAD(TRUNC(MOD(seconds, 3600) / 60)::STRING, 2, '0') || ':' ||
    LPAD(MOD(seconds, 60)::STRING, 2, '0')
$$;

2c. format_minutes_to_hm

CREATE OR REPLACE FUNCTION {target_db}.{target_schema}.format_minutes_to_hm(minutes INT)
RETURNS STRING
AS
$$
    LPAD(TRUNC(minutes / 60)::STRING, 2, '0') || ':' ||
    LPAD(MOD(minutes, 60)::STRING, 2, '0')
$$;

2d. format_minutes_to_dhm

CREATE OR REPLACE FUNCTION {target_db}.{target_schema}.format_minutes_to_dhm(minutes INT)
RETURNS STRING
AS
$$
    LPAD(TRUNC(minutes / 1440)::STRING, 2, '0') || ':' ||
    LPAD(TRUNC(MOD(minutes, 1440) / 60)::STRING, 2, '0') || ':' ||
    LPAD(MOD(minutes, 60)::STRING, 2, '0')
$$;

After all succeed, confirm:

✓ format_seconds_to_hms created
✓ format_seconds_to_dhms created
✓ format_minutes_to_hm created
✓ format_minutes_to_dhm created

---

Step 3 — Verify

-- Expected: 01:01:05  (1h 1m 5s)
SELECT {target_db}.{target_schema}.format_seconds_to_hms(3665);

-- Expected: 01:01:01:01  (1d 1h 1m 1s)
SELECT {target_db}.{target_schema}.format_seconds_to_dhms(90061);

-- Expected: 01:05  (1h 5m)
SELECT {target_db}.{target_schema}.format_minutes_to_hm(65);

-- Expected: 01:01:01  (1d 1h 1m)
SELECT {target_db}.{target_schema}.format_minutes_to_dhm(1501);

If any returns an unexpected value, re-run CREATE OR REPLACE for that function.

---

Step 4 — ThoughtSpot Formula Examples

The UDFs are ready. Here's how to use them in ThoughtSpot formulas:

Format a seconds column as HH:MM:SS:
  sql_string_op ("{target_db}.{target_schema}.format_seconds_to_hms({0})", [seconds column])

Format a seconds column as DD:HH:MM:SS:
  sql_string_op ("{target_db}.{target_schema}.format_seconds_to_dhms({0})", [seconds column])

Format a minutes column as HH:MM:
  sql_string_op ("{target_db}.{target_schema}.format_minutes_to_hm({0})", [minutes column])

Format a minutes column as DD:HH:MM:
  sql_string_op ("{target_db}.{target_schema}.format_minutes_to_dhm({0})", [minutes column])

To add a formula in ThoughtSpot:
  1. Open the Model → Edit → + Add formula
  2. Paste the expression with your column name substituted and give it a name
     (e.g. "Call Duration", "Handle Time Display")
  3. Save the formula — also add a matching columns[] entry if editing via TML.

TML pattern — both entries are required:

formulas:
- id: "formula_Call Duration"
  name: "Call Duration"
  expr: >-
    sql_string_op ("{target_db}.{target_schema}.format_seconds_to_hms({0})", [{seconds_column}])
  properties:
    column_type: ATTRIBUTE

columns:
# ... existing columns ...
- name: "Call Duration"
  formula_id: "formula_Call Duration"   # must match id above exactly
  properties:
    column_type: ATTRIBUTE
    index_type: DONT_INDEX

Note: column_type: ATTRIBUTE and no aggregation — these UDFs return strings and cannot be summed.

Note: editing TML directly requires the ts CLI (Claude Code only). In Snowsight, use the ThoughtSpot UI or run /ts-recipe-formula-hms-display-snowflake in a Claude Code session.

---

Error Handling

Symptom	Action
Insufficient privileges on CREATE FUNCTION	GRANT CREATE FUNCTION ON SCHEMA {target_db}.{target_schema} TO ROLE <role>
Smoke test returns wrong value	Re-run CREATE OR REPLACE for that function
Formula shows as MEASURE in ThoughtSpot	Set column_type: ATTRIBUTE in both formulas[] and columns[]

---

Changelog

Version	Date	Summary
1.0.0	2026-05-13	Initial release