Timezone formulas

I want to have a table that contains events with datetimes - in which there is a input column for adding a datetime with the document’s timezone, and then another column for rendering that prior datetime for the user’s local timezone.

I have tried many different ways of accomplishing this over the months, and am still at a loss.

Column Timezones

The ideal of course would be to be able to set the rendering timezone for the datetime column in its datetime format popup to either (1) document timezone (2) user timezone (3) manual timezone. So another dropdown in this component:

That way I can have two columns, say When as a datetime in document timezone, and Your time as a datetime in user timezone with formula =When.

Timezone Functions

Another approach would be to have a .toTimezone method, that would enable:

  • Today().ToTimezone(CurrentTimezone())
  • Today().ToTimezone(DocumentTimezone())
  • Today().ToTimezone('UTC') and Today().ToTimezone('AEST') for timezone abbreviation support
  • Today().ToTimezone('Australia/Sydney') for tz support

Epoch Functions

Or at the most basic level, there is already EpochToDate(1489187664) so please add:

  • EpochToDateTime(1489187664)
  • Today().ToEpoch()

That way I could at least adhoc something with EpochToDateTime(Today().ToEpoch() + CurrentTimezone().CurrentOffset * 3600) with 3600 being the hour multiplier in epoch time.

JavaScript Functions

Or what would be nice is just letting us execute javascript in our formulas via an eval, however that seems unlikely due to the security implications.

Embeds

Currently it seems the only hope for me is by embedding a converter service, however embed displays as a large card rather than just the content of the text.

Here is the experiment coda document:

https://coda.io/d/Timezone-Experiments_dGtkvpOhoRt

Here is the source for the converter service I wrote which is a cloudflare worker:

addEventListener('fetch', event => {
  event.respondWith(handleRequest(event.request))
})

async function handleRequest(request) {
  const url = new URL(request.url)
  const locale = url.searchParams.get('locale') || 'en-US'
  const result = convert(
    url.searchParams.get('date'),
    url.searchParams.get('fromOffset'),
    url.searchParams.get('fromTZ'),
    url.searchParams.get('toOffset'),
    url.searchParams.get('toTZ'),
    locale
  )
  return page(result, locale)
  // return text(result)
}

function convert (date, fromOffset, fromTZ, toOffset, toTZ, locale) {
  if ( !date || !fromOffset || !toOffset ) return 'invalid inputs'
  const original = new Date(date)
  const delta = (Number(toOffset) - Number(fromOffset)) * 3600
  const result = original.getTime() + delta
  return new Intl.DateTimeFormat(locale, {
    weekday: 'long', year: 'numeric', month: 'long', day: 'numeric',
    hour: 'numeric', minute: 'numeric', second: 'numeric',
    timeZone: toTZ || undefined,
    timeZoneName: 'short'
  }).format(result)
}

function page (data, locale) {
  const title = 'Timezone Converter'
  const image = 'https://images.unsplash.com/photo-1501139083538-0139583c060f?fit=crop&w=640'
  const meta = [
    `<meta name="twitter:card" content="summary" />`,
    `<meta name="twitter:site" content="@balupton" />`,
    `<meta name="twitter:creator" content="@balupton" />`,
    `<meta property="og:url" content="https://bevry.me/api/tzc/" />`,
    `<meta property="og:title" content="${title}" />`,
    `<meta property="og:description" content="${data}" />`,
    `<meta property="og:image" content="${image}" />`
  ].join('')
  const payload = `<!DOCTYPE html><html lang="${locale}"><head><meta charset="utf-8" />${meta}<title>${title}</title></head><body>${data}</body></html>`
  return new Response(payload, {
    status: 200,
    headers: {
      'content-type': 'text/html;charset=UTF-8',
    },
  })
}

function text (data) {
  return new Response(data, {status: 200})
}

Okay, I figured out a way to do it inline within coda formulas. Refer to this published and copy-able coda document for a demo, explanation, and formulas:

1 Like

Dear @balupton.bevry,

Welcome to the community :handshake:
Thank you for sharing in detail how you approached the multiple time zone issue, I am sure many more members will appreciate your efforts.