Dates and Timezones in Customer.io Emails
Send emails with dates / times localized for the recipient’s timezone
Do you store your dates as timestamps? Cool. Do you want to send an email to someone in San Francisco with times local to San Francisco? Here’s how to do it!
Set a timezone on a customer
On each customer, you can pass in a value for a timezone. In the example we’ll use the attribute “timezone” but you can use anything you want.
Example values to store on the user:
Once the person has a value for a timezone, you can then use it to localize a time in an email.
Use a timezone in an email
Let’s look at using an appointment time, then converting that in to the customers local time:
If the appointment time is “1366934245” as a timestamp, and the customer timezone is “US/Eastern” and the date format is “%c %z” it’ll output as
Thu Apr 25 20:01:45 2013 -0400.
Convert timestamps back to readable dates and times
When you export information out of Customer.io, you will see dates and times as unix timestamps (seconds since the epoch). Here’s how to convert timestamps to a readable format in Microsoft Excel.
Use this formula (with A1 as the cell with the epoch number, for example):
=(A1 / 86400) + 25569
If you’re using a Mac, you might find that the date conversion comes out 4 years ahead. If so, use this formula:
=(A1 / 86400) + 24107
Then, format the result cell or column of cells for date (and time). This shows you GMT time.
If you’d like to see the result in your time zone, add the UTC offset into the formula like so:
=((A1 +/- time zone adjustment) / 86400) + 25569
For instance, here’s the formula used to convert timestamp data on a Mac for the Eastern Time Zone in New York: