Get Local Timezones based on CET time in the report

[joannasokolowska]

Dear colleagues,

I have below sample of report which includes dates in CET timezones (PL timezones) for the CEE countries and offices. I need to get local timezone for each country/date, taking into account time changes/shifts during the year. I need this to be working for past and coming years as well (so timeshift build into workflow). 

[alexnajm]

Great question, but a few things are likely needed then:

1) do you have lat long values for your cities? Or spatial centroids available? Otherwise it'll be hard to map to a time zone

2) do you have spatial polygons of time zones? You can find them online if needed!

3) once you have both, you can spatial match the points to the polygons to know what time zone they are in! It will also then include the offset you can use to get times into local time

If you don't want to go down the spatial route, do you have a list of countries and what time zone they are in? Plus a list of time zones and their corresponding offset?

[joannasokolowska]

Hi,

Thank you for reaching out me so quickly! I am attaching additional file with timezones - can you help using these 2 files? 

Best,

Asia

[alexnajm]

I can, but I think you can try it out too! A few things you'll need:

- parse out the offset (+2, +3, etc.) and make it a number so you can do DateTimeDiff calcs

- note down dates that are considered "summer" versus "winter" - is it first half versus second half of the year, or something else?

- Join on country so the data links together

- make sure your date column is in a true datetime format for calculations

Once you have tried it, feel free to come back with a workflow and questions and we can help from there. Good luck!

[joannasokolowska]

Hi,

I started creating the workflow but I got stuck so I appreciate your help.

1. The time shift is always happeing the last Sunday of March (into summer time)  and last Sunday of October (into winter time) so not sure how to calculate it for all years. 

2. The offset is with GTM in the timezones summary file while datetime in timezones file is in CET time (the same as for Poland) , so how can I get local time not GMT? 

I am attaching part of the workflow I started working on.