Is there any easier way to adjust date error caused by timezone?

Lu_zhang
Lu_zhang Member
in Datasets

for example, my google sheet raw data is 3/31/2024 23:16

when I upload it into DOMO, it appears as 4/1/2024 7:16 PM

I had to use Convert_TZ to adjust it.

this time I find there is the problem of summer time and winter time, which makes it more difficult to adjust time zone.

anybody who knows more easier way to adjust my DOMO uploaded date?

thank you for your help!

Best Answer

  • ArborRose
    ArborRose Coach
    Answer ✓

    You can use Beast Mode to adjust your timestamp for time zone differences.


    Step 1: Identify the time zone offset


    Determine the fixed offset between the time zones. For example, if you want to adjust from UTC to a specific time zone, you need to know the standard offset (e.g., UTC-5 for EST).

    Step 2: Adjust for Daylight Saving Time

    Create a logic to account for DST. This can be done using conditional statements in Beast Mode.

    CASE
    
WHEN DATE_FORMAT(your_date_column, '%m-%d') BETWEEN '03-08' AND '11-01' THEN DATE_SUB(your_date_column, INTERVAL 4 HOUR)
    
ELSE DATE_SUB(your_date_column, INTERVAL 5 HOUR)
    
END

    Or you can manipulate it in an ETL or Python.

    from datetime import datetime
    
import pytz

#Your date string from Google Sheets
date_str = '2024-03-31 23:16'
    
date_format = '%Y-%m-%d %H:%M'

#Parse the date
date_obj = datetime.strptime(date_str, date_format)

#Define the original and target timezones
original_tz = pytz.timezone('UTC')
    
target_tz = pytz.timezone('America/New_York')

#Localize the date to the original timezone and convert to the target timezone
localized_date = original_tz.localize(date_obj)
    
adjusted_date = localized_date.astimezone(target_tz)

print(adjusted_date)

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • ArborRose
    ArborRose Coach
    Answer ✓

    You can use Beast Mode to adjust your timestamp for time zone differences.


    Step 1: Identify the time zone offset


    Determine the fixed offset between the time zones. For example, if you want to adjust from UTC to a specific time zone, you need to know the standard offset (e.g., UTC-5 for EST).

    Step 2: Adjust for Daylight Saving Time

    Create a logic to account for DST. This can be done using conditional statements in Beast Mode.

    CASE
    
WHEN DATE_FORMAT(your_date_column, '%m-%d') BETWEEN '03-08' AND '11-01' THEN DATE_SUB(your_date_column, INTERVAL 4 HOUR)
    
ELSE DATE_SUB(your_date_column, INTERVAL 5 HOUR)
    
END

    Or you can manipulate it in an ETL or Python.

    from datetime import datetime
    
import pytz

#Your date string from Google Sheets
date_str = '2024-03-31 23:16'
    
date_format = '%Y-%m-%d %H:%M'

#Parse the date
date_obj = datetime.strptime(date_str, date_format)

#Define the original and target timezones
original_tz = pytz.timezone('UTC')
    
target_tz = pytz.timezone('America/New_York')

#Localize the date to the original timezone and convert to the target timezone
localized_date = original_tz.localize(date_obj)
    
adjusted_date = localized_date.astimezone(target_tz)

print(adjusted_date)

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Categories