How subtract hours from DateTime but not in using Beast Mode

Reply
Green Belt

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
Orange Belt

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

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'

 

 

 

 

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Green Belt

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


All Replies
Green Belt

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

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

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.

Orange Belt

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

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'

 

 

 

 

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Green Belt

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
Domopalooza 2018! Pre-conference training registration is now open! Click here!