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')andToday().ToTimezone('AEST')for timezone abbreviation supportToday().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})
}
