Hi all -

I just got a PurpleAir indoor sensor and now I want to create a DB that stores all of the data. The issue I have right now is that all the date/time I download from the company is text that looks like this:

2021-03-14 01:03:37 UTC

So I put this in a text field called rawDate.

Then I create multiple formula fields to extract Year, Month, Day, Hour, Minutes, and Seconds.

Then I have another formula field that puts together all back together (yes, I know I can create just one formula):

Mar 14, 2021, 01:03

I then thought about DST and figured I had to create a formula that looked at the date/time and compared it to a DST start and end date/time table and changed it accordingly. It all worked out well until I tested date/time that fell right on the switch.

Not surprisingly the MacOS app thinks that newly combined data/time is my local time so at DST it changes it for me, but it is UTC time so it is changing it at the wrong hour. For example, I'm in UTC-7 and on Mar 14, 2021 at 2:00 am it shows as 3:00 am.

I can't wrap my head around how to solve this issue. Any suggestions would be greatly appreciated.