0

I store the last open times for my emails in a separate Data Extension queried from the _opens data view.

The problem I have is that I need to be able to see who's received an email today via a filter, but because the timestamps are in UTC this doesn't work how I want.

What is the best way to handle this? Should I manually offset the times?

0

Yes, you would be required to add offset in your query for the column of Data Extension in which you are fetching the last open time.

We had a similar use case where we were getting Salesforce data in different time zone. And we were using query activity to transfer that data into different BUs. There itself we put the offset to have time in our DE according to the required time zone.

| improve this answer | |
0

You can ask Salesforce to implement your whole configuration to a timezone you wish. This is a custom service and comes with costs.

When you want to offset your timestamps you should keep in mind:

  • If you are on a daylight saving timezone you have to make sure you always set the offset correctly
  • Make sure you do not violate key constraints when you insert data where the timestamp is a contraint while you might have overlapping timelines (daylight saving times setback)
  • Keep in mind that daylight saving times do not have a fixed date and integrate that into your query.
  • Change your whole Company time to a timezone which does not have daylight savings.
| improve this answer | |
0

Use the AT TIME ZONE SQL function. This is by far the easiest way to convert the UTC times you'll get from the system data views as it also handles daylight savings.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.