Converting time value stored as text
Hi,
I have a column of time value in the format of 'HH : MM : SS' - it is currently stored as text, I want to be able to run some calculation such as looking at the sum, or average of this column. How do I do that?
When I aggregate the data, I'd like it to still output as 'HH : MM : SS' on the graph
Comments
-
I was able to accomplish this with the following steps in MySQL:
SELECT
`Resolution Time`
,STR_TO_DATE(`Resolution Time`,'%H:%i:%s') as `Date field`
FROM table_nameThis will change your text column to a date time column. Then I changed the time to seconds, summed them and converted the seconds back to your format with this transform:
SELECT CONCAT_WS(
':',
(SUM(TIME_TO_SEC(`Date field`)) DIV 3600),
(SUM(TIME_TO_SEC(`Date field`)) MOD 3600) DIV 60,
(SUM(TIME_TO_SEC(`Date field`)) MOD 3600) MOD 60) as `Total Time`,
CONCAT_WS(
':',
(AVG(TIME_TO_SEC(`Date field`)) DIV 3600),
(AVG(TIME_TO_SEC(`Date field`)) MOD 3600) DIV 60,
(AVG(TIME_TO_SEC(`Date field`)) MOD 3600) MOD 60) as `Avg Time`
FROM transform_data_1This gives the Total Time and the Average Time:
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
Hi,
Thanks for the suggestion. I have tried to use it but I noticed when the hours value is more than 24 hours, it does not generate.
The idea is to understand the number of hours/min/second on the specifc columns. So it is not considering date at all, but counting the number of hours. Based on the suggested string, I have an output that does not generate any values that are beyond 24 hours in the date field. Any suggestion to resolve?
First Response Time (in Hrs) Resolution Time (in Hrs) 3:00:10 5341:49:00 0:00:00 5147:52:43 9:00:00 5098:59:17 0:00:00 5053:03:48 0:00:00 5080:07:27 0:00:00 4962:48:40 0:00:00 4934:35:47 0:00:00 4894:35:53 0:00:00 4894:30:41 0:00:00 4893:53:05 0:00:00 4893:44:24 0:00:00 0:00:00 0:00:00 0:00:00 Thanks,
0
Categories
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 278 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.7K Transform
- 89 SQL DataFlows
- 557 Datasets
- 2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 571 Beast Mode
- 11 App Studio
- 28 Variables
- 579 Automate
- 141 Apps
- 414 APIs & Domo Developer
- 23 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 352 Distribute
- 92 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 92 Manage
- 89 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 88 Community Announcements
- 4.8K Archive