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

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    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.

  • So if I wanted it to be converted UTC to CST it would be

    CONVERT_TZ('DATE', ''+07:00'')?

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    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.


  • 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")

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    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.

  • 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

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    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

  • GrantSmith
    GrantSmith Indiana 🔴

    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

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    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