Time to Number value
I have tried to find a few answers on this but nothing has come up - is there a way to take a time column (00:02:01) and SUM ALL the values for the total Hours? I have tried SUM(HOUR('TALK TIME'
)) but that is not giving me the correct summarized number of hours. I also tried to do in the ETL but the column keeps coming up as blank. Is there an easier way or another article I can reference?
Answers
-
I'd recommend looking into TIME_TO_SEC function to convert your string into total seconds then you can divide that number by 3600 to convert to it to hours and then SUM it.
TIME_TO_SEC Returns an elapsed number of seconds for all values in a date/time column. TIME_TO_SEC('DateCol')
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
One problem I've had are discrpancies between the calculated number of seconds using TIME_TO_SEC and using EXTRACT() for getting the (Hour/3600) + (Minute/60) to give the time in seconds.
0 -
If your goal is to get a result that is a time string like your input, I believe you want something like
CAST(SEC_TO_TIME(SUM(TIME_TO_SEC(`TALK TIME`))) AS CHAR)
If you only want the hours (as a floating-point number), it would be
SUM(TIME_TO_SEC(`TALK TIME`)) / 3600
If you want integer hours, invoke the
DIV
function instead of using the/
operator:DIV(SUM(TIME_TO_SEC(`TALK TIME`)), 3600)
Magic ETL doesn't currently support
EXTRACT
or any of the functions in that family (e.g.HOUR
,MINUTE
) for time columns representing amounts of time, like you have in this case. They only work on calendric data like dates and timestamps.
Note that care must be taken when converting from text to time. These three common formats are treated these three ways:
1.'100'
is treated as 100 seconds, i.e.00:01:40
2.'100:59'
is 100 hours and 59 minutes, i.e.100:59:00
3.'100:59:59'
is 100 hours, 59 minutes, and 59 seconds.
The00:00
format is particularly troublesome, as it isn't always clear whether it was originally intended to be hours and minutes (which is the way it will be interpreted) or minutes and seconds (in which case you'd need to prepend'00:'
to it for it to parse correctly).Randall Oveson <randall.oveson@domo.com>
1
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