Reply
Green Belt
Posts: 54
Registered: ‎05-19-2016
Accepted Solution

How subtract hours from DateTime but not in using Beast Mode

I need to subtract 7 hours from a DATETIME column. I cannot use Beast Mode unfortunatelly as calculations cannot be used in the "Date Range Field". The data needs to be used as 7AM to 7AM days instead the ussual 12AM to 12AM days.

 

The data comes from the workenbench so the transform cna be done there, usign a dataflow or even SQL (although the latter is ussually beyond my skill level).

 

I have tried simply chaging the Time Zone usgin a workbench transform but as I am in UTC+10 I would UTC+17 time zone which does not exist. 


Accepted Solutions
Solution
Accepted by topic author rado98
‎05-24-2017 07:02 PM
Green Belt
Posts: 54
Registered: ‎05-19-2016

Re: How subtract hours from DateTime but not in using Beast Mode

Thanks for the idea.

As I don't use SQL I did the following in the end

 

Set DATETIME to AUS EST in workbench

In Magic ETL converted to DECIMAL

Subtracted the equivent to 7 hours (25200000)

Converted back to DATETIME

View solution in original post


All Replies
Green Belt
Posts: 54
Registered: ‎05-19-2016

Re: How subtract hours from DateTime but not in using Beast Mode

[ Edited ]

I managed to get this done by using a convoluted method on the workbench.

I escentially "pasted as value" of the original DATETIME as a string, then made a Time Zone Change (to +3), copied the DATETIME string as a DATETIEM type and then Change the Time Zone to +10.

The original DATETIME value is now 7hours earlier than the real time.

The created DATETIME value has the actual time.

 

If anyone has a more straight forward solution I would very much like to see it.

Highlighted
Green Belt
Posts: 54
Registered: ‎05-19-2016

Re: How subtract hours from DateTime but not in using Beast Mode

So my temporary fix from above no longer works after daylight savings.

Anyone with a permanent solution.

White Belt
Posts: 6
Registered: ‎03-23-2017

Re: How subtract hours from DateTime but not in using Beast Mode

[ Edited ]

Using a dataflow and inside the transform try this in your query.

 

CONVERT_TZ(your_date_here,'UTC','America/New_York')  + INTERVAL -7 HOUR AS Your_Date_Column_Name

 

 

Note: I'm just assuming the Time Zone is Eastern. If you need a different time zone here the other major US time zones.

 

Pacific = 'America/Los_Angeles'

Mountain = 'America/Denver'

Central = 'America/'Chicago'

Pacific - Hawaii =  'Pacific/Honolulu'

 

 

 

 

Solution
Accepted by topic author rado98
‎05-24-2017 07:02 PM
Green Belt
Posts: 54
Registered: ‎05-19-2016

Re: How subtract hours from DateTime but not in using Beast Mode

Thanks for the idea.

As I don't use SQL I did the following in the end

 

Set DATETIME to AUS EST in workbench

In Magic ETL converted to DECIMAL

Subtracted the equivent to 7 hours (25200000)

Converted back to DATETIME

Announcements
Customize your Dojo Handle! You can update your Dojo screen name by clicking on My Settings. click here! Thanks!