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.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive