Convert Timestamp to CST, UST, MST, EST Python

leeloo_dallas

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?

MarkSnodgrass

Here is a link with additional documentation:

https://www.w3resource.com/mysql/date-and-time-functions/mysql-convert_tz-function.php

MarkSnodgrass

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.

leeloo_dallas

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

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

MarkSnodgrass

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.

https://www.timeanddate.com/time/zones/

MarkSnodgrass

Here is a link with additional documentation:

https://www.w3resource.com/mysql/date-and-time-functions/mysql-convert_tz-function.php

leeloo_dallas

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

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.

leeloo_dallas

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')

leeloo_dallas

I'm confused by what offset to use

MarkSnodgrass

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

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

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