Convert Timestamp to CST, UST, MST, EST Python

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?

Tagged:

Best Answer

Answers

  • 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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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.


    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • MarkSnodgrass
    Answer ✓
    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • 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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.