I have a data set like the following:
- start_time_UTC
- 2021-09-16T12:00:00-05:00
- 2021-09-15T19:00:00-05:00
- 2021-09-16T08:18:00-05:00
- 2021-09-16T12:22:10-05:00
My default time is UTC, but I would like to create multiple columns based of the start_time_UTC to create cst, mst, and est.
- from domomagic import *
- from datetime import datetime as dt
- import pandas as pd
- from pytz import timezone
- import pytz
- df = read_dataframe('my_dataset')
- df['time_stamp'] = df['start_time']
- utc = timezone('UTC')
- cst = timezone('US/Central')
- mst = timezone('US/Mountain')
- est = timezone('US/Eastern')
- # my issue begins at 'published_time"
- published_time = time_stamp.apply(lambda x: dt.strptime(df.time_stamp, '%a, %d %b %Y %H:%M:%S %Z'))
- time_utc = published_time.replace(tzinfo=utc)
- time_cst = published_time.replace(tzinfo=cst)
- time_mst = published_time.replace(tzinfo=mst)
- time_est = published_time.replace(tzinfo=est)
- # then call using time_timezone
- df['time_published_cst'] = time_cst.strftime('%I:%M:%S %p %Z')
- df['time_published_est'] = time_est.strftime('%I:%M:%S %p %Z')
- df['time_published_mst'] = time_mst.strftime('%I:%M:%S %p %Z')
- df['time_published_utc'] = time_utc.strftime('%I:%M:%S %p %Z')
Initially I received an error for the following that said, "TypeError: strptime() argument 1 must be str, not Series":
- published_time = datetime.strptime(time_stamp, '%a, %d %b %Y %H:%M:%S %Z')
So, I changed it using lambda but got an error saying, " NameError: name 'time_stamp' is not defined"
- published_time = time_stamp.apply(lambda x: dt.strptime(df.time_stamp, '%a, %d %b %Y %H:%M:%S %Z'))
Then again I tried the following but got an error that said, 'time_stamp' does not match format '%a, %d %b %Y %H:%M:%S %Z'
- published_time = df['time_stamp'].apply(lambda x: dt.strptime('time_stamp', '%a, %d %b %Y %H:%M:%S %Z'))
Can anyone tell me what I could be wrong?
Here is a link with additional documentation:
any users posts that helped you.I think it is important to note than when datetime data is imported into Domo, it assumes it is UTC. If you are doing this work before importing into Domo, you will end up with undesirable results when looking at it in Domo.
I would suggest doing the timezone work in Domo via a beast mode or a MySQL dataflow or the Magic ETL 2.0 if you have it.
You can use the CONVERT_TZ() function to easily shift time zones:
CONVERT_TZ() converts the datetime value dt to a new moment in time such that the original value''s wall-clock time when rendered in from_tz matches the new value''s wall-clock time when rendered in to_tz.
Time zones are specified as strings. If the string begins with a ''+'' or ''-'', it is interpreted as an HH:MM offset from UTC. Otherwise, it must be the name of a time zone from the IANA time zone database. Examples: ''+07:00'', ''UTC'', ''America/Denver''.
Hope this helps.
any users posts that helped you.So if I wanted it to be converted UTC to CST it would be
CONVERT_TZ('DATE', ''+07:00'')?
No, it wants it in this format: CONVERT_TZ(dt,from_tz,to_tz)
It would look like:
CONVERT('date',"+00:00","+06:00")
Also, according to this CST is 6 hours off of UTC.
any users posts that helped you.Here is a link with additional documentation:
any users posts that helped you.Thanks for the link so if I did the following:
if my string ends in -05:00 I would do the following for cst?
CONVERT('start_time',"-05:00","-06:00")
If your start time is coming in as UTC then you want to have +00:00 in parameter 2. In parameter 3, is how many hours you want to adjust it.
To say it another way, parameter 2 is to specify how many hours offset from UTC is the datetime in parameter 1. Parameter 3 is how many hours from UTC it should be offset.
any users posts that helped you.How do I know to use + or -
If my string says -05:00 for utc and I want it to be cst
Is it convert_tz('start_time', '+00:00', '-06:00')
or
Is it convert_tz('start_time', '+00:00', '+06:00')
I'm confused by what offset to use
I would refer to this page again to know the offset:
https://www.timeanddate.com/time/zones/
CST is -6 from UTC, so you would enter -06:00 for parameter 3. If you datefield is coming in as UTC, parameter 2 will be +00:00
any users posts that helped you.If I recall correctly you should be able to pass in the abbreviation of the time zone like EST or the full ISO name like US/Eastern as parameters to CONVERT_TZ
Good suggestion by @GrantSmith . I just tested this in a beast mode and it worked properly. Using this syntax:
- CONVERT_TZ(`_BATCH_LAST_RUN_`,'UTC','CST')
Properly converts. See screenshot below.
Again, the Batch_Last_Run field is coming in as UTC, so I put UTC and parameter 2. I want to convert to CST, so I put CST in parameter 3.
Hope this does the trick for you @leeloo_dallas
any users posts that helped you.
