Powershell Add Member/Column Based on Another Column

I have a script that I wrote in Python that I'm trying to replicate with Powershell, which I only started using for the first time yesterday.

It gets data from an API, transforms the data a bit, and then puts it to a SQL server database.

With Python, I get the json that is returned from the POST request and put it into a dataframe. The data includes a reading_id, date_recorded, and the measurement. The date in date_recorded is just a string, but it is in UTC. I created a new column in my dataframe named date_recorded_pst with the date in date_recorded converted to PST.

To do this I created a convertToPst function and then used df.apply like below (this probably isn't the best way to do it but it works)

def convertToPST(utcString):
    format = '%Y-%m-%dT%H:%M:%S'
    dt_utc = datetime.strptime(utcString, format)
    dt_utc = dt_utc.replace(tzinfo=pytz.UTC)
    
    dt_pst = dt_utc.astimezone(tz = pst)
    pst_str = dt_pst.strftime(format)
    
    return pst_str

df['date_recorded_pst'] = df['date_recorded'].apply(lambda x : convertToPST(x))

How can I do the same in Powershell? Sorry I'm not caught up to speed on PS lingo. Is adding this new column the same as adding a new "Member"?

This is what I have so far:

$response = Invoke-RestMethod @Parameters 

$readings = $response.readings

$readings2 = $readings | Select-Object -Property * -ExcludeProperty qa

The last line was because I wanted to remove a column called qa. I just found it in another thread. Is there a better way to do that?

Here are the last two readings in $readings2

reading_id       : 500000023713225
date_recorded    : 2023-02-09T02:43:00
systolic_mmhg    : 112
diastolic_mmhg   : 73


reading_id       : 500000023671605
date_recorded    : 2023-02-08T15:52:00
systolic_mmhg    : 112
diastolic_mmhg   : 73

What I want:

reading_id       : 500000023713225
date_recorded    : 2023-02-09T02:43:00
systolic_mmhg    : 112
diastolic_mmhg   : 73
date_recorded_pst: 2023-02-08T18:43:00


reading_id       : 500000023671605
date_recorded    : 2023-02-08T15:52:00
systolic_mmhg    : 112
diastolic_mmhg   : 73
date_recorded_pst: 2023-02-08T07:52:00

Thanks!

Solution

You can use the ConvertTimeBySystemTimeZoneId method to convert a datetime to a specific Time Zone. The method has 2 overloads of interest, both would work in this case but also one of them will require you to convert these string date times into a datetime instance and then call ToLocalTime() assuming your Time Zone is not UTC. Both examples below using one of the dates in your question:

[System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId(
    ([datetime] '2023-02-09T02:43:00').ToLocalTime(),
    'Pacific Standard Time'
).ToString('yyyy-MM-ddTHH:mm:ss') # => 2023-02-08T18:43:00

[System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId(
    '2023-02-09T02:43:00',        # the method implicitly casts `datetime` to this string
    'UTC',                        # from TimeZone
    'Pacific Standard Time'       # to TimeZone
).ToString('yyyy-MM-ddTHH:mm:ss') # => 2023-02-08T18:43:00

Knowing this and since you're already using Select-Object, we can use a calculated property to create the new objects adding this new calculated property:

Invoke-RestMethod @Parameters |
    ForEach-Object readings |
    Select-Object *, @{
        N = 'date_recorded_pst'
        E = {
            [TimeZoneInfo]::ConvertTimeBySystemTimeZoneId(
                $_.date_recorded,
                'UTC',
                'Pacific Standard Time'
            ).ToString('yyyy-MM-ddTHH:mm:ss')
        }
    } -ExcludeProperty qa