Datediff in Redshift vs Magic ETL
Hi there!
Im trying to recreate a calculation from Redshift to Magic ETL. Im running into some trouble with a specific formula.
In Redshift: DATEDIFF('week',"start_date__c","end_date__c")
In Magic: v1  DATEDIFF(`end_date__c`,`start_date__c`)/7
In Magic: v2  WEEKOFYEAR(`end_date__c`)  WEEKOFYEAR(`start_date__c`)
Showing a picture of my results and the comparison that is happening with some sample data. It looks like the Redshift function is behaving similar to the weekofyear formula but is able to calculate across many years, which isnt compatible with my v2 formula.
We can see that the "weekofyear" formula on the right hand side is the most similar, but it doesnt match when calculating across multiple years.
Hoping someone has solved for this before!
Thank you in advance!
Best Answer

@Robaba04  my recommended approach is to not try to replicate the output exactly. For example, in your original photo, row1 is 1.42 and your desired result is 2, but row2 is 13.42 and your desired result is 13. So it seems like Redshift is applying a varying rounding methodology. I'm not super familiar with the backend logic, so won't comment on that.
However, the approach I typically take when doing date/time differences is to make use of either EPOCH or UNIX. In this case, if you use EPOCH you will get the number of milliseconds between your start and end dates. This removes the complexity of trying to account for partial years, multiple years, start/end weeks across multiple years, etc.
Note, since your data is dates, without milliseconds in the time, you can use either UNIX or EPOCH and get the same result. For the sake of the example I'll use EPOCH.
EPOCH_MILLIS(end)EPOCH_MILLIS(start)
You can then divide to get the number of weeks between those 2 datetimes with fairly accurate precision.
Finally, you can round with whatever approach suits you best (FLOOR, CEIL, ROUND). In this case I'll use round to round up and down.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0
Answers

@Robaba04  the answer provided by @MichelleH is great. When you select an answer, I would ask that you please choose her post. If it was me, I would take a similar approach as what she suggested, probably using UNIX_TIMESTAMP() instead of datediff and then dividing to get back to my desired date grain.
Just wanted to point out that I did notice that using FLOOR is going to cause you to have a different result for row1 and row5 since you will be rounding down. If you're wanting to match the previous redshift output exactly, an option would be… Note, this will only work if all of your dates are within 1 year of each other.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0 
Hi all!
Thank you for the quick responses! I believe we are getting closer. The floor method did disagree with row1 and row5.
The case statement hit the mark for a one year gap. But to throw another wrench in the pile… if the time difference is greater than a year, it doesnt look like the case statement worked as intended.
0 
@Robaba04  my recommended approach is to not try to replicate the output exactly. For example, in your original photo, row1 is 1.42 and your desired result is 2, but row2 is 13.42 and your desired result is 13. So it seems like Redshift is applying a varying rounding methodology. I'm not super familiar with the backend logic, so won't comment on that.
However, the approach I typically take when doing date/time differences is to make use of either EPOCH or UNIX. In this case, if you use EPOCH you will get the number of milliseconds between your start and end dates. This removes the complexity of trying to account for partial years, multiple years, start/end weeks across multiple years, etc.
Note, since your data is dates, without milliseconds in the time, you can use either UNIX or EPOCH and get the same result. For the sake of the example I'll use EPOCH.
EPOCH_MILLIS(end)EPOCH_MILLIS(start)
You can then divide to get the number of weeks between those 2 datetimes with fairly accurate precision.
Finally, you can round with whatever approach suits you best (FLOOR, CEIL, ROUND). In this case I'll use round to round up and down.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0
Categories
 All Categories
 1.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 292 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 95 SQL DataFlows
 601 Datasets
 2.1K Magic ETL
 3.7K Visualize
 2.4K Charting
 689 Beast Mode
 43 App Studio
 39 Variables
 658 Automate
 170 Apps
 441 APIs & Domo Developer
 42 Workflows
 5 DomoAI
 32 Predict
 12 Jupyter Workspaces
 20 R & Python Tiles
 385 Distribute
 110 Domo Everywhere
 269 Scheduled Reports
 6 Software Integrations
 112 Manage
 109 Governance & Security
 8 Domo University
 25 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 98 Community Announcements
 Domo Community Gallery
 4.8K Archive