SQL transform - Duration (mins) between two Datetime Columns
Hi,
I am trying to get the difference between two Datetime Columns, I went through the below post to achieve the same using Beast mode.
But, I want to get the difference in minutes and create a new column based on this condition.
Please suggest, thanks in advance!
Best Answers
-
Hi @Khan_Naziya
I've found unix_timestamp function to be great in cases like this. If you convert two datetimes to unix timstamp it'll give you the number of seconds since 1970-01-01. Simple math would get you the number in unit of minutes.
Here's a BeastMode function that will calculate time between timestamps in minutes (with decimal)
(UNIX_TIMESTAMP(`ResponseTimeUTC`) - UNIX_TIMESTAMP(`CreatedTimeUTC`)) / 60
I wrote a more advanced summary of this you can read here: https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Date-Calculations-minutes-and-seconds/m-p/49259/highlight/true#M8465
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
TIMESTAMPDIFF(MINUTE, ...) calculates whole minutes that have passed between the two timestamps so it makes sense you're seeing 10 (essentially it's truncating instead of rounding) instead of 11. If you want to round to the nearest minute you can just utilize the ROUND(..., 0) function
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
I think I accomplished what I needed using below query, but is there a way to get the decimal part?
SELECT `ResponseTimeUTC`,`CreatedTimeUTC`,
TIMESTAMPDIFF(MINUTE,`CreatedTimeUTC`,`ResponseTimeUTC`) AS RESPONSETIME
from table;0 -
Hi @Khan_Naziya
I've found unix_timestamp function to be great in cases like this. If you convert two datetimes to unix timstamp it'll give you the number of seconds since 1970-01-01. Simple math would get you the number in unit of minutes.
Here's a BeastMode function that will calculate time between timestamps in minutes (with decimal)
(UNIX_TIMESTAMP(`ResponseTimeUTC`) - UNIX_TIMESTAMP(`CreatedTimeUTC`)) / 60
I wrote a more advanced summary of this you can read here: https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Date-Calculations-minutes-and-seconds/m-p/49259/highlight/true#M8465
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
Thanks @GrantSmith !
Using below Query, I can see the decimal part now.
(UNIX_TIMESTAMP(`ResponseTimeUTC`) - UNIX_TIMESTAMP(`CreatedTimeUTC`)) / 60
ResponseTimeUTC CreatedTimeUTC RESPONSETIME(Min) 2020-02-01 00:14:36 2020-02-01 00:03:40 10.9333 Earlier the TIMESTAMPDIFF(MINUTE,`CreatedTimeUTC`,`L1_ResponseTimeUTC`) was rounding off to 10. That is why I want to see decimal part. As I see the decimal part in 10.9333, this should be rounded off to nearest integer i.e.,11.
0 -
TIMESTAMPDIFF(MINUTE, ...) calculates whole minutes that have passed between the two timestamps so it makes sense you're seeing 10 (essentially it's truncating instead of rounding) instead of 11. If you want to round to the nearest minute you can just utilize the ROUND(..., 0) function
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 657 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 815 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 81 App Studio
- 45 Variables
- 775 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 81 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive