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

Lu_zhang

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!

ArborRose

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)

ArborRose

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)