Dates and Timezones in 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:

  • UTC
  • US/Central
  • -3

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:

    {{ customer.appointment_time | timezone: customer.timezone | date: '%c %z' }}

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, 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: =(A1-4/24)/86400+24107

More information