Filtering a set of rows by event time (across different time zones)

jkimo999

I have a dataset, for example, with 3 events in each of the US time zones -- all coming through as 7pm local to the time zone in the dataset.

I need to sort them by the time occurring, so in the example below, California events would be listed first, then Colorado, then Chicago, then New York.

How can i do this? Can I do it in a beast mode?

Event 1, California, 7PM

Event 2, California, 7PM

Event 3, California, 7PM

Event 4, New York, 7PM

Event 5, New York, 7PM

Event 6, New York, 7PM

Event 7, Colorado, 7PM

Event 8, Colorado, 7PM

Event 9, Colorado, 7PM

Event 10, Chicago, 7PM

Event 11, Chicago, 7PM

Event 12, Chicago, 7PM

Thanks!

MarkSnodgrass

Do you have the time zone as part of the datetime data? (i.e. EST, PST, CST, etc,) If so, you can build a case statement to put it in UTC time (or standardized to any time zone that you want). You can use the SUBTIME function to subtract the necessary hours. Here is a list of time zone abbreviations to be looking for.

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